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

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

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

...全文
786 46 打赏 收藏 举报
写回复
46 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
liuzixi 2005-03-02
up
  • 打赏
  • 举报
回复
wangzhenzhu1975 2005-02-21
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
  • 打赏
  • 举报
回复
DragonBill 2005-02-21
都是牛人,不服不行
  • 打赏
  • 举报
回复
mtu207 2005-02-20
呵呵,一个比一个牛!

俺是服了你们了:)
  • 打赏
  • 举报
回复
chinaandys 2005-02-20


I服了You!
  • 打赏
  • 举报
回复
灰屋 2005-02-19
厉害!
  • 打赏
  • 举报
回复
realgz 2005-01-13
我也有超8k的单句,是要把一个3表join出来的结果的部分数据横向转成纵向,再还要把一部分纵向数据转成横向。为此,写了一个存储过程,把这个语句替换关键字,运行8遍~~~~~~~~,写完以后感想就是这辈子不想再改一次这个语句。

服了这些表,居然有两部分数据结构和最终报表大概反过来。。
  • 打赏
  • 举报
回复
vinsonshen 2005-01-12
等着收集这句SQL呢~~
  • 打赏
  • 举报
回复
hai2003xp 2005-01-11
学习牛人
  • 打赏
  • 举报
回复
yibotiemen 2005-01-11
都是牛人啊,景仰
  • 打赏
  • 举报
回复
youyiwuyi 2005-01-11
select column1,column2,column3,……,column9998,column9999,column10000 from table1
  • 打赏
  • 举报
回复
chinaandys 2005-01-09
万里长城永不倒
  • 打赏
  • 举报
回复
lys1706228 2005-01-09
做记号
  • 打赏
  • 举报
回复
郭大侠_ 2005-01-08
阿婆!!
  • 打赏
  • 举报
回复
dingtongsheng 2005-01-08
以下是我写的一句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
  • 打赏
  • 举报
回复
zengzhengliang 2005-01-08
  • 打赏
  • 举报
回复
vinsonshen 2005-01-07
有结果没有呢?
  • 打赏
  • 举报
回复
imwinner 2004-12-14
how bizarre!!!
  • 打赏
  • 举报
回复
huwgao 2004-12-11
--凑个热闹
http://community.csdn.net/Expert/topic/3157/3157620.xml?temp=.3546869
两个表的查询,对其中1个表的操作定义了16个别名。
  • 打赏
  • 举报
回复
mingzaili 2004-12-11
bizarrerie
  • 打赏
  • 举报
回复
加载更多回复(26)
发帖
非技术版
加入

1.1w+

社区成员

MS-SQL Server 非技术版
社区管理员
  • 非技术版社区
申请成为管理员
帖子事件
创建了帖子
2004-12-08 08:51
社区公告
暂无公告