需求: 需要删除资料表(sp_color)里面的颜色资料。(因为一个商品对应了很多种颜色,比如白色。黑色==,现在就是想删除多余的颜色值保留一个颜色即可。但是有又一些商品已经有库存。那么有库存的则不删除 没库存的则颜色则删除。但是一个商品如果有3种颜色都没库存那么 要保留一种颜色)但是如果有 库存(kc_sl)的颜色则保存,
那么如果资料表(sp_color)没有库存的则保留一条记录 比如下面数据 1003 是没有做过进货的
所以只保留一条颜色记录那么就删除 颜色编码为02的了。--表 (sp_color)
--商品编码 颜色编码
bm_sp bm_color1000 01
1000 02
1000 03
1000 04
1001 01
1001 02
1001 03
1002 01
1002 02
1003 01
1003 02
-- 库存表(kc_sl)
--商品编码 颜色编码
bm_sp bm_color
1000 02
1000 03
1001 01
1002 01----- 那么语句运行之后资料表 的结果是 1000 02
1000 03
1001 01
1002 01
1003 01
那么如果资料表(sp_color)没有库存的则保留一条记录 比如下面数据 1003 是没有做过进货的
所以只保留一条颜色记录那么就删除 颜色编码为02的了。--表 (sp_color)
--商品编码 颜色编码
bm_sp bm_color1000 01
1000 02
1000 03
1000 04
1001 01
1001 02
1001 03
1002 01
1002 02
1003 01
1003 02
-- 库存表(kc_sl)
--商品编码 颜色编码
bm_sp bm_color
1000 02
1000 03
1001 01
1002 01----- 那么语句运行之后资料表 的结果是 1000 02
1000 03
1001 01
1002 01
1003 01
or(a.bm_sp<>b.bm_sp and not exists(select 1 from sp_color where bm_sp=a.bm_sp and bm_color<a.bm_color))
if object_id('sp_color') is not null
drop table sp_color
if object_id('kc_sl') is not null
drop table kc_slcreate table sp_color(bm_sp varchar(10), bm_color varchar(10))
create table kc_sl(bm_sp varchar(10), bm_color varchar(10))insert sp_color
select '1000','01' union all
select '1000','02' union all
select '1000','03' union all
select '1000','04' union all
select '1001','01' union all
select '1001','02' union all
select '1001','03' union all
select '1002','01' union all
select '1002','02' union all
select '1003','01' union all
select '1003','02'insert kc_sl
select '1000','02' union all
select '1000','03' union all
select '1001','01' union all
select '1002','01'delete a from sp_color a where exists(select 1 from kc_sl where a.bm_sp=bm_sp) and not exists(select 1 from kc_sl where a.bm_sp=bm_sp and a.bm_color=bm_color)
delete a from sp_color a where not exists(select 1 from kc_sl where a.bm_sp=bm_sp) and exists(select 1 from sp_color where a.bm_sp=bm_sp and a.bm_color>bm_color)select * from sp_color
/*
1000 02
1000 03
1001 01
1002 01
1003 01
*/
create table kc_sl(bm_sp varchar(10), bm_color varchar(10))insert sp_color
select '1000','01' union all
select '1000','02' union all
select '1000','03' union all
select '1000','04' union all
select '1001','01' union all
select '1001','02' union all
select '1001','03' union all
select '1002','01' union all
select '1002','02' union all
select '1003','01' union all
select '1003','02'insert kc_sl
select '1000','02' union all
select '1000','03' union all
select '1001','01' union all
select '1002','01'delete from sp_color where not exists (select * from (select s.* from sp_color s,kc_sl k where s.bm_sp = k.bm_sp and s.bm_color = k.bm_color union
select top 1 s.* from sp_color s where bm_sp not in (select distinct bm_sp from kc_sl)) a where a.bm_sp = sp_color.bm_sp and a.bm_color = sp_color.bm_color)select * from sp_color1000 02
1000 03
1001 01
1002 01
1003 01