抱歉,如果标题不够描述,我不是本地人。 所以我有下表数字
A..B..C..YEAR
1..1..1..2019
2..1..0..2019
2..2..1..2019
2..3..1..2019
2..4..1..2020
当 C 列为 1 时,每个 A 列值在一年中 B 列中应只有一个值。如果有多个,这是我需要找出的错误。
因此,我应该进行一个查询,在此示例中提供第 3 行和第 4 行。我尝试了如下,但这也给了我第 5 行,因为"计数"似乎可以查看所有年份,因为我只想在给定的年份查找。如何包括不同 B 的年份?
SELECT A, B
FROM NUMBERS
WHERE A IN (
SELECT A
FROM NUMBERS
WHERE C = 1
AND YEAR = 2019
GROUP BY A
HAVING COUNT (DISTINCT B) > 1
)
;
如果我理解正确,那么您需要有多个"b"值的行:
select n.*
from (select n.*,
min(b) over (partition by a, year) as min_b,
max(b) over (partition by a, year) as max_b
from numbers n
where n.c = 1
) n
where min_b <> max_b
也许您可以将年份添加到组中。
SELECT A, B
FROM NUMBERS
WHERE A IN (
SELECT A
FROM NUMBERS
WHERE C = 1
AND YEAR = 2019
GROUP BY YEAR, A
HAVING COUNT (DISTINCT B) > 1
)
;