sql 对指定数据进行统计的代码
本文导语: 统计指定数据的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('TEMPD...
统计指定数据的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])
您可能感兴趣的文章:
本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。