如何编写SQL查询展示所有披萨及对应配料且避免披萨重复显示

安全阿岳

2026-10-2

解决思路

通过分组+字符串聚合函数，将同一款披萨对应的多个配料合并为单个字段返回，即可实现单款披萨仅展示一行，同时完整展示所有配料。

不同数据库适配写法

MySQL 写法

SELECT 
  p.name AS pizza_name,
  GROUP_CONCAT(t.name ORDER BY t.name SEPARATOR ', ') AS all_toppings
FROM pizzas p
INNER JOIN pizzasAndToppings pat ON p.idPizza = pat.idPizza
INNER JOIN toppings t ON t.idTopping = pat.idTopping
GROUP BY p.idPizza, p.name;

说明：GROUP_CONCAT默认分隔符为逗号，可通过SEPARATOR自定义分隔符，添加ORDER BY可让配料按固定顺序展示。如果需要保留没有配料的披萨记录，将INNER JOIN改为LEFT JOIN即可。

PostgreSQL / SQL Server 写法

SELECT 
  p.name AS pizza_name,
  STRING_AGG(t.name, ', ' ORDER BY t.name) AS all_toppings
FROM pizzas p
INNER JOIN pizzasAndToppings pat ON p.idPizza = pat.idPizza
INNER JOIN toppings t ON t.idTopping = pat.idTopping
GROUP BY p.idPizza, p.name;

Oracle 写法

SELECT 
  p.name AS pizza_name,
  LISTAGG(t.name, ', ') WITHIN GROUP (ORDER BY t.name) AS all_toppings
FROM pizzas p
INNER JOIN pizzasAndToppings pat ON p.idPizza = pat.idPizza
INNER JOIN toppings t ON t.idTopping = pat.idTopping
GROUP BY p.idPizza, p.name;