我有两张表
(1).BSN_BBS_Posted_Mst表:发帖表
帖子ID Posted_ID BigInt
帖子标题 Posted_Title varchar(max)
帖子内容 Posted_Content text
发帖时间 Posted_Time datetime
发帖人员 Staff_ID BigInt (2).BSN_BBS_Replie_Det表:回帖表
回帖ID Replie_ID BigInt
回帖人员 Staff_ID BigInt
帖子ID Posted_ID BigInt
回帖时间 Replie_Time Datetime
回帖内容 Replie_Content Text最后结果所要的数据显示:
Posted_ID(帖子ID)Posted_Title(帖子内容) Posted_Time(发帖时间) Staff_ID(发帖的) Replie_Time(回帖时间)
我的排序要求是:发帖表里的内容全部获取,也就是说,发帖表的所有数据(所有的帖子)。如果帖子有回复,那么就按照
回帖的时间来排序,如果没有回帖时间,就按照发帖的时间来进行排序!
(1).BSN_BBS_Posted_Mst表:发帖表
帖子ID Posted_ID BigInt
帖子标题 Posted_Title varchar(max)
帖子内容 Posted_Content text
发帖时间 Posted_Time datetime
发帖人员 Staff_ID BigInt (2).BSN_BBS_Replie_Det表:回帖表
回帖ID Replie_ID BigInt
回帖人员 Staff_ID BigInt
帖子ID Posted_ID BigInt
回帖时间 Replie_Time Datetime
回帖内容 Replie_Content Text最后结果所要的数据显示:
Posted_ID(帖子ID)Posted_Title(帖子内容) Posted_Time(发帖时间) Staff_ID(发帖的) Replie_Time(回帖时间)
我的排序要求是:发帖表里的内容全部获取,也就是说,发帖表的所有数据(所有的帖子)。如果帖子有回复,那么就按照
回帖的时间来排序,如果没有回帖时间,就按照发帖的时间来进行排序!
Posted_Title,
Posted_Time,
Staff_ID,
Replie_Time
from tb1 left join tb2
on tb1.Posted_ID = tb2.Posted_ID
order by isnull(Replie_Time,Posted_Time)
SELECT A.Posted_ID,Posted_Title,Posted_Time,A.Staff_ID,Replie_Time
FROM BSN_BBS_Posted_Mst A LEFT JOIN BSN_BBS_Replie_Det B
ON A.Posted_ID=B.Posted_ID
ORDER BY isnull(Replie_Time,Posted_Time)分开来排
SELECT A.Posted_ID,Posted_Title,Posted_Time,A.Staff_ID,Replie_Time
FROM BSN_BBS_Posted_Mst A LEFT JOIN BSN_BBS_Replie_Det B
ON A.Posted_ID=B.Posted_ID
ORDER BY case when Replie_Time is null then 1 else 0 end,
isnull(Replie_Time,Posted_Time)
if object_id('BSN_BBS_Posted_Mst') is not null
drop table BSN_BBS_Posted_Mst
go
create table BSN_BBS_Posted_Mst
(
Posted_ID BigInt,
Posted_Title varchar(max),
Posted_Content text ,
Posted_Time datetime ,
Staff_ID BigInt
)go
if object_id('BSN_BBS_Replie_Det') is not null
drop table BSN_BBS_Replie_Det
go
create table BSN_BBS_Replie_Det
(
Replie_ID BigInt,
Staff_ID BigInt,
Posted_ID BigInt,
Replie_Time Datetime,
Replie_Content Text
)insert into BSN_BBS_Posted_Mst
select 1,'标题一','内容一','2010-05-20','1' union all
select 2,'标题二','内容二','2010-05-21','2' union all
select 3,'标题三','内容三','2010-05-22','3' union all
select 4,'标题四','内容四','2010-05-23','4' union all
select 5,'标题五','内容五','2010-05-24','5' insert into BSN_BBS_Replie_Det
select 1,1,1,'2010-05-22','回帖内容一' union all
select 2,2,2,'2010-05-21','回帖内容二' union all
select 3,3,3,'2010-05-20','回帖内容三';with cte as
(
select F.Posted_Id ,Replie_Time from BSN_BBS_Posted_Mst as F
outer apply
(
select top 1 Replie_Time from BSN_BBS_Replie_Det as H where H.Posted_Id=F.Posted_Id order by Replie_Time asc
) as app
)
select F.*,cte.Replie_Time from BSN_BBS_Posted_Mst as F inner join cte on F.Posted_Id=cte.Posted_Id order by isnull(Replie_Time,Posted_Time)