《评选》2004年度最长一句 SQL 奖 (爆笑、变态、无用者优先)

[playyuer]

参阅:
求购世界上最长的一条SQL语句~暴笑优先
http://community.csdn.net/Expert/TopicView.asp?id=3370686

不要跟我争,否则本年度我就真地颗粒无收了!

[mingzaili]

bizarrerie

[huwgao]

--凑个热闹
http://community.csdn.net/Expert/topic/3157/3157620.xml?temp=.3546869
两个表的查询，对其中1个表的操作定义了16个别名。

[imwinner]

how bizarre!!!

[vinsonshen]

有结果没有呢？

[zengzhengliang]

顶

[dingtongsheng]

以下是我写的一句SQL语句(一个巨长存储过程的一句而已:))，用于公司的数据操作，长度还可以哦!
见笑了：
Select A.Work_Order,A.Part_Num,A.QCI_P_N,M.WO_Qty,A.This_Qty,N.Other_Qty,
M.WO_Qty-N.Other_Qty-A.This_Qty As Not_Qty ,
Model=
Case
when ((A.Part_Num like'Z07D%'Or A.Part_Num like'Z07E%' Or A.Part_Num like'K150%' Or A.Part_Num like'K147%') And A.QCI_P_N like '1Q16%' )Then 'Q16'
when ((A.Part_Num like 'Z0A2%' Or A.Part_Num like 'Z0A3%' or A.Part_Num like 'K163%' or A.Part_Num like 'K1649%' or A.Part_Num like 'K1650%' or A.Part_Num like 'K1671%' or A.Part_Num like 'K1693%' or A.Part_Num like 'K1698%' or A.Part_Num like 'K1687%' or A.Part_Num like 'K1715%' or A.Part_Num like 'K1716%' or A.Part_Num like 'K1722%' or A.Part_Num like 'K1738%' or A.Part_Num like 'K1763%') And A.QCI_P_N like '1Q16%' )Then 'Q16A'
when ((A.Part_Num like'Z07X%') And A.QCI_P_N like '1Q41%' )Then 'Q41'
when ((A.Part_Num Like 'Z0AF%' or A.Part_Num like 'K1651%' or A.Part_Num like 'K1673%' or A.Part_Num like 'K1694%' or A.Part_Num like 'K1723%' or A.Part_Num like 'K1767%') And A.QCI_P_N like '1Q41%' )Then 'Q41A'
End,
A.Country,A.Bundle_Ite
Into #TableInputCTONotebook
from #TempForInputCTONotebook A,
( Select Count(Production_Reference) As WO_Qty,Production_Reference from Tsestatu ---WO_Qty
where Production_Reference In (Select Work_Order from #TempForInputCTONotebook)
Group by Production_Reference) M,
( Select Count(A.PAL_SN) As Other_Qty,B.Production_Reference from PAL_FirstPTL A,Tsestatu B
where B.Production_Reference In (Select Work_Order from #TempForInputCTONotebook)
and A.TransDateTime Not between @TempstrFromDate+@TempstrFromTime and @TempstrToDate + @TempstrToTime
and A.PAL_SN=B.Serial_Number
Group by B.Production_Reference
Union All
Select '0' As Other_Qty,Work_Order from #TempForInputCTONotebook
where Work_Order Not In (
Select P.Production_Reference from (
Select Count(A.PAL_SN) As Other_Qty,B.Production_Reference from PAL_FirstPTL A,Tsestatu B
where B.Production_Reference In (Select Work_Order from #TempForInputCTONotebook)
and A.TransDateTime Not between @TempstrFromDate+@TempstrFromTime and @TempstrToDate + @TempstrToTime
and A.PAL_SN=B.Serial_Number
Group by B.Production_Reference
) P
)
) N
where A.Work_Order=M.Production_Reference
and A.Work_Order=N.Production_Reference
Order by Model

[郭大侠_]

阿婆！！

[lys1706228]

做记号

[chinaandys]

万里长城永不倒

[youyiwuyi]

select column1,column2,column3,……，column9998,column9999,column10000 from table1

[yibotiemen]

都是牛人啊，景仰

[hai2003xp]

学习牛人

[vinsonshen]

等着收集这句SQL呢~~

[realgz]

我也有超8k的单句,是要把一个3表join出来的结果的部分数据横向转成纵向,再还要把一部分纵向数据转成横向。为此,写了一个存储过程,把这个语句替换关键字,运行8遍~~~~~~~~，写完以后感想就是这辈子不想再改一次这个语句。

服了这些表,居然有两部分数据结构和最终报表大概反过来。。

[灰屋]

厉害!

[chinaandys]

I服了You!

[mtu207]

呵呵，一个比一个牛！

俺是服了你们了：）

[DragonBill]

都是牛人,不服不行

[wangzhenzhu1975]

ALTER proc W_GetNowMission @Begintm as datetime,@Endtm as datetime
as

(SELECT CASES.CASEID,
CASESTAT.DSC AS STATUS,
CASES.CRTM,CONTACT.NAME,CONTACT.ADDRESS,contact.allphn,HSINFO.DSC AS HOUSEGROUP,CASES.BRIEFDSC NOTE
FROM CASES
LEFT JOIN CONTACT ON CONTACT.CONTACTID=CASES.CONTACTID
LEFT JOIN HSINFO ON HSINFO.ID=CONTACT.HOUSEGROUP AND TID='HOUSEGROUP'
LEFT JOIN CASESTAT ON CASESTAT.CASESTATID=CASES.STATUS
WHERE (cases.STATUS=34 OR CASES.STATUS=32 or cases.status=0)
and cases.casetypeid<>'ts'
AND CASES.CRTM>=@Begintm
AND CASES.CRTM<=@Endtm
and cases.caseid not in (select caseid from casenote group by caseid))
union

(SELECT CASES.CASEID,
CASESTAT.DSC AS STATUS,
CASES.CRTM,CONTACT.NAME,CONTACT.ADDRESS,contact.allphn,HSINFO.DSC AS HOUSEGROUP,CASES.BRIEFDSC NOTE
FROM CASES
LEFT JOIN CONTACT ON CONTACT.CONTACTID=CASES.CONTACTID
LEFT JOIN HSINFO ON HSINFO.ID=CONTACT.HOUSEGROUP AND TID='HOUSEGROUP'
LEFT JOIN CASESTAT ON CASESTAT.CASESTATID=CASES.STATUS
left join complete_non on complete_non.caseid=cases.caseid
WHERE (cases.status=41 or cases.status=43)
and cases.casetypeid<>'ts'
AND complete_non.mdtm>=@Begintm
AND complete_non.mdtm<=@Endtm
and cases.caseid not in (select caseid from casenote group by caseid))


union
(SELECT CASES.CASEID,
CASESTAT.DSC AS STATUS,
CASES.CRTM,CONTACT.NAME,CONTACT.ADDRESS,contact.allphn,HSINFO.DSC AS HOUSEGROUP,CASES.BRIEFDSC NOTE
FROM CASES
LEFT JOIN CONTACT ON CONTACT.CONTACTID=CASES.CONTACTID
LEFT JOIN HSINFO ON HSINFO.ID=CONTACT.HOUSEGROUP AND TID='HOUSEGROUP'
LEFT JOIN CASESTAT ON CASESTAT.CASESTATID=CASES.STATUS
inner join
(select casenote.* from casenote inner join
(select caseid,MAX(mdtm) as mdtm from casenote where mdtm>= @Begintm and mdtm<=@Endtm group by caseid) temp2
on temp2.caseid=casenote.caseid and casenote.mdtm=temp2.mdtm and casenote.usrid in (select usrid from operator where defgrp='agent')) temp3
on temp3.caseid=cases.caseid
WHERE ((cases.status>12 and cases.status<99) or cases.status=0)
and cases.casetypeid<>'ts' )

union

(SELECT CASES.CASEID,
CASESTAT.DSC AS STATUS,
CASES.CRTM,CONTACT.NAME,CONTACT.ADDRESS,contact.allphn,HSINFO.DSC AS HOUSEGROUP,CASES.BRIEFDSC NOTE
FROM CASES
LEFT JOIN CONTACT ON CONTACT.CONTACTID=CASES.CONTACTID
LEFT JOIN HSINFO ON HSINFO.ID=CONTACT.HOUSEGROUP AND TID='HOUSEGROUP'
LEFT JOIN CASESTAT ON CASESTAT.CASESTATID=CASES.STATUS
left join complete_non on complete_non.caseid=cases.caseid
inner join
(select casenote.* from casenote inner join
(select caseid,MAX(mdtm) as mdtm from casenote group by caseid) temp2
on temp2.caseid=casenote.caseid and casenote.mdtm=temp2.mdtm
and casenote.usrid in (select usrid from operator where defgrp='diaodu')) temp3
on temp3.caseid=complete_non.caseid
WHERE (cases.status=41 or cases.status=43)
and cases.casetypeid<>'ts'
AND complete_non.mdtm>=@Begintm
AND complete_non.mdtm<=@Endtm
and complete_non.mdtm>temp3.mdtm

)



order by crtm desc

[liuzixi]

up