postgresql - 结合 CTE 的结果

Question

我有几个 CTE。 CTE1A 统计区域 1 中 A 类店铺的数量。CTE1B 统计区域 1 中 B 类店铺的数量，以此类推，直到 CTE1D。同样，CTE2B 统计区域 2 中 B 类商店的数量，依此类推。 shop_types CTE 选择所有类型的商店:A、B、C、D。如何显示一个表格，该表格显示每个区域(列)有多少个类型的商店(行)。例如:

    1    2    3    4    5
A   0    7    4    0    0
B   2    3    8    2    9
C   8    5    8    1    6
D   7    1    5    4    3

数据库有 2 个表:

区域表:shop_id, region_id

表格商店:shop_id, shop_type

WITH 
shop_types AS (SELECT DISTINCT shops.shop_type AS type FROM shops WHERE shops.shop_type!='-9999' AND shops.shop_type!='Other'),
cte1A AS (
SELECT regions.region_id, COUNT(regions.shop_id) AS shops_number, shops.shop_type
FROM regions
RIGHT JOIN shops
ON shops.shop_id=regions.shop_id
WHERE regions.region_id=1
AND shops.shop_type='A'
GROUP BY shops.shop_type,regions.region_id)
SELECT * FROM cte1A

Answer 1

我不完全确定我明白你为什么要找，但看起来你正在寻找这样的东西:

select sh.shop_type, 
       count(case when r.region_id = 1 then 1 end) as region_1_count,
       count(case when r.region_id = 2 then 1 end) as region_2_count,
       count(case when r.region_id = 3 then 1 end) as region_3_count
from shops sh
  left join regions r on r.shop_id = sh.shop_id
group by sh.shop_type
order by sh.shop_type;

您需要为要在输出中包含的每个区域添加一个 case 语句。

如果您使用的是 Postgres 9.4，您可以使用 filter 条件替换 case 语句，这使得意图更容易理解(我认为)

count(*) filter (where r.region_id = 1) as region_1_count,
count(*) filter (where r.region_id = 2) as region_2_count, 
...

SQLFiddle:http://sqlfiddle.com/#!1/98391/1

---

在您提问之前:不，您不能根据 select 语句使列数“动态”。查询的列列表必须在语句实际执行之前定义。