1.索引使用 

sqlserver聚集索引是真正加快查询速度的东西，而且索引在查询中能否优化是看你建立的索引是否是在你查询的条件或者group by语句中，而不是你的select查询出来的结果字段上。

 

2.union all/union

 

select sum(a.AddPnt1) as num,a.ChrId1 FROM(

    select ChrId1,AddPnt1 from W_GldMatchBalanceLog where LogTime BETWEEN '2017-08-14' and '2017-08-15'

UNION ALL

    select ChrId2,AddPnt2 from W_GldMatchBalanceLog where LogTime BETWEEN '2017-08-14' and '2017-08-15'

UNION ALL

    select ChrId3,AddPnt3 from W_GldMatchBalanceLog where LogTime BETWEEN '2017-08-14' and '2017-08-15'

UNION ALL

    select ChrId4,AddPnt4 from W_GldMatchBalanceLog where LogTime BETWEEN '2017-08-14' and '2017-08-15') as a where a.ChrId1 <> 0 GROUP BY ChrId1 ORDER BY num DESC

1

2

3

4

5

6

7

8

9

当你需要取同一个查询结果中的多个值时你可能需要多长查询然后再union而sql2005以后出现了相当于面向对象语言里面的变量的概念的关键字with as能够把查询的结果存起来

 

select sum(a.AddPnt1) as num,a.ChrId1 FROM(

with cr AS

(SELECT ChrId1,AddPnt1,ChrId2,AddPnt2,ChrId3,AddPnt3,ChrId4,AddPnt4 from W_GldMatchBalanceLog where  LogTime BETWEEN '2017-08-14' and '2017-08-15')

SELECT ChrId1,AddPnt1 FROM cr

UNION ALL

SELECT ChrId2,AddPnt2 FROM cr

UNION ALL

SELECT ChrId3,AddPnt3 FROM cr

UNION ALL

SELECT ChrId4,AddPnt4 FROM cr)

1

2

3

4

5

6

7

8

9

10

下面代码的效率是上面的一倍多，而且数据量越大差距越明显

--------------------- 

作者：彭梦佳gogogo 

来源：CSDN 

原文：https://blog.csdn.net/peng1037801608/article/details/78530972 

版权声明：本文为博主原创文章，转载请附上博文链接！