MySQL动态生成客户端MAC地址列表头：HD/UHD流量与流数统计查询实现问题

看起来你卡在了动态列透视这一步——毕竟平时咱们写的大多是固定列的行转列查询，而这里客户端的MAC地址是可变的，没法提前写死列名。别担心，咱们用MySQL的动态SQL就能搞定这个需求，我来一步步拆解给你看：

第一步：先搞定基础的分组统计（静态聚合） #

首先，咱们先不考虑动态列，先把每个客户端、每个质量分组的总流量、流数统计出来，这是后续所有操作的基础。

需要关联clients和streams表，按MAC地址和质量分组（HD/UHD）聚合：

SELECT
  c.mac_address,
  CASE
    WHEN s.quality IN ('2160p', '4320p') THEN 'UHD'
    ELSE 'HD'
  END AS quality_group,
  SUM(s.traffic) AS total_bytes,
  COUNT(*) AS stream_count
FROM clients c
JOIN streams s ON c.id = s.client_id
GROUP BY c.mac_address, quality_group
ORDER BY c.mac_address, quality_group;

这一步会得到每个客户端在HD/UHD分组下的原始字节数和流的数量，接下来要处理流量的格式转换。

第二步：流量单位转换与格式化 #

需求里要求把字节数转换成MiB或GiB，保留两位小数，还要和流数拼接成X.XX GiB (N)的格式。咱们可以用嵌套CASE语句来处理单位，再用CONCAT拼接格式：

SELECT
  quality_group,
  mac_address,
  CONCAT(
    ROUND(
      CASE
        WHEN total_bytes >= 1024*1024*1024 THEN total_bytes/(1024*1024*1024)
        ELSE total_bytes/(1024*1024)
      END,
      2
    ),
    ' ',
    CASE
      WHEN total_bytes >= 1024*1024*1024 THEN 'GiB'
      ELSE 'MiB'
    END,
    ' (', stream_count, ')'
  ) AS formatted_traffic
FROM (
  -- 嵌套第一步的聚合查询
  SELECT
    c.mac_address,
    CASE WHEN s.quality IN ('2160p', '4320p') THEN 'UHD' ELSE 'HD' END AS quality_group,
    SUM(s.traffic) AS total_bytes,
    COUNT(*) AS stream_count
  FROM clients c
  JOIN streams s ON c.id = s.client_id
  GROUP BY c.mac_address, quality_group
) AS base_stats;

现在你会得到每个分组、每个客户端的格式化后的数据，但还是行式的，需要转成以MAC为列头的透视表。

第三步：核心！动态生成MAC列头的透视表 #

MySQL没有内置的透视函数，所以得用动态SQL + PREPARE语句来实现。思路是：

1. 先查询所有不重复的MAC地址，按升序排序，把它们拼接成透视列的SQL片段；
2. 把这些片段拼接到主查询里，生成完整的透视SQL；
3. 执行这个动态生成的SQL。

完整的动态SQL代码如下：

-- 调整GROUP_CONCAT的最大长度（避免MAC过多时截断，可选）
SET SESSION group_concat_max_len = 1000000;

-- 第一步：动态生成所有MAC对应的透视列片段
SET @cols = NULL;
SELECT
  GROUP_CONCAT(
    DISTINCT CONCAT(
      'MAX(IF(mac_address = ''', mac_address, ''', formatted_traffic, NULL)) AS `', mac_address, '`'
    )
    ORDER BY mac_address
  ) INTO @cols
FROM clients;

-- 第二步：拼接完整的透视查询SQL
SET @sql = CONCAT(
  'SELECT
    quality_group AS quality,
    ', @cols, '
  FROM (
    -- 嵌套第二步的格式化查询
    SELECT
      quality_group,
      mac_address,
      CONCAT(
        ROUND(
          CASE
            WHEN total_bytes >= 1024*1024*1024 THEN total_bytes/(1024*1024*1024)
            ELSE total_bytes/(1024*1024)
          END,
          2
        ),
        '' '',
        CASE
          WHEN total_bytes >= 1024*1024*1024 THEN ''GiB''
          ELSE ''MiB''
        END,
        '' ('', stream_count, '')''
      ) AS formatted_traffic
    FROM (
      -- 嵌套第一步的基础聚合
      SELECT
        c.mac_address,
        CASE WHEN s.quality IN (''2160p'', ''4320p'') THEN ''UHD'' ELSE ''HD'' END AS quality_group,
        SUM(s.traffic) AS total_bytes,
        COUNT(*) AS stream_count
      FROM clients c
      JOIN streams s ON c.id = s.client_id
      GROUP BY c.mac_address, quality_group
    ) AS base_stats
  ) AS formatted_stats
  GROUP BY quality_group
  ORDER BY quality_group;'
);

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

关键细节解释 #

1. GROUP_CONCAT生成动态列：GROUP_CONCAT会把所有MAC地址对应的MAX(IF(...))片段拼接到一起，每个片段的作用是把当前MAC的格式化流量值作为该列的内容；如果某个客户端在某分组下没有流，会显示NULL，你可以改成IFNULL(MAX(...), '0.00 MiB (0)')来显示默认值。
2. 单位转换逻辑：严格按照1 MiB=1024²字节、1 GiB=1024³字节的定义判断，大于等于1GiB的用GiB单位，否则用MiB。
3. 排序：在GROUP_CONCAT里加了ORDER BY mac_address，确保列按MAC升序排列，完全符合需求。

内容来源于stack exchange