以下为目前为止我所写过的最长的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