SQL 多次EXISTS语句优化，求大神解答

nihewo872746358 2014-06-20 10:58:38
SELECT YEAR(@ksrq) TBNF,MONTH(@ksrq) TBYF,ROW_NUMBER() OVER(ORDER BY A.XM) AS INO,B.DZBH DZBH,A.XM,
(SELECT DAXX FROM SYS1_DAZB WHERE DAZX='02' AND WTBH='0100' AND DABH=A.XB) XBDIS,
CONVERT(VARCHAR(10),A.CSRQ,120) CSRQDIS,B.FQXM FQXM,B.MQXM MQXM,A.HKDZ HKDZ,A.JZDZ JZDZ,
A.LXDH LXDH,A.LXRDH LXRDH,
(SELECT TOP 1 CONVERT(VARCHAR(10),JCRQ,120) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-00-20' AND SZNL<='0-02-00') MYJCRQ,
(SELECT TOP 1 TZ FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-00-20' AND SZNL<='0-02-00') MYTZ,
(SELECT TOP 1 SC FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-00-20' AND SZNL<='0-02-00') MYSC,

(SELECT TOP 1 (SELECT DAXX FROM SYS1_DAZB WHERE DAZX='95' AND WTBH='0206' AND DABH=TZPJ) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-00-20' AND SZNL<='0-02-00' ORDER BY JCRQ DESC) MYTZPJ,
(SELECT TOP 1 (SELECT DAXX FROM SYS1_DAZB WHERE DAZX='95' AND WTBH='0206' AND DABH=SCPJ) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-00-20' AND SZNL<='0-02-00' ORDER BY JCRQ DESC) MYSCPJ,
(SELECT TOP 1 (SELECT DAXX FROM SYS1_DAZB WHERE DAZX='95' AND WTBH='0206' AND DABH=SCBTZPJ) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-00-20' AND SZNL<='0-02-00' ORDER BY JCRQ DESC) MYSCBTZPJ,
(SELECT TOP 1 (SELECT DAXX FROM SYS1_DAZB WHERE DAZX='95' AND WTBH='0204' AND DABH=WYQK) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-00-20' AND SZNL<='0-02-00' ORDER BY JCRQ DESC) MYWYQK,
(SELECT TOP 1 CONVERT(VARCHAR(10),JCRQ,120) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-02-01' AND SZNL<='0-04-00') SYJCRQ,
(SELECT TOP 1 TZ FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-02-01' AND SZNL<='0-04-00') SYTZ,
(SELECT TOP 1 SC FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-02-01' AND SZNL<='0-04-00') SYSC,
(SELECT TOP 1 (SELECT DAXX FROM SYS1_DAZB WHERE DAZX='95' AND WTBH='0206' AND DABH=TZPJ) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-02-01' AND SZNL<='0-04-00' ORDER BY JCRQ DESC) SYTZPJ,
(SELECT TOP 1 (SELECT DAXX FROM SYS1_DAZB WHERE DAZX='95' AND WTBH='0206' AND DABH=SCPJ) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-02-01' AND SZNL<='0-04-00' ORDER BY JCRQ DESC) SYSCPJ,
(SELECT TOP 1 (SELECT DAXX FROM SYS1_DAZB WHERE DAZX='95' AND WTBH='0206' AND DABH=SCBTZPJ) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-02-01' AND SZNL<='0-04-00' ORDER BY JCRQ DESC) SYSCBTZPJ,
(SELECT TOP 1 (SELECT DAXX FROM SYS1_DAZB WHERE DAZX='95' AND WTBH='0204' AND DABH=WYQK) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-02-01' AND SZNL<='0-04-00' ORDER BY JCRQ DESC) SYWYQK,
(SELECT TOP 1 CONVERT(VARCHAR(10),JCRQ,120) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-04-01' AND SZNL<='0-07-00') LYJCRQ,
(SELECT TOP 1 TZ FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-04-01' AND SZNL<='0-07-00') LYTZ,
(SELECT TOP 1 SC FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-04-01' AND SZNL<='0-07-00') LYSC,
(SELECT TOP 1 (SELECT DAXX FROM SYS1_DAZB WHERE DAZX='95' AND WTBH='0206' AND DABH=TZPJ) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-04-01' AND SZNL<='0-07-00' ORDER BY JCRQ DESC) LYTZPJ,
(SELECT TOP 1 (SELECT DAXX FROM SYS1_DAZB WHERE DAZX='95' AND WTBH='0206' AND DABH=SCPJ) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-04-01' AND SZNL<='0-07-00' ORDER BY JCRQ DESC) LYSCPJ,
(SELECT TOP 1 (SELECT DAXX FROM SYS1_DAZB WHERE DAZX='95' AND WTBH='0206' AND DABH=SCBTZPJ) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-04-01' AND SZNL<='0-07-00' ORDER BY JCRQ DESC) LYSCBTZPJ,
(SELECT TOP 1 (SELECT DAXX FROM SYS1_DAZB WHERE DAZX='95' AND WTBH='0204' AND DABH=WYQK) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-04-01' AND SZNL<='0-07-00' ORDER BY JCRQ DESC) LYWYQK,
(SELECT TOP 1 HB FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-04-01' AND SZNL<='0-07-00' ORDER BY JCRQ DESC) LYHB,
(SELECT TOP 1 (SELECT DAXX FROM SYS1_DAZB WHERE DAZX='95' AND WTBH='0253' AND DABH=TL) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-04-01' AND SZNL<='0-07-00' ORDER BY JCRQ DESC) LYTL,
(SELECT TOP 1 GJXLSM FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-04-01' AND SZNL<='0-07-00' ORDER BY JCRQ DESC) LYBAKP,
(SELECT TOP 1 CONVERT(VARCHAR(10),JCRQ,120) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-07-01' AND SZNL<='0-10-00') BYJCRQ,
(SELECT TOP 1 TZ FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-07-01' AND SZNL<='0-10-00') BYTZ,
(SELECT TOP 1 SC FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-07-01' AND SZNL<='0-10-00') BYSC,
(SELECT TOP 1 (SELECT DAXX FROM SYS1_DAZB WHERE DAZX='95' AND WTBH='0206' AND DABH=TZPJ) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-07-01' AND SZNL<='0-10-00' ORDER BY JCRQ DESC) BYTZPJ,
(SELECT TOP 1 (SELECT DAXX FROM SYS1_DAZB WHERE DAZX='95' AND WTBH='0206' AND DABH=SCPJ) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-07-01' AND SZNL<='0-10-00' ORDER BY JCRQ DESC) BYSCPJ,
(SELECT TOP 1 (SELECT DAXX FROM SYS1_DAZB WHERE DAZX='95' AND WTBH='0206' AND DABH=SCBTZPJ) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-07-01' AND SZNL<='0-10-00' ORDER BY JCRQ DESC) BYSCBTZPJ,
(SELECT TOP 1 CONVERT(VARCHAR(10),JCRQ,120) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-10-01' AND SZNL<='1-02-00') YSJCRQ,
(SELECT TOP 1 TZ FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-10-01' AND SZNL<='1-02-00') YSTZ,
(SELECT TOP 1 SC FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-10-01' AND SZNL<='1-02-00') YSSC,
(SELECT TOP 1 (SELECT DAXX FROM SYS1_DAZB WHERE DAZX='95' AND WTBH='0206' AND DABH=TZPJ) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-10-01' AND SZNL<='1-02-00' ORDER BY JCRQ DESC) YSTZPJ,
(SELECT TOP 1 (SELECT DAXX FROM SYS1_DAZB WHERE DAZX='95' AND WTBH='0206' AND DABH=SCPJ) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-10-01' AND SZNL<='1-02-00' ORDER BY JCRQ DESC) YSSCPJ,
(SELECT TOP 1 (SELECT DAXX FROM SYS1_DAZB WHERE DAZX='95' AND WTBH='0206' AND DABH=SCBTZPJ) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-10-01' AND SZNL<='1-02-00' ORDER BY JCRQ DESC) YSSCBTZPJ,
'' YSTL,
(SELECT TOP 1 CONVERT(VARCHAR(10),JCRQ,120) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='1-02-01' AND SZNL<='1-08-00') YBYJCRQ,
(SELECT TOP 1 TZ FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='1-02-01' AND SZNL<='1-08-00') YBYTZ,
(SELECT TOP 1 SC FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='1-02-01' AND SZNL<='1-08-00') YBYSC,
(SELECT TOP 1 (SELECT DAXX FROM SYS1_DAZB WHERE DAZX='95' AND WTBH='0206' AND DABH=TZPJ) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='1-02-01' AND SZNL<='1-08-00' ORDER BY JCRQ DESC) YBYTZPJ,
(SELECT TOP 1 (SELECT DAXX FROM SYS1_DAZB WHERE DAZX='95' AND WTBH='0206' AND DABH=SCPJ) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='1-02-01' AND SZNL<='1-08-00' ORDER BY JCRQ DESC) YBYSCPJ,
(SELECT TOP 1 HB FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='1-02-01' AND SZNL<='1-08-00' ORDER BY JCRQ DESC) YBYHB,
(SELECT TOP 1 GJXLSM FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='1-02-01' AND SZNL<='1-08-00' ORDER BY JCRQ DESC) YBYBAKP,
'' YBYSL,'' YBYZS,
(SELECT TOP 1 CONVERT(VARCHAR(10),JCRQ,120) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='1-08-01' AND SZNL<='2-02-00') ESYJCRQ,
(SELECT TOP 1 TZ FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='1-08-01' AND SZNL<='2-02-00') ESYTZ,
(SELECT TOP 1 SC FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='1-08-01' AND SZNL<='2-02-00') ESYSC,
(SELECT TOP 1 (SELECT DAXX FROM SYS1_DAZB WHERE DAZX='95' AND WTBH='0206' AND DABH=TZPJ) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='1-08-01' AND SZNL<='2-02-00' ORDER BY JCRQ DESC) ESYTZPJ,
(SELECT TOP 1 (SELECT DAXX FROM SYS1_DAZB WHERE DAZX='95' AND WTBH='0206' AND DABH=SCPJ) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='1-08-01' AND SZNL<='2-02-00' ORDER BY JCRQ DESC) ESYSCPJ,
(SELECT TOP 1 (SELECT DAXX FROM SYS1_DAZB WHERE DAZX='95' AND WTBH='0206' AND DABH=SCBTZPJ) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='1-08-01' AND SZNL<='2-02-00' ORDER BY JCRQ DESC) ESYSCBTZPJ,
'' ESTL,'' ESSL,'' ESZS,
(SELECT TOP 1 CONVERT(VARCHAR(10),JCRQ,120) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='2-02-01' AND SZNL<='2-06-00') SSYJCRQ,
(SELECT TOP 1 TZ FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='2-02-01' AND SZNL<='2-06-00') SSYTZ,
'' SLTL,'' SLSL,'' SLZS
FROM DA_GR_HXDA A,EB_BJ_XXK B
WHERE A.GRBJH=B.GRBJH AND A.JLZT='0' --AND EXISTS(SELECT LSH FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0')
AND B.JCDW in (SELECT JGBM FROM #ZCJG)
AND B.JLZT='0' AND B.SFLD=@sfld AND DATEDIFF(mm,A.CSRQ,@jsrq)>=0 AND DATEDIFF(mm,A.CSRQ,@jsrq)<=36 ORDER BY A.XM

SQL2005 DA_GR_HXDA 表100W数据，EB_BJ_XXK 2.5W数据，EB_BJ_TGJC 10W数据量
现在的语句基本sqlserver接近崩溃，求大神优化语句
...全文
444 16 打赏 收藏 转发到动态 举报
写回复
用AI写文章
16 条回复
切换为时间正序
请发表友善的回复…
发表回复
nihewo872746358 2014-06-23
  • 打赏
  • 举报
 回复
分我就随便给了
nihewo872746358 2014-06-23
  • 打赏
  • 举报
 回复
引用 13 楼 Tiger_Zhao 的回复:
[code=SQL]EXISTS(SELECT LSH FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0') [/SQL] 2.5W 条 EB_BJ_XXK 总不会个个 GRBJH 都不同吧。 用 DISTINCT 或 GROUP BY （不确定那个快）选到中间表中再加个主键/索引，应该低好几个数量级吧。 然后从中间表进行 EXISTS 判断。
GRBJH是唯一的 现在的处理是业务处理是建立另一个关联表（专门查询用） 效率还不错 1分钟左右 谢谢大家了
Q315054403 2014-06-21
  • 打赏
  • 举报
 回复
真是经验很丰富的CODER开发出来的，呵呵
nihewo872746358 2014-06-20
  • 打赏
  • 举报
 回复
基本的sql语句整理好 就是这个样子的
nihewo872746358 2014-06-20
  • 打赏
  • 举报
 回复 
SELECT YEAR(@ksrq) TBNF,MONTH(@ksrq) TBYF,ROW_NUMBER() OVER(ORDER BY A.XM) AS INO,B.DZBH DZBH,A.XM,
(SELECT DAXX FROM SYS1_DAZB WHERE DAZX='02' AND WTBH='0100' AND DABH=A.XB) XBDIS,
CONVERT(VARCHAR(10),A.CSRQ,120) CSRQDIS,B.FQXM FQXM,B.MQXM MQXM,A.HKDZ HKDZ,A.JZDZ JZDZ,
A.LXDH LXDH,A.LXRDH LXRDH,
(SELECT TOP 1 CONVERT(VARCHAR(10),JCRQ,120) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-00-20' AND SZNL<='0-02-00') MYJCRQ,
(SELECT TOP 1 TZ FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-00-20' AND SZNL<='0-02-00') MYTZ,
(SELECT TOP 1 SC FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='0-00-20' AND SZNL<='0-02-00') MYSC,
XXX其它类似select top 或者exists
(SELECT TOP 1 SC FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='1-02-01' AND SZNL<='1-08-00') YBYSC,
(SELECT TOP 1 (SELECT DAXX FROM SYS1_DAZB WHERE DAZX='95' AND WTBH='0206' AND DABH=TZPJ) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='1-02-01' AND SZNL<='1-08-00' ORDER BY JCRQ DESC) YBYTZPJ,
(SELECT TOP 1 (SELECT DAXX FROM SYS1_DAZB WHERE DAZX='95' AND WTBH='0206' AND DABH=SCPJ) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='1-02-01' AND SZNL<='1-08-00' ORDER BY JCRQ DESC) YBYSCPJ,
(SELECT TOP 1 HB FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='1-02-01' AND SZNL<='1-08-00' ORDER BY JCRQ DESC) YBYHB,
(SELECT TOP 1 GJXLSM FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='1-02-01' AND SZNL<='1-08-00' ORDER BY JCRQ DESC) YBYBAKP,
'' YBYSL,'' YBYZS,
(SELECT TOP 1 CONVERT(VARCHAR(10),JCRQ,120) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='1-08-01' AND SZNL<='2-02-00') ESYJCRQ,
(SELECT TOP 1 TZ FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='1-08-01' AND SZNL<='2-02-00') ESYTZ,
(SELECT TOP 1 SC FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='1-08-01' AND SZNL<='2-02-00') ESYSC,
(SELECT TOP 1 (SELECT DAXX FROM SYS1_DAZB WHERE DAZX='95' AND WTBH='0206' AND DABH=TZPJ) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='1-08-01' AND SZNL<='2-02-00' ORDER BY JCRQ DESC) ESYTZPJ,
(SELECT TOP 1 (SELECT DAXX FROM SYS1_DAZB WHERE DAZX='95' AND WTBH='0206' AND DABH=SCPJ) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='1-08-01' AND SZNL<='2-02-00' ORDER BY JCRQ DESC) ESYSCPJ,
(SELECT TOP 1 (SELECT DAXX FROM SYS1_DAZB WHERE DAZX='95' AND WTBH='0206' AND DABH=SCBTZPJ) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='1-08-01' AND SZNL<='2-02-00' ORDER BY JCRQ DESC) ESYSCBTZPJ,
'' ESTL,'' ESSL,'' ESZS,
(SELECT TOP 1 CONVERT(VARCHAR(10),JCRQ,120) FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='2-02-01' AND SZNL<='2-06-00') SSYJCRQ,
(SELECT TOP 1 TZ FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0' AND SZNL>='2-02-01' AND SZNL<='2-06-00') SSYTZ,
'' SLTL,'' SLSL,'' SLZS
FROM DA_GR_HXDA A,EB_BJ_XXK B
WHERE A.GRBJH=B.GRBJH AND A.JLZT='0' --AND EXISTS(SELECT LSH FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0') 
AND B.JCDW in (SELECT JGBM FROM #ZCJG)
AND B.JLZT='0' AND B.SFLD=@sfld AND DATEDIFF(mm,A.CSRQ,@jsrq)>=0 AND DATEDIFF(mm,A.CSRQ,@jsrq)<=36 ORDER BY A.XM
以学习为目的 2014-06-20
  • 打赏
  • 举报
 回复
引用 4 楼 fredrickhu 的回复:
语句这么长 逻辑比较复杂。把格式编辑好以后再发吧。
复制放到查询分析器里看吧，我编辑了下格式，结果回复提示最多只能提交10000字符
以学习为目的 2014-06-20
  • 打赏
  • 举报
 回复
大概浏览了下，我觉得你这个可以把最后where条件的表先过滤下。比如你的B.JCDW in (SELECT JGBM FROM #ZCJG) A.JLZT='0' B.JLZT='0' 你可以先把过滤好的数据写到一个表中。然后再来查询。你的子查询中对一个表多次top查询，然后最后又一次order by排序。 还是看下执行计划，然后找出问题来改正。感觉你是不是匹配的排序的等关键字段没有合适的索引
--小F-- 2014-06-20
  • 打赏
  • 举报
 回复
语句这么长 逻辑比较复杂。把格式编辑好以后再发吧。
發糞塗牆 2014-06-20
  • 打赏
  • 举报
 回复
改写逻辑，这种查询看执行计划用处不大
wangnaisheng 2014-06-20
  • 打赏
  • 举报
 回复
写进存储过程吧。至少还能好点。
以学习为目的 2014-06-20
  • 打赏
  • 举报
 回复
你这个语句没有编辑格式，看起来好凌乱。 可以执行的时候Ctrl+m 看下实际的执行计划。 或者用sql自带的优化顾问，看下优化建议。包括语句的写法和索引的创建
Tiger_Zhao 2014-06-20
  • 打赏
  • 举报
 回复
[code=SQL]EXISTS(SELECT LSH FROM EB_BJ_TGJC WHERE GRBJH=B.GRBJH AND JLZT='0') [/SQL]
2.5W 条 EB_BJ_XXK 总不会个个 GRBJH 都不同吧。
用 DISTINCT 或 GROUP BY （不确定那个快）选到中间表中再加个主键/索引，应该低好几个数量级吧。
然后从中间表进行 EXISTS 判断。
asd613613 2014-06-20
  • 打赏
  • 举报
 回复
真的很复杂~
以学习为目的 2014-06-20
  • 打赏
  • 举报
 回复
引用 9 楼 nihewo872746358 的回复:
[quote=引用 4 楼 fredrickhu 的回复:] 语句这么长 逻辑比较复杂。把格式编辑好以后再发吧。
格式改好了，是不是需要建立临时表？[/quote]是的，把你where能过滤的数据先过滤掉放到临时表再操作其他的数据
skykrf 2014-06-20
  • 打赏
  • 举报
 回复
首先这样的SQL我就会给枪毙掉
nihewo872746358 2014-06-20
  • 打赏
  • 举报
 回复
引用 4 楼 fredrickhu 的回复:
语句这么长 逻辑比较复杂。把格式编辑好以后再发吧。
格式改好了，是不是需要建立临时表？
数据库性能优化之SQL语句优化
数据库性能优化之SQL语句优化
聊聊sql优化的15个小技巧，太赞了
前言 sql优化是一个大家都比较关注的热门话题，无论你在面试，还是工作中，都很有可能会遇到。...很多时候，我们写sql语句时，为了方便，喜欢直接使用select *，一次性查出表中所有列的数据。 反例： sele
把MySQL语句转换为sqlserver_mysql语句转换为sql server语句
数据库我不太懂求大神帮忙啊SETFOREIGN_KEY_CHECKS=0;DROPTABLEIFEXISTS`tb_bookcase`;CREATETABLE`tb_bookcase`(`id`int(10)unsignedNOTNULLAUTO_INCREMENT,`name`varchar(30)DEFAUL...数据库我不太懂 求大神帮忙啊...
常用的sql语句优化
常用的sql语句优化  Order by 任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。 应尽量避免在 where 子句中使用!= Not 操作符 当ORACLE"遇到"NOT,他就会停止使用索引转而执行全...
SQLServer 语句 创建索引
SQLServer 语句 创建索引
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章