sql怎么查询oracle中，没有重复出现的记录（即只出现一次）和不同字段1共同存在字段2值的记录，求SQL语句

发布网友 发布时间：2022-04-13 18:55

我来回答

共7个回答

热心网友 时间：2022-04-13 20:24

select *, count(distinct value) from 表a group by value
结果:
id value count(distinct name)
3 e 1
2 f 1
最后一项是多余的，不用管就行了，目的达到。。。。。
group by 必须放在 order by 和 limit之前，不然会报错

查询某个字段重复！
select *
from 表a u
where u.value in (select u.value from 表a u
group by u.value having count(*) > 1)追问第一句中的*, count(distinct value) 这个直接sql执行报错；
测试第二句查出的结果不符合要求。

热心网友 时间：2022-04-13 21:42

石亮东的基础改的：
第二个：
SELECT distinct value
FROM (SELECT ID,
VALUE,
COUNT (*) OVER (PARTITION BY VALUE ORDER BY 1) AS value_cnt,
COUNT (distinct id) OVER () AS id_cnt
FROM 表a) a
WHERE value_cnt = id_cnt;追问是对的，我联合语句中再加distinct 就可以了

热心网友 时间：2022-04-13 23:17

1.select * from a where value in(select value from a group by value having count(1)=1)
2.select distinct value
from a
where value not in (select distinct t.value
from (select *
from (select distinct id from a),
(select distinct value from a)
order by id, value) t,
a
where t.id = a.id(+)
and t.value = a.value(+)
and a.id is null)

热心网友 时间：2022-04-14 01:08

结果1
select a.* from A a,(select value from A group by value having count(1)=1) b where a.value=b.value追问对于结果1，这句sql测试通过。那结果2呢？

追答结果2
select value from (select id,value from A group by id,value) group by value having count(1)=(select count(1) from (select distinct id from A))

热心网友 时间：2022-04-14 03:16

select ID, VALUE from T, (select id count(value) c from T GROUP BY ID) T1 WHERE T.ID = T1.ID AND T1.C = 1;追问id count(value) c 测试不行，sql错误

热心网友 时间：2022-04-14 05:41

1)
select * from (
select id, value, count(*) over(partition by value order by 1) as cnt from 表A
) a
where cnt=1;

2) select value from (select id, value, count(*) over(partition by value order by 1) as value_cnt, count(*) over(partition by id order by 1) as id_cnt from rom 表A
) a
where value_cnt=id_cnt;