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])