SQL如何将多组阶梯价格、数量合并为|分隔的单字段展示

该需求完全可以实现，根据你使用的数据库版本，有以下两种常用实现方案，优先推荐适配性更高、代码更简洁的最优方案：

最优方案（适用于MySQL 4.0+/PostgreSQL/SQL Server 2017+等支持CONCAT_WS函数的数据库） #

CONCAT_WS函数自带跳过NULL值的特性，不会产生多余的分隔符，执行效率高、代码可读性强，完全匹配你的需求。

SELECT 'Manufacturer', 'ManufacturerPartNumber', 'PriceQuantity', 'Price'
UNION ALL
SELECT
  manufact.name AS 'Manufacturer',
  item.item_no AS 'ManufacturerPartNumber',
  -- 拼接数量字段，分隔符用' | '匹配你期望的带空格格式
  CONCAT_WS(' | ',
    CAST((CASE WHEN price.prc_1 IS NOT NULL THEN '1' END) AS VARCHAR),
    CAST((CASE
      WHEN price.qty_1 IS NULL THEN price.qty_1
      WHEN price.qty_1 = 9999999 AND price.prc_2 = 0 THEN NULL
      WHEN price.qty_1 = 0 THEN NULL
      WHEN price.qty_1 = 9999999 THEN price.qty_1
      ELSE price.qty_1 + 1
    END) AS VARCHAR),
    CAST((CASE
      WHEN price.qty_2 IS NULL THEN price.qty_2
      WHEN price.qty_2 = 9999999 AND price.prc_3 = 0 THEN NULL
      WHEN price.qty_2 = 0 THEN NULL
      WHEN price.qty_2 = 9999999 THEN price.qty_2
      ELSE price.qty_2 + 1
    END) AS VARCHAR),
    CAST((CASE
      WHEN price.qty_3 IS NULL THEN price.qty_3
      WHEN price.qty_3 = 9999999 AND price.prc_4 = 0 THEN NULL
      WHEN price.qty_3 = 0 THEN NULL
      WHEN price.qty_3 = 9999999 THEN price.qty_3
      ELSE price.qty_3 + 1
    END) AS VARCHAR),
    CAST((CASE
      WHEN price.qty_4 IS NULL THEN price.qty_4
      WHEN price.qty_4 = 9999999 AND price.prc_5 = 0 THEN NULL
      WHEN price.qty_4 = 0 THEN NULL
      WHEN price.qty_4 = 9999999 THEN price.qty_4
      ELSE price.qty_4 + 1
    END) AS VARCHAR)
  ) AS PriceQuantity,
  -- 拼接价格字段
  CONCAT_WS('|',
    CAST((CASE
      WHEN price.prc_1 IS NULL THEN NULL
      WHEN price.prc_1 = 0 THEN NULL
      ELSE price.prc_1
    END) AS VARCHAR),
    CAST((CASE
      WHEN price.prc_2 IS NULL THEN NULL
      WHEN price.prc_2 = 0 THEN NULL
      ELSE price.prc_2
    END) AS VARCHAR),
    CAST((CASE
      WHEN price.prc_3 IS NULL THEN NULL
      WHEN price.prc_3 = 0 THEN NULL
      ELSE price.prc_3
    END) AS VARCHAR),
    CAST((CASE
      WHEN price.prc_4 IS NULL THEN NULL
      WHEN price.prc_4 = 0 THEN NULL
      ELSE price.prc_4
    END) AS VARCHAR),
    CAST((CASE
      WHEN price.prc_5 IS NULL THEN NULL
      WHEN price.prc_5 = 0 THEN NULL
      ELSE price.prc_5
    END) AS VARCHAR)
  ) AS Price
FROM item
-- 原SQL缺失表关联条件，自行补充item、manufact、price三张表的关联逻辑即可
JOIN manufact ON item.manufact_id = manufact.id
JOIN price ON item.price_id = price.id

通用兼容方案（适用于所有主流数据库，含不支持CONCAT_WS的低版本数据库） #

如果使用SQL Server 2016及更早版本，可通过STUFF+ISNULL组合实现，逻辑是手动拼接后去除开头多余的分隔符：

-- 以价格字段拼接为例，数量字段逻辑完全一致
STUFF(
  ISNULL('|' + CAST(价格1判断逻辑 AS VARCHAR), '') +
  ISNULL('|' + CAST(价格2判断逻辑 AS VARCHAR), '') +
  ISNULL('|' + CAST(价格3判断逻辑 AS VARCHAR), '') +
  ISNULL('|' + CAST(价格4判断逻辑 AS VARCHAR), '') +
  ISNULL('|' + CAST(价格5判断逻辑 AS VARCHAR), ''),
1,1,'') AS Price

注意事项 #

• 如果不需要数量字段的|前后带空格，把CONCAT_WS的第一个参数修改为'|'即可
• 注意调整CAST的字符串长度，避免长数字/价格被截断

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