mysql - 循环选择查询

Question

我正在尝试创建包含按年区分的计算属性的查询。从 2007 年开始到今年。我的 sql 的简化示例:

SELECT users.id as id
       users.name as name
       SUM(CASE WHEN posts.approved = 1) AND posts.created_at BETWEEN '2007-01-01 00:00:00 +0100' AND '2007-12-31 23:59:59 +0100' THEN posts.ratings ELSE 0 END) as approved_rating_2007
       SUM(CASE WHEN posts.approved = 0) AND posts.created_at BETWEEN '2007-01-01 00:00:00 +0100' AND '2007-12-31 23:59:59 +0100' THEN posts.ratings ELSE 0 END) as rejected_rating_2007
       SUM(CASE WHEN posts.approved = 1) AND posts.created_at BETWEEN '2008-01-01 00:00:00 +0100' AND '2008-12-31 23:59:59 +0100' THEN posts.ratings ELSE 0 END) as approved_rating_2008
       SUM(CASE WHEN posts.approved = 0) AND posts.created_at BETWEEN '2008-01-01 00:00:00 +0100' AND '2008-12-31 23:59:59 +0100' THEN posts.ratings ELSE 0 END) as rejected_rating_2008
       etc...
FROM users
LEFT OUTER JOIN posts ON posts.user_id = users.id

有没有一种方法可以将它写成某种循环，始终包含从 2007 年到 current_year 的记录？

Answer 1

SELECT u.id,
      u.name,
      year(p.created_at) as create_year
      SUM(CASE WHEN p.approved = 1 then p.ratings else 0 end) as approved_rating,
      SUM(CASE WHEN p.approved = 0 then p.ratings else 0 end) as rejected_rating
FROM users u
LEFT OUTER JOIN posts p ON p.user_id = u.id
group by u.id, u.name, create_year