sp_color -- 表名下面是字段 商品编码和颜色编码
bm_sp bm_color
1000 01
1000 02
1000 03
1001 01
1001 02
1002 04
1002 05
1002 06zb_sl -- 表名下面是字段
bm_sp bm_color
1000 02
1002 05现在的需求是想删除 sp_color表 在 zb_sl 不存在的数据 要根据颜色编码来删除 而且如果zb_sl表里面不存在 那么sp_color里面 至少要按编码
保留一条记录比如结果就是
bm_sp bm_color1000 02
1001 01
1002 05
delete from sp_color a
LEFT join zb_sl b on a.bm_sp=b.bm_sp and a.bm_color=b.bm_color
where a.bm_sp in (select bm_sp from zb_sl)
and b.bm_sp is nulldelete a from sp_color a where bm_color<any(select bm_color from sp_color where bm_sp=a.bm_sp)
drop table tempdb..zb_slcreate table sp_color -- 表名下面是字段 商品编码和颜色编码
(bm_sp varchar(4),bm_color varchar(2))
insert into sp_color
select '1000','01' union all
select '1000','02' union all
select '1000','03' union all
select '1001','01' union all
select '1001','02' union all
select '1002','04' union all
select '1002','05' union all
select '1002','06'create table zb_sl -- 表名下面是字段
(bm_sp varchar(4),bm_color varchar(2))
insert into zb_sl
select '1000','02' union all
select '1002','05'with tb as (
select * from sp_color a
where not exists (
select *
from zb_sl
where a.bm_color = bm_color )
except
select MIN(bm_sp),MIN(bm_color)
from sp_color a
where not exists (
select * from zb_sl
where a.bm_sp = bm_sp)
)
delete A --(4 行受影响)
from sp_color a
where exists (
select *
from tb
where a.bm_color=bm_color and a.bm_sp=bm_sp)
select * from sp_color
--result:
bm_sp bm_color
----- --------
1000 02
1001 01
1001 02 --原本属于zb_sl里面存在的;
1002 05(4 行受影响)
if object_id('tempdb.dbo.#Asp_color') is not null drop table #Asp_color
create table #Asp_color (bm_sp int,bm_color varchar(2))
insert into #Asp_color
select 1000,'01' union all
select 1000,'02' union all
select 1000,'03' union all
select 1001,'01' union all
select 1001,'02' union all
select 1002,'04' union all
select 1002,'05' union all
select 1002,'06'
--> 测试数据: #zb_sl
if object_id('tempdb.dbo.#zb_sl') is not null drop table #zb_sl
create table #zb_sl (bm_sp int,bm_color varchar(2))
insert into #zb_sl
select 1000,'02' union all
select 1002,'05'
--select * from #zb_sl
DELETE S FROM #Asp_color S
WHERE EXISTS(SELECT 1 FROM #zb_sl B WHERE bm_sp=S.bm_sp AND bm_color!=S.bm_color)
OR
(
NOT EXISTS(SELECT 1 FROM #zb_sl B WHERE bm_sp=S.bm_sp )
AND EXISTS(SELECT 1 FROM #Asp_color WHERE bm_sp=S.bm_sp AND S.bm_color>bm_color)
)
select * from #Asp_color
/*
bm_sp bm_color
----------- --------
1000 02
1001 01
1002 05(所影响的行数为 3 行)
IF OBJECT_ID('A') IS NOT NULL
DROP TABLE A
GOCREATE TABLE A
(
BM_SP INT,
BM_COLOR VARCHAR(5)
)
GOIF OBJECT_ID('B') IS NOT NULL
DROP TABLE B
GOCREATE TABLE B
(
BM_SP INT,
BM_COLOR VARCHAR(5)
)
GOINSERT INTO A
SELECT 1000,'01'
UNION ALL
SELECT 1000,'02'
UNION ALL
SELECT 1000,'03'
UNION ALL
SELECT 1001,'01'
UNION ALL
SELECT 1001,'02'
UNION ALL
SELECT 1002,'04'
UNION ALL
SELECT 1002,'05'
UNION ALL
SELECT 1002,'06'
GOINSERT INTO B
SELECT 1000,'02'
UNION ALL
SELECT 1002,'05'
GODELETE FROM A
WHERE NOT EXISTS
(
SELECT * FROM (
SELECT A.BM_SP,A.BM_COLOR
FROM A
JOIN B
ON A.BM_SP=B.BM_SP AND A.BM_COLOR=B.BM_COLOR
UNION
SELECT TOP 1 *
FROM A
WHERE NOT EXISTS(SELECT * FROM B WHERE A.BM_SP=B.BM_SP)
) C
WHERE A.BM_SP=C.BM_SP AND A.BM_COLOR=C.BM_COLOR
)
GOSELECT *
FROM A
GO