34,587
社区成员
发帖
与我相关
我的任务
分享
--公用表表达式
--1、
;WITH CTE
AS
(
SELECT 1 AS ID,2 AS ID2
UNION ALL
SELECT 2 AS ID,1 AS ID2
)
SELECT ID,ID2 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
)
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
)
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
)
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;