如何用MS SQL Server的Full Join解决LeetCode重构部门表问题

问题根因 #

你当前的写法出现ID为NULL的异常行，核心原因有两个：

• 最终查询只取了m1.id，如果某部门没有1月的营收数据，哪怕后续其他月份有该部门的数据，最终返回的ID也会显示为NULL
• 每次FULL JOIN的关联条件都绑定在上一个月子查询的ID上，ID字段分散在12个月份子查询中，没有做统一对齐处理

基于FULL JOIN的修复方案 #

这类行转列场景可以用FULL JOIN实现，只需要把返回的ID字段改成用COALESCE函数合并所有月份子查询的ID即可，修复后的代码如下：

/* Write your T-SQL query statement below */
SELECT 
  COALESCE(m1.id, m2.id, m3.id, m4.id, m5.id, m6.id, m7.id, m8.id, m9.id, m10.id, m11.id, m12.id) AS id,
  m1.revenue AS Jan_Revenue,
  m2.revenue AS Feb_Revenue,
  m3.revenue AS Mar_Revenue,
  m4.revenue AS Apr_Revenue,
  m5.revenue AS May_Revenue,
  m6.revenue AS Jun_Revenue,
  m7.revenue AS Jul_Revenue,
  m8.revenue AS Aug_Revenue,
  m9.revenue AS Sep_Revenue,
  m10.revenue AS Oct_Revenue,
  m11.revenue AS Nov_Revenue,
  m12.revenue AS Dec_Revenue
FROM (SELECT * FROM Department WHERE month = 'Jan') m1 
FULL JOIN (SELECT * FROM Department WHERE month = 'Feb') m2 ON m1.id = m2.id
FULL JOIN (SELECT * FROM Department WHERE month = 'Mar') m3 ON COALESCE(m1.id, m2.id) = m3.id
FULL JOIN (SELECT * FROM Department WHERE month = 'Apr') m4 ON COALESCE(m1.id, m2.id, m3.id) = m4.id
FULL JOIN (SELECT * FROM Department WHERE month = 'May') m5 ON COALESCE(m1.id, m2.id, m3.id, m4.id) = m5.id
FULL JOIN (SELECT * FROM Department WHERE month = 'Jun') m6 ON COALESCE(m1.id, m2.id, m3.id, m4.id, m5.id) = m6.id
FULL JOIN (SELECT * FROM Department WHERE month = 'Jul') m7 ON COALESCE(m1.id, m2.id, m3.id, m4.id, m5.id, m6.id) = m7.id
FULL JOIN (SELECT * FROM Department WHERE month = 'Aug') m8 ON COALESCE(m1.id, m2.id, m3.id, m4.id, m5.id, m6.id, m7.id) = m8.id
FULL JOIN (SELECT * FROM Department WHERE month = 'Sep') m9 ON COALESCE(m1.id, m2.id, m3.id, m4.id, m5.id, m6.id, m7.id, m8.id) = m9.id
FULL JOIN (SELECT * FROM Department WHERE month = 'Oct') m10 ON COALESCE(m1.id, m2.id, m3.id, m4.id, m5.id, m6.id, m7.id, m8.id, m9.id) = m10.id
FULL JOIN (SELECT * FROM Department WHERE month = 'Nov') m11 ON COALESCE(m1.id, m2.id, m3.id, m4.id, m5.id, m6.id, m7.id, m8.id, m9.id, m10.id) = m11.id
FULL JOIN (SELECT * FROM Department WHERE month = 'Dec') m12 ON COALESCE(m1.id, m2.id, m3.id, m4.id, m5.id, m6.id, m7.id, m8.id, m9.id, m10.id, m11.id) = m12.id

更推荐的实现方案 #

这类行转列需求更建议使用条件聚合实现，写法更简洁，执行效率也更高，不需要多表关联：

/* Write your T-SQL query statement below */
SELECT 
  id,
  SUM(CASE WHEN month = 'Jan' THEN revenue END) AS Jan_Revenue,
  SUM(CASE WHEN month = 'Feb' THEN revenue END) AS Feb_Revenue,
  SUM(CASE WHEN month = 'Mar' THEN revenue END) AS Mar_Revenue,
  SUM(CASE WHEN month = 'Apr' THEN revenue END) AS Apr_Revenue,
  SUM(CASE WHEN month = 'May' THEN revenue END) AS May_Revenue,
  SUM(CASE WHEN month = 'Jun' THEN revenue END) AS Jun_Revenue,
  SUM(CASE WHEN month = 'Jul' THEN revenue END) AS Jul_Revenue,
  SUM(CASE WHEN month = 'Aug' THEN revenue END) AS Aug_Revenue,
  SUM(CASE WHEN month = 'Sep' THEN revenue END) AS Sep_Revenue,
  SUM(CASE WHEN month = 'Oct' THEN revenue END) AS Oct_Revenue,
  SUM(CASE WHEN month = 'Nov' THEN revenue END) AS Nov_Revenue,
  SUM(CASE WHEN month = 'Dec' THEN revenue END) AS Dec_Revenue
FROM Department
GROUP BY id

内容的提问来源于stack exchange，提问作者KarlTMuahaha T