sql 只包含指定数据和包含其他数据的统计-优快云博客

本文通过具体的示例演示了如何使用SQL进行复杂的数据筛选操作，包括如何找出表中既包含指定数据也包含其他数据的记录，以及如何仅筛选出包含指定数据的记录。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

转载于:https://www.cnblogs.com/pato/archive/2013/03/01/2939265.html