php - 如果一个条件不存在则返回值 mysql

Question

即使值不存在，我也想返回值。例如，id = 245 的用户仅具有 2017 年的值，但想法是如果数据库中没有数据，则返回 2016 年的值 0 和 year = 2018 的值 = 0。

SELECT member_id, year,
   SUM(amount_nitrogen) as amount_nitrogen,   
   SUM(amount_potassium) as amount_potassium,
   SUM(amount_phosphorus) as amount_phosphorus,
   SUM(amount_sodium_oxide) as amount_sodium_oxide
   FROM ct 
WHERE year >= 2016 and year <= 2018
GROUP BY  member_id, year

如果用户有所有 3 年的数据:2018、2017、2016 很好，但如果用户只有一年，我只获得今年的数据。范围是获取别人2年的数据，这个数据应该是0

Answer 1

使用CROSS JOIN 生成行并使用LEFT JOIN 引入数据:

SELECT m.member_id, y.year,
       COALESCE(SUM(ct.amount_nitrogen), 0) as amount_nitrogen,   
       COALESCE(SUM(ct.amount_potassium), 0) as amount_potassium,
       COALESCE(SUM(ct.amount_phosphorus), 0) as amount_phosphorus,
       COALESCE(SUM(ct.amount_sodium_oxide), 0) as amount_sodium_oxide
FROM (SELECT DISTINCT member_id FROM ct) m CROSS JOIN
     (SELECT 2016 as year UNION ALL SELECT 2017 UNION ALL SELECT 2018
     ) y LEFT JOIN
     ct
     ON ct.member_id = m.member_id AND ct.year = y.year
GROUP BY m.member_id, y.year