以下列举了公用表/临时表/聚合函数三个因素为例子(覆盖索引因素除外,有利用此类索引都会以索引顺序)
环境:
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
)