sql - PostgreSQL中的交叉表查询

我希望在交叉表中显示此查询的结果：

SELECT district, sex ,count(sex)
FROM table1
GROUP BY sex, district
ORDER BY district;

district | sex | count
---------+-----+-----
dis_1    | M   | 2
dis_1    | F   | 4
dis_1    | NI  | 1
dis_2    | M   | 5
dis_2    | F   | 2

这样地：

district | M | F | NI
---------+---+---+---
dis_1    | 2 | 4 | 1
dis_2    | 5 | 2 | 0

我做了一些测试没有成功，如下所示：

SELECT  row_name AS district,
        category_1::varchar(10) AS m,
        category_2::varchar(10) AS f,
        category_3::varchar(10) AS ni,
        category_4::int AS count

FROM crosstab('select district, sex, count(*)
               from table1 group by district, sex')
     AS ct  (row_name varchar(27),
             category_1 varchar(10),
             category_2 varchar(10),
             category_3 varchar(10),
             category_4 int);

最佳答案

您可以将聚合函数与CASE表达式一起使用，以获得列中的结果：

select district,
  sum(case when sex ='M' then 1 else 0 end) M,
  sum(case when sex ='F' then 1 else 0 end) F,
  sum(case when sex ='NI' then 1 else 0 end) NI
from table1
group by district
order by district

关于sql - PostgreSQL中的交叉表查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/15351673/

District

sql - PostgreSQL中的交叉表查询