sql 对指定数据进行统计的代码

USE wms;
--> 测试数据：#ta
IF OBJECT_ID('TEMPDB.DBO.#ta') IS NOT NULL DROP TABLE #ta
GO
CREATE TABLE #ta([name] VARCHAR(10))
INSERT #ta
SELECT 'a' UNION ALL
SELECT 'b' UNION ALL
SELECT 'd'

--> 测试数据：#tb
IF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tb
GO
CREATE TABLE #tb([id] INT,[name] VARCHAR(1))
INSERT #tb
SELECT 1,'a' UNION ALL
SELECT 1,'b' UNION ALL
SELECT 1,'c' UNION ALL
SELECT 2,'a' UNION ALL
SELECT 2,'a' UNION ALL
SELECT 3,'a' UNION ALL
SELECT 3,'b' UNION ALL
SELECT 4,'b' UNION ALL
SELECT 4,'d' UNION ALL
SELECT 5,'c'

/*只包含指定数据*/
SELECT [id] FROM #tb AS b
JOIN #ta AS a ON a.[name]=b.[name]
EXCEPT
SELECT [id] FROM #tb AS b
WHERE NOT EXISTS (SELECT 1 FROM #ta AS t WHERE t.[name]=b.[name])

/*包含指定数据和其他数据*/
SELECT [id] FROM #tb AS b
WHERE EXISTS (SELECT 1 FROM #ta AS a WHERE a.[name]=b.[name])
INTERSECT
SELECT [id] FROM #tb AS b
WHERE NOT EXISTS (SELECT 1 FROM #ta AS t WHERE t.[name]=b.[name])