现在我想在同一个ID下确认另外一个字段的值是否有重复的。废话不多说,直接举例、
ord2_ordid ord2_title
8697 面纸
8697 内标纸
8697 面纸
8698 面纸
8698 内标纸
像这样的数据。 我需要查出在同一个ID字段ord2_ordid 下 是否存在 ord2_title 有相同的 如果有,就将数据查出来
上面的数据就查出 ord2_ordid等于8697的数据假设表名为a
ord2_ordid ord2_title
8697 面纸
8697 内标纸
8697 面纸
8698 面纸
8698 内标纸
像这样的数据。 我需要查出在同一个ID字段ord2_ordid 下 是否存在 ord2_title 有相同的 如果有,就将数据查出来
上面的数据就查出 ord2_ordid等于8697的数据假设表名为a
where ord2_ordid in
(select ord2_ordid from tb group by ord2_ordid having count(ord2_ordid)>1)
where ord2_ordid in
(select ord2_ordid from a group by ord2_ordid having count(ord2_ordid)>1)
from ta a
where exists(select 1 from ta where ord2_ordid = a.ord2_ordid and ord2_title <> a.ord2_title )
select *
from ta a
where exists(select 1 from ta where ord2_ordid != a.ord2_ordid and ord2_title = a.ord2_title )
select a.*
from a join
(select ord2_ordid,ord2_title
from a
group by ord2_ordid,odr2_title
having count(1)>1) b
on a.ord2_did = b.ord2_did
where ord2_ordid in
(select ord2_ordid from tb group by ord2_ordid having count(ord2_ordid)>1)
或select * from tb
where exists
(select * from tb having count(ord2_ordid)>1)
create table a(ord2_ordid int,ord2_title varchar(20))
insert into a
select 8697,'面纸' union all
select 8697,'内标纸' union all
select 8697,'面纸' union all
select 8698,'面纸' union all
select 8698,'内标纸'
goselect a.*
from a join
(select ord2_ordid,ord2_title
from a
group by ord2_ordid,ord2_title
having count(1)>1) b
on a.ord2_ordid = b.ord2_ordiddrop table a/************ord2_ordid ord2_title
----------- --------------------
8697 面纸
8697 内标纸
8697 面纸(3 行受影响)
3946 1055 3944 1212 1 0 2 内文B 787*1092 0.00 0.0000 0.00 2009-12-07 13:01:46.700 0 2009-12-07 13:01:46.700 0.0000 0.0000 0.0000 3.0000 0.0000 0.00 0.00 0 0.00 0 0 0 0.00 0.00 0.00 0.00 0.00 0.00 0.0000 0.0000 0.00 0.00 0.0000 0.0000 0.00 0.00 0 0.0000 0 0 0 0看这2条吧,都为 1055下 内文A 和 内文B。 2个ord_title 是不同的,但是还是查出来了。如果还有一条编号也为1055的,且ord_title是内文A或者内文B的其中一个,查出来是没问题。但是只有2条,而且他们的ord_title不同 所以不应该查出来吧。
select * from tb
where ord2_ordid in
(select ord2_ordid from tb group by ord2_ordid,ord2_title having count(*)>1)
from a join
(select ord2_ordid,ord2_title
from a
group by ord2_ordid,ord2_title
having count(1)>1) b
on a.ord2_ordid = b.ord2_ordid and a.ord2_title = b.ord2_title
/*
ord2_ordid ord2_title
----------- --------------------
8697 面纸
8697 面纸*/??
create table a(ord2_ordid int,ord2_title varchar(20))
insert into a
select 8697,'面纸' union all
select 8697,'内标纸' union all
select 8697,'面纸' union all
select 8698,'面纸' union all
select 8698,'内标纸'
goselect *
from a
where ord2_ordid in (select ord2_ordid from a group by ord2_ordid,ord2_title having count(1)>1)drop table a/************ord2_ordid ord2_title
----------- --------------------
8697 面纸
8697 内标纸
8697 面纸(3 行受影响)
*
from
tb t
where
exists(select 1 from ta where ord2_ordid <> t.ord2_ordid and ord2_title = t.ord2_title )