27,579
社区成员
发帖
与我相关
我的任务
分享
--一张货源表A(字段:创建时间(2017-08-09 09:07:01),有用户id),
--一张用户表B(字段:id,审核状态0,1,2),
--现在将货源表的数据按照创建时间排序,时间前的排前面,并且如果是同一天的数据还要按照用户的审核状态在排,审核通过的排前面(1代表审核通过)
if not object_id(N'Tempdb..#Tmp_A') is null
drop table #Tmp_A
Go
create table #Tmp_A
(
Id varchar(10),
Create_Date Datetime)
INSERT INTO #Tmp_A
Select 1, '2017-08-09 10:07:01' UNION all
Select 2, '2017-08-09 11:07:01' UNION all
Select 3, '2017-08-10 10:07:01' UNION all
Select 4, '2017-08-11 10:07:01'
if not object_id(N'Tempdb..#Tmp_B') is null
drop table #Tmp_B
Go
create table #Tmp_B
(
Id varchar(10),
Audit_Status int)
INSERT INTO #Tmp_B
Select 1, 0 UNION all
Select 2, 1 UNION all
Select 3, 0 UNION all
Select 4, 1
Select ID,Create_Date,Audit_Status
From (
Select a.ID,Create_Date,SUBSTRING(Cast(Create_Date as VARCHAR),1,10) as Create_Date2,Audit_Status
From #Tmp_A a
Left Join #Tmp_B b on a.ID=b.ID
) a
Order By Create_Date2,Audit_Status Desc
SELECT A.*
FROM A
JOIN B ON B.id = A.id
ORDER BY createtime ,
CASE WHEN state = 1 THEN 0
ELSE 1
END;