有表结构如下
town 代表镇,city代表城市 province代表省
town city province
T1 C1 P1
T2 C1 P1
T4 C1 P1
T3 C1 P2一个镇只能属于一个城市 ,例如 T1不能同时属于C1和C2,同样,一个市只能属于一个省份,例如 C1不能同时属于P1和P2,现在要求删除这样的垃圾数据,如上表中,要求删除最后一条记录,因为有三条C1对应P1的记录,而只有一条C1对应P2的记录,如果出现了上述小概念同时对应两个大概念的情况,删除对应少的哪一条记录,SQL怎么写阿
town 代表镇,city代表城市 province代表省
town city province
T1 C1 P1
T2 C1 P1
T4 C1 P1
T3 C1 P2一个镇只能属于一个城市 ,例如 T1不能同时属于C1和C2,同样,一个市只能属于一个省份,例如 C1不能同时属于P1和P2,现在要求删除这样的垃圾数据,如上表中,要求删除最后一条记录,因为有三条C1对应P1的记录,而只有一条C1对应P2的记录,如果出现了上述小概念同时对应两个大概念的情况,删除对应少的哪一条记录,SQL怎么写阿
as
delete a from A a,
(select city,province from
(select count(*)num,tt.city ,tt.province from
(select * from A where exists (select 1 from A as B where B.city=A.city and A.province<>B.province))tt
group by tt.city ,tt.province)tttt
where num in
(select min(num)idd from
(select count(*)num,tt.city ,tt.province from
(select * from A where exists (select 1 from A as B where B.city=A.city and A.province<>B.province))tt
group by tt.city ,tt.province)ttt))B
where A.city=B.city and A.province=B.provincedelete a from A a,
(select towm,city from
(select count(*)num,tt.towm ,tt.city from
(select * from A where exists (select 1 from A as B where B.towm=A.towm and A.city<>B.city))tt
group by tt.towm ,tt.city)tttt
where num in
(select min(num)idd from
(select count(*)num,tt.towm ,tt.city from
(select * from A where exists (select 1 from A as B where B.towm=A.towm and A.city<>B.city))tt
group by tt.towm ,tt.city)ttt))B
where A.towm=B.towm and A.city=B.city
goexec test