表t_products
col_htbh col_cpbh col_gcbh
001 A001 H1
001 A001 H2
002 A002 H1
002 A002 H2我现在想删除col_htbh,col_cpbh有相同的,而且col_gcbh=H1的数据,怎么写?
删除后得到的结果为:col_htbh col_cpbh col_gcbh
001 A001 H2002 A002 H2
col_htbh col_cpbh col_gcbh
001 A001 H1
001 A001 H2
002 A002 H1
002 A002 H2我现在想删除col_htbh,col_cpbh有相同的,而且col_gcbh=H1的数据,怎么写?
删除后得到的结果为:col_htbh col_cpbh col_gcbh
001 A001 H2002 A002 H2
insert t_products select '001', 'A001', 'H1'
union all select '001', 'A001', 'H2'
union all select '002', 'A002', 'H1'
union all select '002', 'A002', 'H2'
delete t_products from (
select col_htbh, col_cpbh from t_products
group by col_htbh, col_cpbh
having count(*)>1
) tmp
where t_products.col_htbh=tmp.col_htbh and t_products.col_cpbh=tmp.col_cpbh
and col_gcbh='H1'
col_htbh col_cpbh col_gcbh
-------- -------- --------
001 A001 H2
002 A002 H2(2 row(s) affected)
create table t_products(col_htbh char(3), col_cpbh char(4), col_gcbh char(2))
insert t_products select '001', 'A001', 'H1'
union all select '001', 'A001', 'H2'
union all select '002', 'A002', 'H1'
union all select '002', 'A002', 'H2'
--查询要删的数据
select * from t_products ta --ta为表的别名
where col_gcbh =(select top 1 col_gcbh from t_products where col_htbh=ta.col_htbh and col_cpbh=ta.col_cpbh )
--删除查询结果
delete ta from t_products ta --ta为表的别名
where col_gcbh =(select top 1 col_gcbh from t_products where col_htbh=ta.col_htbh and col_cpbh=ta.col_cpbh )
from t_products
where col_gcbh = 'H1'
and exists(select 1 from t_products T1 where T1.col_gcbh <> 'H1' and T1.col_htbh = col_htbh and T1.col_cpbh = col_cpbh)
delete from t_products
where
( col_htbh+col_cpbh in (select col_htbh+col_cpbh from t_products group by col_htbh+col_cpbh having count(*)>1) and col_gcbh='H1'
insert #t select 001, 'A001', 'H1'
union all select 001, 'A001', 'H2'
union all select 002, 'A002', 'H1'
union all select 002, 'A002', 'H2'select count(*) as col_count,col_htbh,col_cpbh
into #temp
from #t
group by col_htbh,col_cpbhdelete from #t
where col_htbh in (select col_htbh from #temp where col_count>0)
and col_cpbh in (select col_cpbh from #temp where col_count>0)
and col_gcbh = 'h1'drop table #t
drop table #temp
--給結果集起一個別名
create table t_products(col_htbh char(3), col_cpbh char(4), col_gcbh char(2))
insert t_products select '001', 'A001', 'H1'
union all select '001', 'A001', 'H2'
union all select '002', 'A002', 'H1'
union all select '002', 'A002', 'H2'
--查询要删的数据
select * from t_products ta --ta为表的别名
where not exists (select * from t_products where col_htbh=ta.col_htbh and col_cpbh=ta.col_cpbh and col_gcbh<ta.col_gcbh )--删除查询结果
delete ta from t_products ta --ta为表的别名
where not exists (select * from t_products where col_htbh=ta.col_htbh and col_cpbh=ta.col_cpbh and col_gcbh<ta.col_gcbh )
col_htbh col_cpbh col_gcbh
001 A001 H1
001 A001 H2
002 A002 H1
002 A002 H2delete from t_products where col_htbh in (select col_htbh from t_products group by col_htbh having count(*) > 1) and col_gcbh = 'H1'
insert @t select '001', 'A001', 'H1'
union all select '001', 'A001', 'H2'
union all select '002', 'A002', 'H1'
union all select '002', 'A002', 'H2'
delete t from @t t
where (select count(*) from @t where col_htbh=t.col_htbh and col_cpbh=t.col_cpbh)>1 and col_gcbh='H1'select * from @t(所影响的行数为 4 行)
(所影响的行数为 2 行)col_htbh col_cpbh col_gcbh
-------- -------- --------
001 A001 H2
002 A002 H2(所影响的行数为 2 行)