使用GROUP BY筛选数据：按司机统计各物品数量的技术问询

嘿，我懂你现在的困扰了——单纯按driver_id分组只能算出每个司机的物品总数，没法拆分成每个物品维度的统计对吧？这其实是个很常见的**行转列（透视表）**需求，咱们分两种场景来解决：

场景1：物品类型是固定的（已知所有物品名称） #

这种情况用条件聚合最直接，通过CASE WHEN配合聚合函数（比如COUNT或SUM），针对每个物品单独统计数量。

假设你的原始数据表叫driver_inventory，包含driver_id（司机ID）和item_type（物品类型）两个字段，示例SQL如下（以MySQL为例）：

SELECT
  driver_id,
  -- 统计安全帽数量，不匹配时返回NULL，COUNT会自动忽略NULL
  COUNT(CASE WHEN item_type = '安全帽' THEN 1 END) AS 安全帽数量,
  COUNT(CASE WHEN item_type = '反光背心' THEN 1 END) AS 反光背心数量,
  COUNT(CASE WHEN item_type = '灭火器' THEN 1 END) AS 灭火器数量,
  -- 如果想把NULL替换为0，用COALESCE包裹
  COALESCE(COUNT(CASE WHEN item_type = '急救包' THEN 1 END), 0) AS 急救包数量
FROM driver_inventory
GROUP BY driver_id
ORDER BY driver_id;

执行后就能得到每个司机对应各物品的具体数量，完美匹配你的目标结果表。

场景2：物品类型不固定（可能随时新增） #

如果物品类型会动态变化，静态SQL就不够灵活了，这时候需要用动态SQL自动生成所有物品的统计列。

还是以MySQL为例，代码如下：

-- 第一步：动态拼接所有物品的统计语句
SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'COUNT(CASE WHEN item_type = ''',
      item_type,
      ''' THEN 1 END) AS `',
      item_type, '_数量`'
    )
  ) INTO @sql
FROM driver_inventory;

-- 第二步：拼接完整的查询SQL
SET @sql = CONCAT('SELECT driver_id, ', @sql, ' FROM driver_inventory GROUP BY driver_id ORDER BY driver_id');

-- 第三步：执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

如果用的是其他数据库，也有对应的透视语法：

• SQL Server：用PIVOT函数简化操作

  SELECT driver_id, [安全帽], [反光背心], [灭火器]
  FROM (
    SELECT driver_id, item_type
    FROM driver_inventory
  ) AS SourceTable
  PIVOT (
    COUNT(item_type)
    FOR item_type IN ([安全帽], [反光背心], [灭火器])
  ) AS PivotTable;
  

• PostgreSQL：需要先安装tablefunc扩展，再用crosstab函数

  CREATE EXTENSION IF NOT EXISTS tablefunc;
  
  SELECT * FROM crosstab(
    'SELECT driver_id, item_type, COUNT(*) FROM driver_inventory GROUP BY driver_id, item_type ORDER BY 1,2',
    'SELECT DISTINCT item_type FROM driver_inventory ORDER BY 1'
  ) AS ct(driver_id INT, 安全帽 INT, 反光背心 INT, 灭火器 INT);
  

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