随笔- 94  评论- 2  文章- 0 

分享小知识:善用Group By排序

以下列举了公用表/临时表/聚合函数三个因素为例子(覆盖索引因素除外,有利用此类索引都会以索引顺序)

 环境:
Microsoft SQL Server 2014 (SP1-GDR) (KB3194720) - 12.0.4232.0 (X64) 
	Sep 23 2016 18:45:14 
	Copyright (c) Microsoft Corporation
	Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 10240: )

  

--公用表表达式
--1、
;WITH CTE
AS
(
SELECT 1 AS ID,2 AS ID2
UNION ALL
SELECT 2 AS ID,1 AS ID2
UNION ALL
SELECT 0 AS ID,0 AS ID2
)
SELECT ID2,ID FROM CTE GROUP BY ID,ID2;

--2、
;WITH CTE
AS
(
SELECT 1 AS ID,2 AS ID2
UNION ALL
SELECT 2 AS ID,1 AS ID2
UNION ALL
SELECT 0 AS ID,0 AS ID2
)
SELECT ID,ID2,COUNT(*) FROM CTE GROUP BY ID,ID2;

--3、
;WITH CTE
AS
(
SELECT 1 AS ID,2 AS ID2
UNION ALL
SELECT 2 AS ID,1 AS ID2
UNION ALL
SELECT 0 AS ID,0 AS ID2
)
SELECT ID,ID2 FROM CTE GROUP BY ID2,ID;

--4、
;WITH CTE
AS
(
SELECT 1 AS ID,2 AS ID2
UNION ALL
SELECT 2 AS ID,1 AS ID2
UNION ALL
SELECT 0 AS ID,0 AS ID2
)
SELECT ID,ID2,COUNT(*) FROM CTE GROUP BY ID2,ID;

  

--临时表
IF OBJECT_ID('Tempdb..#CTE') IS NOT NULL
	DROP TABLE #CTE;
SELECT 1 AS ID,2 AS ID2 INTO #CTE
UNION ALL
SELECT 2 AS ID,1 AS ID2
--5、
SELECT ID,ID2,COUNT(*) FROM #CTE GROUP BY ID,ID2;
--6、
SELECT ID,ID2 FROM #CTE GROUP BY ID,ID2;
--7、
SELECT ID,ID2,COUNT(*) FROM #CTE GROUP BY ID2,ID;
--8、
SELECT ID,ID2 FROM #CTE GROUP BY ID2,ID;

显示效果:

结论:表按分组列(group by 后)顺序没聚合函数时 从左到右,非则反之。特殊情况CTE时按存储显示列(SELECT)顺序从左到右
这一部分的列存储顺序

;WITH CTE
AS
(
SELECT 1 AS ID,2 AS ID2
UNION ALL
SELECT 2 AS ID,1 AS ID2
UNION ALL
SELECT 0 AS ID,0 AS ID2
)

 

posted on 2017-03-17 16:59 中國風 阅读(...) 评论(...) 编辑 收藏