我有一个表,我想只输出1个字段,所以它可以是另一个查询WHERE
语句(WHERE SID IN (THIS NEW QUERY)
)的一部分。
然而,正因为如此,我只能在SELECT中包含SID,但这删除了使不同计数工作所需的内容。
SO SELECT * FROM Tablea
给我:
SID deta detb
22222 8159 3763
22222 8159 3763
44444 4739 6135
44444 4739 6135
44444 4739 6134
44444 4739 6135
55555 5937 0223
55555 5936 0223
66666 8577 9497
66666 8577 9497
66666 8577 9497
66666 8576 9496
66666 8577 9497
88888 3595 0919
88888 3595 0919
88888 3595 0919
88888 3595 0914
77777 5678 3456
然后SELECT DISTINCT SID, deta, detb FROM Tablea
给了我:
SID deta detb
22222 8159 3763
44444 4739 6134
44444 4739 6135
55555 5936 0223
55555 5937 0223
66666 8576 9496
66666 8577 9497
88888 3595 0914
88888 3595 0919
77777 5678 3456
我想要的数据是:
SID deta detb
44444 4739 6134
44444 4739 6135
55555 5936 0223
55555 5937 0223
66666 8576 9496
66666 8577 9497
88888 3595 0914
88888 3595 0919
这可以通过使用不同的计数来完成,但是我想要的最终输出是这样的:
SID
44444
55555
66666
88888
但是当我只输出一个字段时,我无法实现它。
使用group by
:
SELECT SID
FROM Tablea
GROUP BY SID
HAVING COUNT(DISTINCT deta || ':' || detab) > 1;
如果您确实想要完整的行(而不是SID
值),那么使用窗口函数:
SELECT a.*
FROM (SELECT a.*, COUNT(DISTINCT deta || ':' || detab) OVER (PARTITION BY SID) as cnt
FROM tablea a
) a
WHERE cnt > 1;
我会这样做:
with sample_data (SID, deta, detb) as (select 22222, 8159, 3763 from dual union all
select 22222, 8159, 3763 from dual union all
select 44444, 4739, 6135 from dual union all
select 44444, 4739, 6135 from dual union all
select 44444, 4739, 6134 from dual union all
select 44444, 4739, 6135 from dual union all
select 55555, 5937, 0223 from dual union all
select 55555, 5936, 0223 from dual union all
select 66666, 8577, 9497 from dual union all
select 66666, 8577, 9497 from dual union all
select 66666, 8577, 9497 from dual union all
select 66666, 8576, 9496 from dual union all
select 66666, 8577, 9497 from dual union all
select 88888, 3595, 0919 from dual union all
select 88888, 3595, 0919 from dual union all
select 88888, 3595, 0919 from dual union all
select 88888, 3595, 0914 from dual union all
select 77777, 5678, 3456 from dual)
--- end of mimicking your sample data
select sid
from (select distinct sid,
deta,
detb
from sample_data)
group by sid
having count(*) > 1;
SID
----------
44444
66666
55555
88888