SQL语句

以下为目前为止我所写过的最长的SQL语句，记录下来以作为纪念。

if   object_id('#tb01','u') is not null    drop table #tb01   if   object_id(' #tb001','u') is not null      drop table #tb001  if   object_id('#tba1','u') is not null    drop table #tba1  if   object_id('#tba2','u') is not null   drop table #tba2      if   object_id('#tba3','u') is not null       drop table #tba3    if   object_id('#tba4','u') is not null   drop table #tba4        if   object_id('#tba5','u') is not null        drop table #tba5     if   object_id('#tba6','u') is not null      drop table #tba6   if   object_id('#tba7','u') is not null        drop table #tba7     if   object_id('#tbb1','u') is not null      drop table #tbb1    if   object_id('#tbb2','u') is not null      drop table #tbb2   if   object_id('#tbb3','u') is not null        drop table #tbb3      if   object_id('#tbb4','u') is not null      drop table #tbb4    if   object_id('#tbb5','u') is not null      drop table #tbb5   if   object_id('#tbb5','u') is not null        drop table #tbb6       if   object_id('#tbb7','u') is not null    drop table #tbb7     if   object_id('#aaa','u') is not null        drop table #aaa   if   object_id('#bbb','u') is not null         drop table #bbb    if   object_id('#ccc','u') is not null      drop table #ccc      if   object_id('#ddd','u') is not null        drop table #ddd   if   object_id('#hhh','u') is not null         drop table #hhh      if   object_id('#fff','u') is not null    drop table #fff       if   object_id('#ggg','u') is not null        drop table #ggg     select LOG_ID as 'ID',[外观是否清洁],[镜头及载物台保持清洁],[仪器水平],[主电源连接正确],[软件运作是否正常] into #tb01 from MES_Check_Detail_Log a pivot (max(a.CheckResult) for a.CheckItemCode in ([外观是否清洁],[镜头及载物台保持清洁],[仪器水平],[主电源连接正确],[软件运作是否正常]))b      select datepart(hh, m.CreatorTime) as dd, 'T'+RIGHT(CONVERT(NVARCHAR(20), m.CreatorTime, 112), 2) AS Ta, CONVERT(int, RIGHT(CONVERT(NVARCHAR(20), m.CreatorTime, 112), 8)) AS CreatorDay123, CONVERT(NVARCHAR(20), m.CreatorTime, 14) AS CreatorT,(case when datepart(hh, m.CreatorTime) > 0  then '白班' else '夜班' end) as 'heibai',(case when u.RealName is null then '无' else u.RealName end) as crator, (case when w.RealName is null then '无' else w.RealName end) as confirmer, t.* into #tb001 from MES_Check_log as m left join #tb01 as t on t.ID=m.Id LEFT JOIN Sys_User u ON u.id=m.CreatorUserId LEFT JOIN Sys_User w ON w.id=m.checker  where m.EquipmentCode=" + equipCode + " and CONVERT(NVARCHAR(20), YEAR(  m.CreatorTime)) + '-' + CASE WHEN LEN(CONVERT(NVARCHAR(20), MONTH(m.CreatorTime))) = 1  THEN '0' + CONVERT(NVARCHAR(20), MONTH(m.CreatorTime)) ELSE CONVERT(NVARCHAR(20), MONTH(m.CreatorTime)) END = '" + startDate + "' select* into #tba1 from #tb001 where [外观是否清洁] is not null order by CreatorDay123       select heibai, [T01],[T02],[T03],[T04],[T05],[T06],[T07],[T08],[T09],[T10],[T11],[T12],[T13],[T14],[T15],[T16],[T17],[T18],[T19],[T20],[T21],[T22],[T23],[T24],[T25],[T26],[T27],[T28],[T29],[T30],[T31] into #tbb1 from #tba1 q pivot (max([外观是否清洁]) for q.Ta in ([T01],[T02],[T03],[T04],[T05],[T06],[T07],[T08],[T09],[T10],[T11],[T12],[T13],[T14],[T15],[T16],[T17],[T18],[T19],[T20],[T21],[T22],[T23],[T24],[T25],[T26],[T27],[T28],[T29],[T30],[T31] )) o     select* into #tba2 from #tb001 where [仪器水平] is not null order by CreatorDay123    select heibai, [T01],[T02],[T03],[T04],[T05],[T06],[T07],[T08],[T09],[T10],[T11],[T12],[T13],[T14],[T15],[T16],[T17],[T18],[T19],[T20],[T21],[T22],[T23],[T24],[T25],[T26],[T27],[T28],[T29],[T30],[T31] into #tbb2 from #tba2 q pivot (max([仪器水平]) for q.Ta in ([T01],[T02],[T03],[T04],[T05],[T06],[T07],[T08],[T09],[T10],[T11],[T12],[T13],[T14],[T15],[T16],[T17],[T18],[T19],[T20],[T21],[T22],[T23],[T24],[T25],[T26],[T27],[T28],[T29],[T30],[T31] )) o    select* into #tba3 from #tb001 where [主电源连接正确] is not null order by CreatorDay123      select heibai, [T01],[T02],[T03],[T04],[T05],[T06],[T07],[T08],[T09],[T10],[T11],[T12],[T13],[T14],[T15],[T16],[T17],[T18],[T19],[T20],[T21],[T22],[T23],[T24],[T25],[T26],[T27],[T28],[T29],[T30],[T31] into #tbb3 from #tba3 q pivot (max([主电源连接正确]) for q.Ta in ([T01],[T02],[T03],[T04],[T05],[T06],[T07],[T08],[T09],[T10],[T11],[T12],[T13],[T14],[T15],[T16],[T17],[T18],[T19],[T20],[T21],[T22],[T23],[T24],[T25],[T26],[T27],[T28],[T29],[T30],[T31] )) o   select* into #tba4 from #tb001 where [镜头及载物台保持清洁] is not null order by CreatorDay123    select heibai, [T01],[T02],[T03],[T04],[T05],[T06],[T07],[T08],[T09],[T10],[T11],[T12],[T13],[T14],[T15],[T16],[T17],[T18],[T19],[T20],[T21],[T22],[T23],[T24],[T25],[T26],[T27],[T28],[T29],[T30],[T31] into #tbb4 from #tba4 q pivot (max([镜头及载物台保持清洁]) for q.Ta in ([T01],[T02],[T03],[T04],[T05],[T06],[T07],[T08],[T09],[T10],[T11],[T12],[T13],[T14],[T15],[T16],[T17],[T18],[T19],[T20],[T21],[T22],[T23],[T24],[T25],[T26],[T27],[T28],[T29],[T30],[T31] )) o   select* into #tba5 from #tb001 where [软件运作是否正常] is not null order by CreatorDay123  select heibai, [T01],[T02],[T03],[T04],[T05],[T06],[T07],[T08],[T09],[T10],[T11],[T12],[T13],[T14],[T15],[T16],[T17],[T18],[T19],[T20],[T21],[T22],[T23],[T24],[T25],[T26],[T27],[T28],[T29],[T30],[T31] into #tbb5 from #tba5 q pivot (max([软件运作是否正常]) for q.Ta in ([T01],[T02],[T03],[T04],[T05],[T06],[T07],[T08],[T09],[T10],[T11],[T12],[T13],[T14],[T15],[T16],[T17],[T18],[T19],[T20],[T21],[T22],[T23],[T24],[T25],[T26],[T27],[T28],[T29],[T30],[T31] )) o   select* into #tba6 from #tb001 where [软件运作是否正常] is not null order by CreatorDay123        select heibai,[T01],[T02],[T03],[T04],[T05],[T06],[T07],[T08],[T09],[T10],[T11],[T12],[T13],[T14],[T15],[T16],[T17],[T18],[T19],[T20],[T21],[T22],[T23],[T24],[T25],[T26],[T27],[T28],[T29],[T30],[T31] into #tbb6 from #tba6 q pivot (max(crator) for q.Ta in ([T01],[T02],[T03],[T04],[T05],[T06],[T07],[T08],[T09],[T10],[T11],[T12],[T13],[T14],[T15],[T16],[T17],[T18],[T19],[T20],[T21],[T22],[T23],[T24],[T25],[T26],[T27],[T28],[T29],[T30],[T31] )) o   select* into #tba7 from #tb001 where confirmer is not null order by CreatorDay123  select heibai, [T01],[T02],[T03],[T04],[T05],[T06],[T07],[T08],[T09],[T10],[T11],[T12],[T13],[T14],[T15],[T16],[T17],[T18],[T19],[T20],[T21],[T22],[T23],[T24],[T25],[T26],[T27],[T28],[T29],[T30],[T31] into #tbb7 from #tba7 q pivot (max(confirmer) for q.Ta in ([T01],[T02],[T03],[T04],[T05],[T06],[T07],[T08],[T09],[T10],[T11],[T12],[T13],[T14],[T15],[T16],[T17],[T18],[T19],[T20],[T21],[T22],[T23],[T24],[T25],[T26],[T27],[T28],[T29],[T30],[T31] )) o  select* into #aaa from (  select '1次/天' as title1,'1' as title2, heibai,'a外观是否清洁' as miniType,max([T01]) as [T01],max([T02]) as [T02],max([T03]) as [T03],max([T04]) as [T04],max([T05]) as [T05],max([T06]) as [T06],max([T07]) as [T07],max([T08]) as [T08],max([T09]) as [T09],max([T10]) as [T10],max([T11]) as [T11],max([T12]) as [T12],max([T13]) as [T13],max([T14]) as [T14],max([T15]) as [T15],max([T16]) as [T16],max([T17]) as [T17],max([T18]) as [T18],max([T19]) as [T19],max([T20]) as [T20],max([T21]) as [T21],max([T22]) as [T22],max([T23]) as [T23],max([T24]) as [T24],max([T25]) as [T25],max([T26]) as [T26],max([T27]) as [T27],max([T28])as [T28],max([T29]) as [T29],max([T30]) as [T30],max([T31]) as [T31] from  #tbb1 group by heibai) b      select * into #bbb from (select '1次/天' as title1,'3' as title2, heibai,'c仪器水平' as miniType,max([T01]) as [T01],max([T02]) as [T02],max([T03]) as [T03],max([T04]) as [T04],max([T05]) as [T05],max([T06]) as [T06],max([T07]) as [T07],max([T08]) as [T08],max([T09]) as [T09],max([T10]) as [T10],max([T11]) as [T11],max([T12]) as [T12],max([T13]) as [T13],max([T14]) as [T14],max([T15]) as [T15],max([T16]) as [T16],max([T17]) as [T17],max([T18]) as [T18],max([T19]) as [T19],max([T20]) as [T20],max([T21]) as [T21],max([T22]) as [T22],max([T23]) as [T23],max([T24]) as [T24],max([T25]) as [T25],max([T26]) as [T26],max([T27]) as [T27],max([T28])as [T28],max([T29]) as [T29],max([T30]) as [T30],max([T31]) as [T31] from  #tbb2 group by heibai)c   select * into #ccc from ( select '1次/天' as title1,'4' as title2, heibai,'d主电源连接正确' as miniType,max([T01]) as [T01],max([T02]) as [T02],max([T03]) as [T03],max([T04]) as [T04],max([T05]) as [T05],max([T06]) as [T06],max([T07]) as [T07],max([T08]) as [T08],max([T09]) as [T09],max([T10]) as [T10],max([T11]) as [T11],max([T12]) as [T12],max([T13]) as [T13],max([T14]) as [T14],max([T15]) as [T15],max([T16]) as [T16],max([T17]) as [T17],max([T18]) as [T18],max([T19]) as [T19],max([T20]) as [T20],max([T21]) as [T21],max([T22]) as [T22],max([T23]) as [T23],max([T24]) as [T24],max([T25]) as [T25],max([T26]) as [T26],max([T27]) as [T27],max([T28])as [T28],max([T29]) as [T29],max([T30]) as [T30],max([T31]) as [T31]  from  #tbb3 group by heibai)d     select * into #ddd from (   select '1次/天' as title1,'2' as title2, heibai,'b镜头及载物台保持清洁' as miniType,max([T01]) as [T01],max([T02]) as [T02],max([T03]) as [T03],max([T04]) as [T04],max([T05]) as [T05],max([T06]) as [T06],max([T07]) as [T07],max([T08]) as [T08],max([T09]) as [T09],max([T10]) as [T10],max([T11]) as [T11],max([T12]) as [T12],max([T13]) as [T13],max([T14]) as [T14],max([T15]) as [T15],max([T16]) as [T16],max([T17]) as [T17],max([T18]) as [T18],max([T19]) as [T19],max([T20]) as [T20],max([T21]) as [T21],max([T22]) as [T22],max([T23]) as [T23],max([T24]) as [T24],max([T25]) as [T25],max([T26]) as [T26],max([T27]) as [T27],max([T28])as [T28],max([T29]) as [T29],max([T30]) as [T30],max([T31]) as [T31] from  #tbb4 group by heibai)e      select * into #fff from ( select '1次/天' as title1,'5' as title2, heibai,'e软件运作是否正常' as miniType,max([T01]) as [T01],max([T02]) as [T02],max([T03]) as [T03],max([T04]) as [T04],max([T05]) as [T05],max([T06]) as [T06],max([T07]) as [T07],max([T08]) as [T08],max([T09]) as [T09],max([T10]) as [T10],max([T11]) as [T11],max([T12]) as [T12],max([T13]) as [T13],max([T14]) as [T14],max([T15]) as [T15],max([T16]) as [T16],max([T17]) as [T17],max([T18]) as [T18],max([T19]) as [T19],max([T20]) as [T20],max([T21]) as [T21],max([T22]) as [T22],max([T23]) as [T23],max([T24]) as [T24],max([T25]) as [T25],max([T26]) as [T26],max([T27]) as [T27],max([T28])as [T28],max([T29]) as [T29],max([T30]) as [T30],max([T31]) as [T31]  from  #tbb5 group by heibai)f       select * into #ggg from ( select '点检人' as title1,'6' as title2, heibai,'fcrator' as miniType,max([T01]) as [T01],max([T02]) as [T02],max([T03]) as [T03],max([T04]) as [T04],max([T05]) as [T05],max([T06]) as [T06],max([T07]) as [T07],max([T08]) as [T08],max([T09]) as [T09],max([T10]) as [T10],max([T11]) as [T11],max([T12]) as [T12],max([T13]) as [T13],max([T14]) as [T14],max([T15]) as [T15],max([T16]) as [T16],max([T17]) as [T17],max([T18]) as [T18],max([T19]) as [T19],max([T20]) as [T20],max([T21]) as [T21],max([T22]) as [T22],max([T23]) as [T23],max([T24]) as [T24],max([T25]) as [T25],max([T26]) as [T26],max([T27]) as [T27],max([T28])as [T28],max([T29]) as [T29],max([T30]) as [T30],max([T31]) as [T31] from  #tbb6 group by heibai)g  select * into #hhh from ( select '确认人' as title1,'7' as title2, heibai,'gconfirmer' as miniType,max([T01]) as [T01],max([T02]) as [T02],max([T03]) as [T03],max([T04]) as [T04],max([T05]) as [T05],max([T06]) as [T06],max([T07]) as [T07],max([T08]) as [T08],max([T09]) as [T09],max([T10]) as [T10],max([T11]) as [T11],max([T12]) as [T12],max([T13]) as [T13],max([T14]) as [T14],max([T15]) as [T15],max([T16]) as [T16],max([T17]) as [T17],max([T18]) as [T18],max([T19]) as [T19],max([T20]) as [T20],max([T21]) as [T21],max([T22]) as [T22],max([T23]) as [T23],max([T24]) as [T24],max([T25]) as [T25],max([T26]) as [T26],max([T27]) as [T27],max([T28])as [T28],max([T29]) as [T29],max([T30]) as [T30],max([T31]) as [T31] from  #tbb7 group by heibai)h  select *from #aaa union select * from #bbb union select * from #ccc union select * from #ddd union select * from #fff union select * from #hhh union select * from #ggg  drop table #tb01    drop table #tb001 drop table #tba1   drop table #tba2     drop table #tba3   drop table #tba4  drop table #tba5  drop table #tba6 drop table #tba7 drop table #tbb1   drop table #tbb2  drop table #tbb3  drop table #tbb4   drop table #tbb5  drop table #tbb6   drop table #tbb7   drop table #aaa   drop table #bbb  drop table #ccc  drop table #ddd  drop table #hhh   drop table #fff  drop table #ggg