查重复值问题
表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
--------------------------
1 11
1 22
1 33
2 33
2 44
3 55

------解决方案--------------------
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