name Item value tag
John ITCH 89.33 1
John ITCH 33.22 2
Alex ITCH 67.77 3
Jacky ITCH 89.01 1
Alex YHTC 34.22 2
Bruce YHTC 35.11 2
Bruce YHTC 89.11 3
...
这张表有一个缺憾,在Item ITCH上,John有两笔记录(1/2),同样,在YHTC项目,Bruce也有两笔记录(2/3).这是不符合规则的.
目前需求是,对于同一个项目,每人只能取第一笔数据.也就是John tag2和Bruce tag3是不合法的,需要删除.
问题是,这张表已经是一张查询表了,那么该怎么编写SQL才能从这张表中删除不需要的数据?
注意,这是access的环境,oracle的一些SQL语句是失效的.
delete a
from ta a where exists(select 1 from ta where name=a.name and Item = a.Item and flag <a.flag) |
delete a
from ta a where tag <> (select min(tag) from ta where name=a.name and Item = a.Item )
delete a
from ta a where tag in (select min(tag) from ta where name=a.name and Item = a.Item )
select * from tb t
where not exists(select * from tb where t.name=name and t.item=item and tag<t.tag )
select a.CateNo,a.CateName,(select top 1 TagsPath from T_sol_tags where a.CateNo=CateNo order by TagsPath asc) as TagsPath from T_sol_tagscate a order by CateNo desc ,Tagspath asc