我从一张表里按照条件提取如下纪录集合 patient_id opeartion_name opeartion_date 、、、、
1001 阑尾切除术 2005-01-01
1001 胃大部切除术 2005-01-01
1002 阑尾切除术 2005-05-03
1003 胃大部切除术 2005-04-05现在要将 patient_id 重复的记录删除,也就是说,patient_id 为 1001 的2条记录,我只需要其中的任意1条,其他的都可以删除!请高手指教!
1001 阑尾切除术 2005-01-01
1001 胃大部切除术 2005-01-01
1002 阑尾切除术 2005-05-03
1003 胃大部切除术 2005-04-05现在要将 patient_id 重复的记录删除,也就是说,patient_id 为 1001 的2条记录,我只需要其中的任意1条,其他的都可以删除!请高手指教!
先选出唯一的纪录: select * from table group by patientid
然后删除重复纪录: delete table where count(patientid)>1
再插入之前选出的纪录:insert into table(...) values(第一步选出数据)
用 4 句 SQL:
select distinct patient_id,operation_name,operation_date into temptable from sourcetabledelete from sourcetableinsert * into sourcetable from temptabledrop table temptable
select * into temptable from sourcetable group by patient_iddelete from sourcetableinsert into sourcetable select * from temptabledrop table temptable
Min(opeartion_name) as opeartion_name,
Min(opeartion_date) as opeartion_date,
Min(...) as ...
into temptable
from sourcetable group by patient_iddelete from sourcetableinsert into sourcetable select * from temptabledrop table temptable
(SELECT MAX(ID) FROM Operation GROUP BY patient_id)这个做法的前提是Operation表必须有一个唯一的ID字段
where b.patient_id=表.patient_id)
delete opeartion
from opeartion a,
(
select max(id) as id,Name from opeartion
group by opeartion_name having count(*)>1
) as b
where a.id<>b.id
and a.opeartion_name=b.opeartion_name
达到你的目的再后删除ID