SQL Server 2019中如何在查询结果顶部添加各STATUS统计行

需求说明 #

我们使用SQL Server 2019，针对TRAILER表需要实现：

• 筛选出Zone为V2R 5M3的trailer_id、zone、status明细数据
• 在查询结果顶部添加STATUS（AVAIL、DISP、SPTLD）的计数，支持两种展示形式：
  1. 一行展示所有状态计数（格式示例：AVAIL = 5 | DISP = 1 | SPTLD = 1）
  2. 每行展示一个状态的计数

示例数据 #

trailer_id  Zone        Status
2X0705       V2R 5M3    AVAIL
2X0706       V2R 5M3    AVAIL
2X0808       V2R 5M3    AVAIL
2X0817       V2R 5M3    AVAIL
3X1226       V2R 5M3    AVAIL
2X0777       V2R 5M3    DISP
2X0814       V2R 5M3    SPTLD

现有查询 #

select trailer_id, zone, status 
from trailer
where zone = 'V2R 5M3'
group by zone, status, trailer_id
order by zone, status, trailer_id

方案1：一行展示所有状态计数 #

通过UNION ALL将统计行与明细行合并，确保统计行位于结果顶部：

-- 生成状态统计首行
SELECT 
    'AVAIL = ' + CAST(COUNT(CASE WHEN Status = 'AVAIL' THEN 1 END) AS VARCHAR) 
    + ' | DISP = ' + CAST(COUNT(CASE WHEN Status = 'DISP' THEN 1 END) AS VARCHAR) 
    + ' | SPTLD = ' + CAST(COUNT(CASE WHEN Status = 'SPTLD' THEN 1 END) AS VARCHAR) AS trailer_id,
    '' AS zone,
    '状态统计' AS status
FROM TRAILER
WHERE Zone = 'V2R 5M3'

UNION ALL

-- 原明细查询
SELECT trailer_id, zone, status 
FROM TRAILER
WHERE Zone = 'V2R 5M3'
ORDER BY 
    -- 控制统计行排在最前
    CASE WHEN status = '状态统计' THEN 0 ELSE 1 END,
    zone, status, trailer_id

效果说明 #

结果首行展示所有状态的计数汇总，后续行是符合条件的明细数据。

方案2：每行展示一个状态的计数 #

针对每个状态单独生成统计行，再与明细行合并：

-- AVAIL状态计数行
SELECT 
    CAST(COUNT(*) AS VARCHAR) + ' 个' AS trailer_id,
    'V2R 5M3' AS zone,
    'AVAIL 状态计数' AS status
FROM TRAILER
WHERE Zone = 'V2R 5M3' AND Status = 'AVAIL'

UNION ALL

-- DISP状态计数行
SELECT 
    CAST(COUNT(*) AS VARCHAR) + ' 个' AS trailer_id,
    'V2R 5M3' AS zone,
    'DISP 状态计数' AS status
FROM TRAILER
WHERE Zone = 'V2R 5M3' AND Status = 'DISP'

UNION ALL

-- SPTLD状态计数行
SELECT 
    CAST(COUNT(*) AS VARCHAR) + ' 个' AS trailer_id,
    'V2R 5M3' AS zone,
    'SPTLD 状态计数' AS status
FROM TRAILER
WHERE Zone = 'V2R 5M3' AND Status = 'SPTLD'

UNION ALL

-- 原明细查询
SELECT trailer_id, zone, status 
FROM TRAILER
WHERE Zone = 'V2R 5M3'
ORDER BY 
    -- 控制计数行排在明细前
    CASE WHEN status LIKE '%状态计数' THEN 0 ELSE 1 END,
    status, trailer_id

效果说明 #

结果顶部依次展示每个状态的计数行，后续行是明细数据。

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