mssql2000重复视图

[sufull88]

mssql2000重复视图 ,快速查询重复行

视图：vip_fwjgdj
列
往来单位，物料,单价
viptraderid, materialid , gjdj
1 , 2 , 3
1 , 2 , 4
2 , 3 , 6
3 , 7 , 6
3 , 8 , 9


查询当viptraerid,和materialid 相同的行
结果如下:

往来单位，物料,单价
viptraderid, materialid , gjdj
1 , 2 , 3
1 , 2 , 4

[AcHerat]

select *
from tb t
where exists (select 1 from tb where 往来单位=t.往来单位 and 物料=t.物料 and 单价<>t.单价)

[sufull88]

[Quote=引用 1 楼 的回复:]
SQL code

select *
from tb t
where exists (select 1 from tb where 往来单位=t.往来单位 and 物料=t.物料 and 单价<>t.单价)
[/Quote]

谢谢回复,但是也有可能 单价=t.单价 这种情况出现

[AcHerat]

select 往来单位,物料,单价
from tb t
where exists (select 1 from tb where 往来单位=t.往来单位 and 物料=t.物料 and 单价<>t.单价)
union all
select 往来单位,物料,单价
from tb
group by 往来单位,物料,单价
having count(*) > 1

[sufull88]

[Quote=引用 3 楼 的回复:]
SQL code

select 往来单位,物料,单价
from tb t
where exists (select 1 from tb where 往来单位=t.往来单位 and 物料=t.物料 and 单价<>t.单价)
union all
select 往来单位,物料,单价
from tb
group by 往来单位,物料,单价
having count(*) > 1

……
[/Quote]

好的,谢谢!有没有更高效率的代码处理呢?

[正牌风哥]

select * from tb t where
 exists (select 1 from tb where 往来单位=t.往来单位 and 物料=t.物料 having count(1)>1)

[AcHerat]

select * from tb t where
 exists (select 1 from tb where 往来单位=t.往来单位 and 物料=t.物料 group by 往来单位,物料 having count(1)>1)