日期:2014-05-18 浏览次数:20493 次
--这个肯定没重复的 select distinct k.spmch from int_ddhz as z,spkfk as k,int_ddmx as x where x.spid =k.spid and z.danjbh =x.danjbh and dspcode like 'J%' --这是表示 k.spmch + z.lxr + z.lxrdh + z.dzhdh+x.danjbh 记录不重复 select distinct k.spmch,z.lxr,z.lxrdh,z.dzhdh,x.danjbh from int_ddhz as z,spkfk as k,int_ddmx as x where x.spid =k.spid and z.danjbh =x.danjbh and dspcode like 'J%'
------解决方案--------------------
select * from (select ROW_NUMBER() over(partition by k.spmch,z.lxr,z.lxrdh,x.danjbh order by id) as rowindex,* from mytest1) as a , spkfk k, int_ddmx x where rowindex=1 and x.spid =k.spid and z.danjbh =x.danjbh and dspcode like 'J%'
------解决方案--------------------
select distinct k.spmch,z.lxr,z.lxrdh,z.dzhdh,x.danjbh
from int_ddhz as z,spkfk as k,int_ddmx as x
where x.spid =k.spid and z.danjbh =x.danjbh and dspcode like 'J%'
这里用到的distinct去重复是指将 select 后所有列都重复的去掉,并不是去掉单独一列的重复
如果
select distinct b.spmch
from ..
where ..
这样倒是去掉spmch列的重复数据。
去掉重复有很多方法,楼主可以查查看,用标识列,时间字段等 not exists , max min ,row_number 等。
------解决方案--------------------
这个应该是后面字段有重新的数据类型,所以这样的吧。
可以看看表中的数据,然后选择去重的方法。
------解决方案--------------------
你把需求理解错了,你的这个DISTINCT 是在你SELECT 后面的所有列都不重复。
要想只第一列不重复,就得分组查询,后面的显示最大或最小,或平均值
------解决方案--------------------