一个SQL语句的优化(sqlserver)

最早的写法:

jquery if条件语句的写法,jqueryif语句写法

//写个网页用了多门语言,脑袋转不过来亚!

//代码:

if(){} 
else if(){} 
else {} 

以上这篇jquery
if条件语句的写法就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持帮客之家。

http://www.bkjia.com/Javascript/1128176.htmlwww.bkjia.comtruehttp://www.bkjia.com/Javascript/1128176.htmlTechArticlejquery if条件语句的写法,jqueryif语句写法
//写个网页用了多门语言,脑袋转不过来亚! //代码: if(){} else if(){} else
{} 以上这篇jquery if条件语句…

WITH T AS
(SELECT case when Col1 IS NULL OR Col1=N'' then Col2 else Col1 end as Code,case when Col1 IS NULL OR Col1=N'' then 1 else 0 end as Flag FROM YM  WHERE Col_076 BETWEEN '2018-07-25' AND '2018-08-03' AND Col_478=N'xx' AND Col_346 LIKE N'%dd%'),
D AS (SELECT Code,province,city  FROM Adds)
SELECT province AS 省,city as 市,COUNT(1) as 票数 FROM 
(SELECT A.DR_250 as province,A.DR_251 as city FROM T INNER JOIN TB AS A ON A.DR_203=T.Code WHERE T.Flag=0
 UNION ALL
 SELECT D.province,D.city FROM T INNER JOIN D ON D.Code=T.Code WHERE T.Flag=1
 UNION ALL
 SELECT '' AS province,'' AS city FROM T WHERE Code IS NULL OR Code=N'') AS S 
GROUP BY province,city;

 

最新的写法:

SELECT case when Col1 IS NULL OR Col1=N'' then Col2 else Col1 end as Code,case when Col1 IS NULL OR Col1=N'' then 1 else 0 end as Flag into #T FROM YM WHERE Col_076 BETWEEN '2018-07-25' AND '2018-08-03' AND Col_478=N'xx' AND Col_346 LIKE N'%dd%';
SELECT Code,province,city into #D  FROM Adds;
SELECT province AS 省,city as 市,COUNT(1) as 票数 FROM 
(SELECT A.DR_250 as province,A.DR_251 as city FROM #T INNER JOIN TB AS A ON A.DR_203=#T.Code WHERE #T.Flag=0
UNION ALL
SELECT #D.province,#D.city FROM #T INNER JOIN #D ON #D.Code=#T.Code WHERE #T.Flag=1
UNION ALL
SELECT '' AS province,'' AS city FROM #T WHERE Code IS NULL OR Code=N'') AS S GROUP BY province,city;
DROP table #T;
DROP table #D;

新的写法比原始写法性能高出太多(原语句执行会超时),最大的原因是对with语句理解有误!!!