MySQL查询结果列转行格式实现：基于商品列表与日志表的特定日期状态统计需求

要把你的商品状态统计从列格式（每个状态一行）转成行格式（每个状态作为列，计数作为行值），可以通过条件聚合或者动态SQL来实现，下面是具体的方案：

1. 静态行转列方案（状态固定时） #

如果status表中的状态是固定的（比如你示例里的Publish、Action、Let、Sold、Draft），可以直接用硬编码的条件聚合来生成列：

WITH X AS (
    SELECT 
        l.*,
        (SELECT status_from 
         FROM logs 
         WHERE logs.refno = l.refno 
           AND logs.logtime >= '2021-10-01' 
         ORDER BY logs.logtime 
         LIMIT 1) logstat
    FROM listings l 
    WHERE l.added_date < '2021-10-01'
),
Y AS (
    SELECT 
        IFNULL(X.logstat, X.status) stat
    FROM X
)
SELECT
    COUNT(CASE WHEN stat = 'Publish' THEN 1 END) AS Publish,
    COUNT(CASE WHEN stat = 'Action' THEN 1 END) AS Action,
    COUNT(CASE WHEN stat = 'Let' THEN 1 END) AS Let,
    COUNT(CASE WHEN stat = 'Sold' THEN 1 END) AS Sold,
    COUNT(CASE WHEN stat = 'Draft' THEN 1 END) AS Draft
FROM Y;

这个查询会返回一行结果，每列对应一个状态的计数，示例输出结构如下：

2. 动态行转列方案（状态动态变化时） #

如果status表可能新增或删除状态，硬编码列名会很麻烦，这时可以用动态SQL自动生成列：

-- 动态生成列的SQL语句
SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT('COUNT(CASE WHEN stat = ''', text, ''' THEN 1 END) AS ', text)
    ) INTO @sql
FROM status;

-- 拼接完整查询语句
SET @sql = CONCAT('
WITH X AS (
    SELECT 
        l.*,
        (SELECT status_from 
         FROM logs 
         WHERE logs.refno = l.refno 
           AND logs.logtime >= ''2021-10-01'' 
         ORDER BY logs.logtime 
         LIMIT 1) logstat
    FROM listings l 
    WHERE l.added_date < ''2021-10-01''
),
Y AS (
    SELECT 
        IFNULL(X.logstat, X.status) stat
    FROM X
)
SELECT ', @sql, ' FROM Y');

-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

这个方案会自动读取status表中的所有状态，生成对应的统计列，不需要手动修改查询语句。

3. 纯文本行格式输出（匹配你示例的样式） #

如果你需要输出像示例那样的纯文本格式（第一行是状态名，第二行是计数），可以通过GROUP_CONCAT拼接结果：

第一步：获取状态名行 #

SELECT GROUP_CONCAT(DISTINCT text SEPARATOR ' ') AS status_headers
FROM status;

输出示例：Publish Action Let Sold Draft

第二步：获取计数行 #

WITH X AS (
    SELECT 
        l.*,
        (SELECT status_from 
         FROM logs 
         WHERE logs.refno = l.refno 
           AND logs.logtime >= '2021-10-01' 
         ORDER BY logs.logtime 
         LIMIT 1) logstat
    FROM listings l 
    WHERE l.added_date < '2021-10-01'
),
Y AS (
    SELECT 
        IFNULL(X.logstat, X.status) stat
    FROM X
),
agg AS (
    SELECT
        COUNT(CASE WHEN stat = 'Publish' THEN 1 END) AS Publish,
        COUNT(CASE WHEN stat = 'Action' THEN 1 END) AS Action,
        COUNT(CASE WHEN stat = 'Let' THEN 1 END) AS Let,
        COUNT(CASE WHEN stat = 'Sold' THEN 1 END) AS Sold,
        COUNT(CASE WHEN stat = 'Draft' THEN 1 END) AS Draft
    FROM Y
)
SELECT GROUP_CONCAT(Publish, ' ', Action, ' ', Let, ' ', Sold, ' ', Draft) AS status_counts
FROM agg;

输出示例：0 3 0 1 0

你可以把这两个查询的结果拼接起来，得到你想要的纯文本行格式。

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