SQL中基于动态ID获取定制产品选择的行转列查询咨询

以下是兼容所有主流关系型数据库的标准SQL写法，通过关联主餐与配套选择后做条件聚合实现矩阵输出：

SELECT
  main_meal AS '',
  MAX(CASE WHEN side_meal = 'R-COKE' THEN 1 ELSE 0 END) AS 'R-COKE',
  MAX(CASE WHEN side_meal = 'R-FANT' THEN 1 ELSE 0 END) AS 'R-FANT',
  MAX(CASE WHEN side_meal = 'ARWA' THEN 1 ELSE 0 END) AS 'ARWA',
  MAX(CASE WHEN side_meal = 'R-SPRT' THEN 1 ELSE 0 END) AS 'R-SPRT'
FROM (
  -- 关联每个组合餐的主餐名称和用户选择的配套产品
  SELECT
    m.main_meal,
    c.side_meal
  FROM (
    -- 获取每个组合餐ID对应的主餐名称
    SELECT id, description AS main_meal
    FROM item
    WHERE level = 0 AND action = 'RECIPE'
  ) m
  LEFT JOIN (
    -- 获取每个组合餐ID对应的用户选择的配套产品
    SELECT id, description AS side_meal
    FROM item
    WHERE action = 'CHOICE'
  ) c ON m.id = c.id
) t
GROUP BY main_meal
ORDER BY main_meal;

如果配套产品列表是动态不固定的，你可以根据使用的数据库类型（MySQL/PostgreSQL/SQL Server等），用数据库对应的动态SQL语法拼接查询语句即可。

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