选择使用mysql对表进行广泛重构

I have this table scheme:

我有这个表格方案：

 pcode   city    
 1       MAD     
 3       MAD     
 3       LON     
 1       MAD     
 2       LON     
 3       MAD     
 1       LON     
 2       LON

and I want this result:

我想要这个结果：

 pcode    cityMAD    cityLON
 1        2          1
 2        0          2
 3        2          1

I mean, I need a SELECT to 'reshape' the table, counting the number of observations for every pcode and city.

我的意思是，我需要一个SELECT来“重塑”表格，计算每个pcode和城市的观察数量。

The table is quite big (>100,000 obs). The number of different pcode's is unknown, but the number of different cities is small, so I can include the names of the cities in the SELECT.

桌子很大（> 100,000 obs）。不同pcode的数量是未知的，但不同城市的数量很少，所以我可以在SELECT中包含城市的名称。

2 个解决方案

#1

You can run the following query:

您可以运行以下查询：

SELECT pcode,
       SUM(city='MAD') as cityMAD,
       SUM(city='LON') as cityLON
FROM table
GROUP BY pcode

but then you'll need to create all desired columns manually.

但是你需要手动创建所有想要的列。

Maybe you should group by both pcode and city, and use pivot table.

也许您应该按pcode和city分组，并使用数据透视表。

note that SUM receives 1 when True and 0 when False automatically.

请注意，SUM在True时收到1，在False时自动收到0。

#2

You can build query like below:

您可以构建如下查询：

select pcode,
sum(case when city = 'MAD' then 1 ELSE 0 END) as cityMAD,
sum(case when city = 'LON' then 1 ELSE 0 END) as cityLON
from table_name
group by pcode;

You need to add sum for each city like I have adde for cityMAD and cityLON.

您需要为每个城市添加总和，例如我为cityMAD和cityLON添加的内容。

#1