简单的单表自连接查询

简单的单表自连接查询

insert into SayReply values ('其实我也不知道为啥不好','2017-04-21 11:34:43','曹操',3)
insert into SayReply values ('我知道心情为啥不好','2017-04-21 11:35:36','诸葛亮',1)
insert into SayReply values ('那你说说为啥不好','2017-04-21 11:35:49','曹操',6)

insert into SayReply values ('因为你家司马懿不行了','2017-04-21 12:05:47','诸葛亮',7)
insert into SayReply values ('管我啥事','2017-04-21 12:06:18','司马懿',8)
insert into SayReply values ('既生亮何生瑜','2017-04-21 13:38:21','周瑜',0)
insert into SayReply values ('经典说得好，周先生','2017-04-21 13:38:43','诸葛亮',10)

insert into SayReply values ('厉害了我的哥','2017-04-21 13:39:02','曹操',11)
insert into SayReply values ('亮啊，你别生周先生的气','2017-04-21 13:40:46','司马懿',10)
insert into SayReply values ('没看懂','2017-04-21 13:41:12','粟裕',10)
insert into SayReply values ('别说话，小草','2017-04-21 13:41:59','诸葛亮',12)

--修改字段类型
ALTER TABLE SayReply ALTER COLUMN Content Nvarchar(max);

--6.查询司马懿回复的信息
select * from SayReply where  Publisher='司马懿';

--7连接查询被“司马懿”评论和回复的数据，要求显示被评论和回复的内容和人名 及“司马懿”评论和回复的内容
select b.content as P_content,b.Publisher as P_person,b.releasetime as P_releasetime,a.content as C_content,a.Publisher as C_Person,a.releasetime as C_reseasetime from SayReply as a left join SayReply b on a.ParentID=b.id where a.Publisher='司马懿'

--8.修正7题查询结果，并定义别名
select (b.content+ ' 作者:'+b.Publisher) as '留言',(a.content+ ' 作者:' +a.Publisher)as '回复内容' from SayReply as a left join SayReply b on a.ParentID=b.id where a.Publisher='司马懿'

--9.修正7题查询结果，并定义别名
 
select (two.Content +' 作者: '+two.Publisher) as 留言,(one.Publisher  +' 在:'+CONVERT(varchar(100),(one.Releasetime), 20)+'【评论】: '+two.Publisher+'说:'+one.Content) as 回复内容 from SayReply as two inner join SayReply as one on one.ParentId=two.Id where one.Publisher='司马懿'

 

 --10连接查询所有的说说回复信息
with t as
(select t1.ParentID ,t1.id ,t1.Publisher person1 ,t2.Publisher person2 ,t1.content from SayReply t1 left join SayReply t2 on t1.ParentID=t2.id)
select case when t.ParentID = 0 then person1+'【发布】：'+content
when t.ParentID in (select id from t where ParentID=0) then person1+'【评论】'+person2+'说：'+content
else person1 +'【回复】'+person2+'说：'+content end
from t

 

posted on 2018-12-07 14:19  不写Bug的程序员不是好经理 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/guoyu8/p/10082682.html