SQLite查询-限制值的出现

piok6c0g 于 2023-06-23 发布在 SQLite

关注(0)|答案(1)|浏览(156)

我有一个查询返回这个结果。如何限制第4列中的值的出现？

19  1   _BOURC01    1   
20  1   _BOURC01    3   2019-11-18
20  1   _BOURC01    3   2017-01-02
21  1   _BOURC01    6   
22  1   _BOURC01    10  
23  1   _BOURC01    13  2016-06-06
24  1   _BOURC01    21  2016-09-19

我的查询：

SELECT "sk"."id" AS "id", "sk"."active" AS "active", "sk"."id_speaker" AS "id_speaker", "sk"."Speech" AS "Speech", "34 Program Weekend"."date" AS "date"
FROM "we"
LEFT JOIN "we" we" ON "we"."Speech" = "34 Program Weekend"."theme_id"
WHERE "id_speaker" = "_BOURC01"
ORDER BY id_speaker, Speech, date DESC

谢谢

sqlite

来源：https://stackoverflow.com/questions/65092612/sqlite-query-limit-occurrence-of-value

1条答案

按热度按时间

0g0grzrc1#

我想这就是你想要的：

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY s.id, s.active, s.id_speaker, s.Speech
                                 ORDER BY p.date DESC) rn
    FROM "_44_SpeakerSpeech" s
    LEFT JOIN "_34_programWeekend" p ON s.Speech = p.theme_id
    WHERE s.id_speaker = '_BOURC01'
)

SELECT id, active, id_speaker, Speech, date
FROM cte
WHERE rn = 1;

此逻辑假定当两个或多个记录都具有相同的列值（不包括日期）时，您希望只保留最新的记录。

赞(0）回复(0）举报 2023-06-23

我来回答

SQLite查询-限制值的出现

1条答案

相关问题

热门标签

最新问答