表1
DJBH SPDM
BH000015334 8F25004
BH000015334 8J25003
BH000015335 8J25003
BH000015335 8J25101
BH000015335 8J25004
BH000015335 8J65002
BH000015335 8N25003表2
DJBH SPDM GG1 GG2
BH000015334 8F25004 150 5
BH000015334 8F25004 150 6
BH000015334 8F25004 200 5
BH000015334 8F25004 200 6
BH000015334 8J25003 303 5
BH000015334 8J25003 303 6
BH000015334 8N25003 200 27
BH000015334 8N25003 200 28
BH000015334 8N25003 200 29
BH000015335 8J25101 259 6
BH000015335 8J25101 259 7
BH000015335 8J25004 105 5
BH000015335 8J25004 105 6
BH000015335 8J25004 500 5
BH000015335 8J25004 500 6
BH000015335 8J65002 154 5
BH000015335 8J65002 154 6
BH000015335 8J65002 154 7
BH000015335 8N25003 200 28
BH000015335 8N25003 200 29
如何得到表2的下面这些数据:
BH000015334 8N25003 200 27
BH000015334 8N25003 200 28
BH000015334 8N25003 200 29
就是表2内 DJBH 与 表1相同,SPDM 不存在 表1的数据请各位帮忙一下
DJBH SPDM
BH000015334 8F25004
BH000015334 8J25003
BH000015335 8J25003
BH000015335 8J25101
BH000015335 8J25004
BH000015335 8J65002
BH000015335 8N25003表2
DJBH SPDM GG1 GG2
BH000015334 8F25004 150 5
BH000015334 8F25004 150 6
BH000015334 8F25004 200 5
BH000015334 8F25004 200 6
BH000015334 8J25003 303 5
BH000015334 8J25003 303 6
BH000015334 8N25003 200 27
BH000015334 8N25003 200 28
BH000015334 8N25003 200 29
BH000015335 8J25101 259 6
BH000015335 8J25101 259 7
BH000015335 8J25004 105 5
BH000015335 8J25004 105 6
BH000015335 8J25004 500 5
BH000015335 8J25004 500 6
BH000015335 8J65002 154 5
BH000015335 8J65002 154 6
BH000015335 8J65002 154 7
BH000015335 8N25003 200 28
BH000015335 8N25003 200 29
如何得到表2的下面这些数据:
BH000015334 8N25003 200 27
BH000015334 8N25003 200 28
BH000015334 8N25003 200 29
就是表2内 DJBH 与 表1相同,SPDM 不存在 表1的数据请各位帮忙一下
-- Author: liangCK 小梁
-- Date : 2008-11-23 16:40:17
---------------------------------
--> 生成测试数据: @表1
DECLARE @表1 TABLE (DJBH VARCHAR(11),SPDM VARCHAR(7))
INSERT INTO @表1
SELECT 'BH000015334','8F25004' UNION ALL
SELECT 'BH000015334','8J25003' UNION ALL
SELECT 'BH000015335','8J25003' UNION ALL
SELECT 'BH000015335','8J25101' UNION ALL
SELECT 'BH000015335','8J25004' UNION ALL
SELECT 'BH000015335','8J65002' UNION ALL
SELECT 'BH000015335','8N25003'
--> 生成测试数据: @表2
DECLARE @表2 TABLE (DJBH VARCHAR(11),SPDM VARCHAR(7),GG1 INT,GG2 INT)
INSERT INTO @表2
SELECT 'BH000015334','8F25004',150,5 UNION ALL
SELECT 'BH000015334','8F25004',150,6 UNION ALL
SELECT 'BH000015334','8F25004',200,5 UNION ALL
SELECT 'BH000015334','8F25004',200,6 UNION ALL
SELECT 'BH000015334','8J25003',303,5 UNION ALL
SELECT 'BH000015334','8J25003',303,6 UNION ALL
SELECT 'BH000015334','8N25003',200,27 UNION ALL
SELECT 'BH000015334','8N25003',200,28 UNION ALL
SELECT 'BH000015334','8N25003',200,29 UNION ALL
SELECT 'BH000015335','8J25101',259,6 UNION ALL
SELECT 'BH000015335','8J25101',259,7 UNION ALL
SELECT 'BH000015335','8J25004',105,5 UNION ALL
SELECT 'BH000015335','8J25004',105,6 UNION ALL
SELECT 'BH000015335','8J25004',500,5 UNION ALL
SELECT 'BH000015335','8J25004',500,6 UNION ALL
SELECT 'BH000015335','8J65002',154,5 UNION ALL
SELECT 'BH000015335','8J65002',154,6 UNION ALL
SELECT 'BH000015335','8J65002',154,7 UNION ALL
SELECT 'BH000015335','8N25003',200,28 UNION ALL
SELECT 'BH000015335','8N25003',200,29--SQL查询如下:SELECT *
FROM @表2 AS t
WHERE NOT EXISTS(
SELECT *
FROM @表1
WHERE DJBH=t.DJBH
AND SPDM=t.SPDM
)/*
DJBH SPDM GG1 GG2
----------- ------- ----------- -----------
BH000015334 8N25003 200 27
BH000015334 8N25003 200 28
BH000015334 8N25003 200 29(3 行受影响)
*/
insert into tb1 values('BH000015334' ,'8F25004')
insert into tb1 values('BH000015334' ,'8J25003')
insert into tb1 values('BH000015335' ,'8J25003')
insert into tb1 values('BH000015335' ,'8J25101')
insert into tb1 values('BH000015335' ,'8J25004')
insert into tb1 values('BH000015335' ,'8J65002')
insert into tb1 values('BH000015335' ,'8N25003')
create table tb2(DJBH varchar(20) , SPDM varchar(20),GG1 int,GG2 int)
insert into tb2 values('BH000015334' ,'8F25004', 150 ,5 )
insert into tb2 values('BH000015334' ,'8F25004', 150 ,6 )
insert into tb2 values('BH000015334' ,'8F25004', 200 ,5 )
insert into tb2 values('BH000015334' ,'8F25004', 200 ,6 )
insert into tb2 values('BH000015334' ,'8J25003', 303 ,5 )
insert into tb2 values('BH000015334' ,'8J25003', 303 ,6 )
insert into tb2 values('BH000015334' ,'8N25003', 200 ,27 )
insert into tb2 values('BH000015334' ,'8N25003', 200 ,28 )
insert into tb2 values('BH000015334' ,'8N25003', 200 ,29 )
insert into tb2 values('BH000015335' ,'8J25101', 259 ,6 )
insert into tb2 values('BH000015335' ,'8J25101', 259 ,7 )
insert into tb2 values('BH000015335' ,'8J25004', 105 ,5 )
insert into tb2 values('BH000015335' ,'8J25004', 105 ,6 )
insert into tb2 values('BH000015335' ,'8J25004', 500 ,5 )
insert into tb2 values('BH000015335' ,'8J25004', 500 ,6 )
insert into tb2 values('BH000015335' ,'8J65002', 154 ,5 )
insert into tb2 values('BH000015335' ,'8J65002', 154 ,6 )
insert into tb2 values('BH000015335' ,'8J65002', 154 ,7 )
insert into tb2 values('BH000015335' ,'8N25003', 200 ,28 )
insert into tb2 values('BH000015335' ,'8N25003', 200 ,29 )
goselect m.* from tb2 m where not exists(select 1 from tb1 n where n.DJBH = m.DJBH and n.SPDM = m.SPDM )drop table tb1,tb2/*
DJBH SPDM GG1 GG2
-------------------- -------------------- ----------- -----------
BH000015334 8N25003 200 27
BH000015334 8N25003 200 28
BH000015334 8N25003 200 29(所影响的行数为 3 行)
*/
WHERE DJBH IN(SELECT DJBH FROM 表1) AND
SPDM NOT IN (SELECT SPDM FROM 表1)