如何将MySQL/MariaDB查询统计结果的列转为行展示？

你当前的查询返回的是横向统计结果，要转换成纵向的键值对形式完全可行，在MySQL社区版和MariaDB中可以通过UNION ALL实现行转列的效果，具体操作如下：

原查询及结果 #

原SQL语句：

SELECT COUNT(order_id) AS "No of Orders",
       COUNT(po_id) AS "No of Production Orders",
       SUM(po_qty) AS "No of Tools"
  FROM orders
  JOIN production_orders ON po_o_id=order_id
 WHERE order_type="Trial"
   AND (po_status <> "COMPLETED"
   AND po_status <> "GOODS_ISSUED"
   AND po_status <> "PO_TERMINATED"
   AND po_status IS NOT NULL);

执行后得到的横向结果：

修改后的查询方案 #

基础写法（直接拆分合并） #

把原查询的每个统计项拆分成独立的行，通过UNION ALL合并结果：

SELECT "No of Orders" AS Labels, COUNT(order_id) AS Values
FROM orders
JOIN production_orders ON po_o_id=order_id
WHERE order_type="Trial"
  AND (po_status <> "COMPLETED"
  AND po_status <> "GOODS_ISSUED"
  AND po_status <> "PO_TERMINATED"
  AND po_status IS NOT NULL)
UNION ALL
SELECT "No of Production Orders" AS Labels, COUNT(po_id) AS Values
FROM orders
JOIN production_orders ON po_o_id=order_id
WHERE order_type="Trial"
  AND (po_status <> "COMPLETED"
  AND po_status <> "GOODS_ISSUED"
  AND po_status <> "PO_TERMINATED"
  AND po_status IS NOT NULL)
UNION ALL
SELECT "No of Tools" AS Labels, SUM(po_qty) AS Values
FROM orders
JOIN production_orders ON po_o_id=order_id
WHERE order_type="Trial"
  AND (po_status <> "COMPLETED"
  AND po_status <> "GOODS_ISSUED"
  AND po_status <> "PO_TERMINATED"
  AND po_status IS NOT NULL);

优化写法（避免重复查询） #

如果觉得重复写WHERE条件冗余，可以先把统计结果存入临时数据集，再进行转置：

WITH stats AS (
    SELECT COUNT(order_id) AS order_count,
           COUNT(po_id) AS po_count,
           SUM(po_qty) AS tool_count
    FROM orders
    JOIN production_orders ON po_o_id=order_id
    WHERE order_type="Trial"
      AND po_status NOT IN ("COMPLETED", "GOODS_ISSUED", "PO_TERMINATED")
      AND po_status IS NOT NULL
)
SELECT "No of Orders" AS Labels, order_count AS Values FROM stats
UNION ALL
SELECT "No of Production Orders" AS Labels, po_count AS Values FROM stats
UNION ALL
SELECT "No of Tools" AS Labels, tool_count AS Values FROM stats;

注：WITH子句仅支持MySQL 8.0+和MariaDB 10.2+版本，低版本可改用子查询：

SELECT "No of Orders" AS Labels, order_count AS Values
FROM (
    SELECT COUNT(order_id) AS order_count,
           COUNT(po_id) AS po_count,
           SUM(po_qty) AS tool_count
    FROM orders
    JOIN production_orders ON po_o_id=order_id
    WHERE order_type="Trial"
      AND po_status NOT IN ("COMPLETED", "GOODS_ISSUED", "PO_TERMINATED")
      AND po_status IS NOT NULL
) AS stats
UNION ALL
SELECT "No of Production Orders" AS Labels, po_count AS Values FROM stats
UNION ALL
SELECT "No of Tools" AS Labels, tool_count AS Values FROM stats;

预期输出结果 #

执行上述优化后的查询，会得到你需要的纵向键值对：

另外，原WHERE条件中多个po_status <> 可以简化为po_status NOT IN ("COMPLETED", "GOODS_ISSUED", "PO_TERMINATED")，逻辑完全一致且代码更简洁。

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