我有这样的桌子
CREATE TABLE prova
(`ID` int, `CODCLI` longtext, `RIFYEAR` int, `VAL` int)
;
INSERT INTO prova
(`ID`, `CODCLI`, `RIFYEAR`, `VAL`)
VALUES
(1, '1dad000', 2020, 150),
(2, '500', 2020, 100),
(3, '1dad000', 2021, 50),
(4, '1dad000', 2022, 70),
(5, '2000', 2023, 80)
;
http://www.sqlfiddle.com/#!9/7697a4/4,我想选择这些行
2, '500', 2020, 100
3, '1dad000', 2021, 50
4, '1dad000', 2022, 70
我能怎么做?
我写了这样的东西
SELECT *
FROM prova
WHERE CODCLI IN ('500','1dad000')
但是,当“ RIFYEAR”相同时,我只想选择CODCLI = 500的行。
感谢您的帮助 ;)
一种方法使用窗口函数:
SELECT p.*
FROM (SELECT p.*,
ROW_NUMBER() OVER (PARTITION BY year ORDER BY CODCLI DESC) as seqnum
FROM prova p
WHERE CODCLI IN ('500', '1dad000')
) p
WHERE seqnum = 1;
这样可以保证每年返回一行。
或使用NOT EXISTS
:
select p.*
from prova p
where p.codcli = '500' or
(p.codcli = '1dad000' and
not exists (select 1
from prova p2
where p2.year = p.year and p2.codcli = '500'
)
);
如果中有重复项,则每年可以返回重复项prova
。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句