34,571
社区成员
发帖
与我相关
我的任务
分享
-->SQL2K的另一方法:
SELECT *
FROM [TA]
WHERE CHECKSUM(*)
NOT IN (
SELECT CHECKSUM(*)
FROM [TB]
)
/*
编号 名称 状态
----------- ----------- -----------
1000 1000 0
1002 1002 1
1003 1003 1
1004 1004 1
(4 行受影响)
*/
select a.* from TA a left join TB b on a.编号=b.编号
where b.编号 is null or a.状态<>b.状态
/*
编号 名称 状态
----------- ----------- -----------
1000 1000 0
1002 1002 1
1003 1003 1
1004 1004 1
(所影响的行数为 4 行)
*/
select * from [TA] a where not exists
(select 1 from tb where 编号=a.编号 and 状态=a.状态D)
--> 生成测试数据表: [TA]
IF OBJECT_ID('[TA]') IS NOT NULL
DROP TABLE [TA]
GO
CREATE TABLE [TA] ([编号] [int],[名称] [int],[状态] [int])
INSERT INTO [TA]
SELECT '1000','1000','0' UNION ALL
SELECT '1001','1001','0' UNION ALL
SELECT '1002','1002','1' UNION ALL
SELECT '1003','1003','1' UNION ALL
SELECT '1004','1004','1' UNION ALL
SELECT '1005','1005','1'
--> 生成测试数据表: [TB]
IF OBJECT_ID('[TB]') IS NOT NULL
DROP TABLE [TB]
GO
CREATE TABLE [TB] ([编号] [int],[名称] [int],[状态] [int])
INSERT INTO [TB]
SELECT '1000','1000','1' UNION ALL
SELECT '1001','1001','0' UNION ALL
SELECT '1003','1003','0' UNION ALL
SELECT '1005','1005','1'
-->SQL查询如下:
SELECT * FROM [TA]
EXCEPT
SELECT * FROM [TB]
/*
编号 名称 状态
----------- ----------- -----------
1000 1000 0
1002 1002 1
1003 1003 1
1004 1004 1
(4 行受影响)
*/