sql 使用distinct 后还有重复数据

[weimei521]

SELECT distinct a.* FROM mz_ht_result a   inner  join yp_mz_base b on a.charge_code=b.charge_code
	  inner join a_employee_mi c on a.op_id=c.code 
	    INNER join mz_charge_detail d on a.patient_id=d.patient_id and a.times=d.times and 
       a.receipt_no=d.receipt_no and a.order_no=d.order_no and a.item_no=d.item_no
	   WHERE a.patient_id='P10798773-0 ' and a.times=3      
	    and d.real_no>0 order by a.order_no,a.real_no 

这是有两条记录，但是如果加上查询b表的数据，就会重复

select DISTINCT a.id, d.pay_mark,drugname,ht_result,ht_time,validate_time,op_id,op_name=c.name,op_time,ht_remark,
                             a.patient_id,a.times,a.receipt_no,a.order_no,a.item_no,a.real_no,d.input_id
                             from mz_ht_result a 
                            inner  join yp_mz_base b on a.charge_code=b.charge_code 
                             inner join a_employee_mi c on a.op_id=c.code
                             INNER join mz_charge_detail d on a.patient_id=d.patient_id and a.times=d.times and 
                             a.receipt_no=d.receipt_no and a.order_no=d.order_no and a.item_no=d.item_no
                             where a.patient_id='P10798773-0 ' and a.times=3
                             and d.real_no>0 order by a.order_no,a.real_no  

这样会有4条数据，请问第二条要怎么改

[OwenZeng_DBA]

引用 楼主 weimei521 的回复:

SELECT distinct a.* FROM mz_ht_result a   inner  join yp_mz_base b on a.charge_code=b.charge_code
	  inner join a_employee_mi c on a.op_id=c.code 
	    INNER join mz_charge_detail d on a.patient_id=d.patient_id and a.times=d.times and 
       a.receipt_no=d.receipt_no and a.order_no=d.order_no and a.item_no=d.item_no
	   WHERE a.patient_id='P10798773-0 ' and a.times=3      
	    and d.real_no>0 order by a.order_no,a.real_no 

这是有两条记录，但是如果加上查询b表的数据，就会重复

select DISTINCT a.id, d.pay_mark,drugname,ht_result,ht_time,validate_time,op_id,op_name=c.name,op_time,ht_remark,
                             a.patient_id,a.times,a.receipt_no,a.order_no,a.item_no,a.real_no,d.input_id
                             from mz_ht_result a 
                            inner  join yp_mz_base b on a.charge_code=b.charge_code 
                             inner join a_employee_mi c on a.op_id=c.code
                             INNER join mz_charge_detail d on a.patient_id=d.patient_id and a.times=d.times and 
                             a.receipt_no=d.receipt_no and a.order_no=d.order_no and a.item_no=d.item_no
                             where a.patient_id='P10798773-0 ' and a.times=3
                             and d.real_no>0 order by a.order_no,a.real_no  

这样会有4条数据，请问第二条要怎么改

数据截个图来看下吧

[acen_chen]

这很正常，说明添加的b表数据里面有不同呀

[学海无涯-回头是岸]

是完全 重复 还是个别字段不同

[二月十六]

看看除了a 表，其他表的字段是不是不重复？不重复的想怎么处理？

[中国风]

用group by +max(或其它聚合函数) a表列放在group by b列用max(或其它聚合函数)

[顺势而为1]

分别查一下, 表 yp_mz_base 的 charge_code, a_employee_mi 的 code, mz_charge_detail 的 patient_id 是否有出现重复现象.