sql—连续两个季度的查询内容

q8l4jmvw 于 2021-08-09 发布在 Java

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

我有一个名为evaluations的表。它有4列：用户id、季度、年度、内容。
我要选择连续两个季度的所有字段（例如：当前季度是2，所以我要选择2020年第1季度和2019年第4季度的所有字段）
我的问题是：

SELECT e1.user_id, 
e1.quarter AS last_quarter, 
e1.year AS last_year, 
e1.content AS content_last_quarter, 
e2.quarter AS quarter, 
e2.year AS year, 
e2.content AS content_quarter 
FROM evaluations AS e1 JOIN evaluations AS e2 ON e2.user_id = e1.user_id
WHERE (e1.year = 2019) 
    AND (e1.quarter = 4) 
    AND (e2.year = 2020)) 
    AND (e2.quarter = 1) 
    AND e1.deleted_at IS NULL
    AND e2.deleted_at IS NULL

但如果（2020年第1季度）的记录存在，则（2019年第4季度）的记录为空，反之亦然，则没有结果。我想选择其中一个或两者都存在。有人能帮我吗？
对不起，我的英语水平不好。
谢谢您！

sql postgresql

来源：https://stackoverflow.com/questions/61883221/query-content-of-two-consecutive-quarter

1条答案

按热度按时间

sg24os4d1#

使用条件聚合：

SELECT e.user_id, 
       MAX(e.quarter) FILTER (WHERE year = 2019) as last_quarter, 
       MAX(e.quarter) FILTER (WHERE year = 2019) as last_year, 
       MAX(e.content) FILTER (WHERE year = 2019) as last_content, 
       MAX(e.quarter) FILTER (WHERE year = 2020) as quarter, 
       MAX(e.quarter) FILTER (WHERE year = 2020) as year, 
       MAX(e.content) FILTER (WHERE year = 2020) as content
FROM evaluations e
WHERE ((e.year = 2019 AND e.quarter = 4) OR
       (e.year = 2020 AND e.quarter = 1) 
      ) AND
      e.deleted_at IS NULL
GROUP BY e.user_id;

赞(0）回复(0）举报 2021-08-09

我来回答

sql—连续两个季度的查询内容

1条答案

相关问题

热门标签

最新问答