我表test有两个字段
xh name
字段值大致如下:
1 a
1 b
1 c
2 c
2 d
2 b
3 f
3 b
3 c
4 a
4 b
4 c
主关键字段是xh,因为test表的数据很多
如何用sql语句识别出虽然xh不一样,但name的值完全相同的xh呢?
如上面相同的
1 a
1 b
1 c
4 a
4 b
4 c
。
请指点!为了让大家理解我的意思,说形象点:
xh就象组号,name就象几个人一样譬如:同样几个人报名在第一组,而同样几个人报名又在第5组,不是搞假吗,重复了吗?只不过组号不同罢了,而组成的人相同。
我就想查出这样的情况!
xh name
字段值大致如下:
1 a
1 b
1 c
2 c
2 d
2 b
3 f
3 b
3 c
4 a
4 b
4 c
主关键字段是xh,因为test表的数据很多
如何用sql语句识别出虽然xh不一样,但name的值完全相同的xh呢?
如上面相同的
1 a
1 b
1 c
4 a
4 b
4 c
。
请指点!为了让大家理解我的意思,说形象点:
xh就象组号,name就象几个人一样譬如:同样几个人报名在第一组,而同样几个人报名又在第5组,不是搞假吗,重复了吗?只不过组号不同罢了,而组成的人相同。
我就想查出这样的情况!
from (select xh, max(substr(SYS_CONNECT_BY_PATH(name, ','), 2)) name
from (SELECT xh,
name,
ROW_NUMBER() OVER(PARTITION BY xh ORDER BY name) AS row_num_id
FROM table_name)
start with row_num_id = 1
connect by PRIOR row_num_id + 1 = row_num_id
and PRIOR xh = xh
group by xh)
group by name having count(*)>1
from test L,
(select a.xh xhL, b.xh xhR, a.cnt cnt
from (select xh, count(*) cnt from test group by xh) a,
(select xh, count(*) cnt from test group by xh) b
where a.cnt = b.cnt
and a.xh <> b.xh) m, -- 找出型号对应 count(*) 相同的数据
test R
where L.xh = m.xhL
and R.xh = m.xhR
and L.name = R.name
group by m.xhL, m.xhR, m.cnt
having m.cnt = count(*);
--语句就简单了
select * from test where name in
(select name as cnt from test having count(name) > 1 group by name )
select * from test where name in
(select name from test having count(name) > 1 group by name )
from
(
select a.xh ad,b.xn bd,a.name adata,b.name bdata,count(*)over(partition by a.xh,b.xh) rn
from test a,
test b
where a.name=b.name and a.xh <> b.xh
order by 1,3
)
where rn >= 3
group by ad,adata