如何改写SQL语句将registrations表城市统计结果转为预期布局？

架构老林

2026-5-12

解决方案：将横向统计转为纵向布局

你当前的SQL语句通过条件计数把每个地点的统计结果输出为单独的列，得到的是一行多列的横向布局。要改成一行对应一个地点的纵向布局，我们可以用两种常见的方式实现：

方法一：使用`UNION ALL`拼接独立统计查询

这种方式写法直接，适合地点数量较少的场景：

SELECT 'LOCATION 1' AS location, COUNT(*) AS total
FROM registrations
WHERE city LIKE '%location 1%'
UNION ALL
SELECT 'LOCATION 2' AS location, COUNT(*) AS total
FROM registrations
WHERE city LIKE '%location 2%'
UNION ALL
SELECT 'LOCATION 3' AS location, COUNT(*) AS total
FROM registrations
WHERE city LIKE '%location 3%';

说明：

每个SELECT语句单独统计一个地点的注册总数，并用AS location指定地点名称列，AS total指定总数列。
UNION ALL会把三个独立的结果集合并成一个纵向表格，最终输出三行数据，每行对应一个地点的统计结果。
即使某个地点没有匹配的注册记录，COUNT(*)也会返回0，确保所有地点都会出现在结果中。

方法二：使用CTE+关联查询（更易维护）

如果后续需要添加更多地点，这种方式只需要修改CTE部分，无需重复编写统计逻辑：

WITH locations AS (
    SELECT 'LOCATION 1' AS name, '%location 1%' AS pattern
    UNION ALL
    SELECT 'LOCATION 2' AS name, '%location 2%' AS pattern
    UNION ALL
    SELECT 'LOCATION 3' AS name, '%location 3%' AS pattern
)
SELECT l.name AS location, COUNT(r.city) AS total
FROM locations l
LEFT JOIN registrations r ON r.city LIKE l.pattern
GROUP BY l.name;