查重复值问题
表COM_PriceSpecEntry,字段 fitemid,frelatedid,fid 求fitemid重复但frelatedid不重复的值
[解决办法]
select * from COM_PriceSpecEntry as tmp
where exists (select 1 from COM_PriceSpecEntry where fitemid=tmp.fitemid and frelatedid <> tmp.frelatedid
having count(1)> 1)
[解决办法]
select a.* from COM_PriceSpecEntry a,
(
select fitemid,frelatedid,max(fid) as fid from --也可以用FID并且确保fitemid,frelatedid相同情况下,fid不能相同
(
select * from COM_PriceSpecEntry where fitemid in (select fitemid from COM_PriceSpecEntry group by fitemid having count(*) > 1)
) t
group by fitemid,frelatedid
) b
where a.fitemid = b.fitemid and a.frelatedid = b.frelatedid and a.fid = b.fid
[解决办法]
select fitemid,count(distinct frelatedid)
from COM_PriceSpecEntry
group by fitemid
[解决办法]
select distinct fitemid,frelatedid from COM_PriceSpecEntry
[解决办法]
create table COM_PriceSpecEntry(fitemid int,frelatedid int,fid int)
insert into COM_PriceSpecEntry
select 1,11,NULL
union all select 1,22,NULL
union all select 1,33,NULL
union all select 1,33,NULL
union all select 2,33,NULL
union all select 2,44,NULL
union all select 3,55,NULL
select distinct fitemid,frelatedid from COM_PriceSpecEntry
fitemid frelatedid
--------------------------
111
122
133
233
244
355
[解决办法]
select a.* from COM_PriceSpecEntry a,
(
select fitemid,frelatedid,max(fid) as fid from --也可以用min并且确保fitemid,frelatedid相同情况下,fid不能相同
(
select * from COM_PriceSpecEntry where fitemid in (select fitemid from COM_PriceSpecEntry group by fitemid having count(*) > 1)
) t
group by fitemid,frelatedid
) b
where a.fitemid = b.fitemid and a.frelatedid = b.frelatedid and a.fid = b.fid
[解决办法]
fitemid frelatedid
651 75
651 75
652 76
653 76
657 77
655 75
655 75
657 77
结果是
651 75
651 75
655 75
655 75
657 77
657 77
你这里的结果和最开始的说法完全不一样.最开始是fitemid重复frelatedid不重复.
这里是两个都重复.
如果是上诉结果.
select fitemid , frelatedid
from COM_PriceSpecEntry
group by fitemid , frelatedid
having count(*) > 1
[解决办法]
select * from COM_PriceSpecEntry a
inner join
(Select fitemid, frelatedid
From COM_PriceSpecEntry
Group By fitemid, frelatedid Having Count(*) > 1) b
on a.fitemid=b.fitemid and a.frelatedid=b.frelatedid
