日期:2014-05-17 浏览次数:20748 次
if OBJECT_ID('tempdb..#t') is not null drop table #t
select'1' as cID, N'測試課' as cClass, '81201533' as cSN, N'燕XX' as cName,null as cSign into #t
union
select'2', N'測試課', '81206820', N'丁XX',null
union
select'0', N'測試課', '81209639', N'朱XX',null
union
select'1', N'測試課', '81213788', N'黃X ',null
union
select'2', N'測試課', '81214553', N'孟XX',null
union
select'0', N'測試課', '81215971', N'侯X ',null
union
select'1', N'測試課', '81215988', N'梁XX',null
union
select'2', N'測試課', '81216034', N'張XX',null
union
select'0', N'測試課', '81222073', N'閆X ',null
-- select * from #t
if object_id('tempdb..#t2') is not null drop table #t2
select ROW_NUMBER() over (partition by cID order by cSN) cPrt, * into #t2 from #t
-- select * from #t2
select * from #t2 A
left join #t2 B on B.cPrt = A.cPrt and B.cID = 2
left join #t2 C on C.cPrt = A.cPrt and C.cID = 0
where A.cID = 1