SQL行转列实现按用户统计tbl_liabilities表各负债类型的总额与次数

• 分组规则：以User_id和Name作为分组维度，确保每个用户对应一行输出结果
• 行转列实现：用条件聚合完成统计，通过CASE WHEN判断负债类型，对符合对应类型的记录分别做金额求和、条数统计，无对应负债的类型自动返回0

SELECT
  User_id AS id,
  Name AS name,
  SUM(CASE WHEN Type = 'Housing' THEN Amount ELSE 0 END) AS h_sum,
  COUNT(CASE WHEN Type = 'Housing' THEN 1 END) AS h_count,
  SUM(CASE WHEN Type = 'Mortage' THEN Amount ELSE 0 END) AS M_sum,
  COUNT(CASE WHEN Type = 'Mortage' THEN 1 END) AS M_count,
  SUM(CASE WHEN Type = 'Revolving' THEN Amount ELSE 0 END) AS R_sum,
  COUNT(CASE WHEN Type = 'Revolving' THEN 1 END) AS R_count,
  SUM(CASE WHEN Type = 'Other' THEN Amount ELSE 0 END) AS O_sum,
  COUNT(CASE WHEN Type = 'Other' THEN 1 END) AS O_count
FROM tbl_liabilities
GROUP BY User_id, Name
ORDER BY User_id;

以上写法为通用标准SQL，兼容所有主流关系型数据库。如果使用MySQL等支持IF语法的数据库，也可以把CASE WHEN替换为IF简化写法，比如SUM(IF(Type='Housing', Amount, 0))。
注：你提供的示例输出中Toad的h_sum标注为100属于笔误，按照给出的示例数据运行上述代码，得到的Toad的h_sum为200、h_count为1，和实际数据一致。

内容的提问来源于stack exchange，提问作者Mario Vega