postgreSQL查询 – 计算两列匹配的列值

发布时间：2020-12-13 16:28:58 所属栏目：百科来源：网络整理

导读：我需要帮助创建一个SQL查询来计算在两个单独的列上分列的行. 这是我的表的DDL： CREATE TABLE Agency ( id SERIAL not null,city VARCHAR(200) not null,PRIMARY KEY(id));CREATE TABLE Customer ( id SERIAL not null,fullname VARCHAR(200) not null,statu

我需要帮助创建一个SQL查询来计算在两个单独的列上分列的行.

这是我的表的DDL：

CREATE TABLE Agency (
  id SERIAL not null,city VARCHAR(200) not null,PRIMARY KEY(id)
);
CREATE TABLE Customer (
  id SERIAL not null,fullname VARCHAR(200) not null,status VARCHAR(15) not null CHECK(status IN ('new','regular','gold')),agencyID INTEGER not null REFERENCES Agency(id),PRIMARY KEY(id)
);

表中的样本数据

AGENCY
id|'city'
1 |'London'
2 |'Moscow'
3 |'Beijing'

CUSTOMER
id|'fullname'      |'status' |agencyid
1 |'Michael Smith' |'new'    |1
2 |'John Doe'      |'regular'|1
3 |'Vlad Atanasov' |'new'    |2
4 |'Vasili Karasev'|'regular'|2
5 |'Elena Miskova' |'gold'   |2
6 |'Kim Yin Lu'    |'new'    |3
7 |'Hu Jintao'     |'regular'|3
8 |'Wen Jiabao'    |'regular'|3

我想按城市计算新客户,普通客户和黄金客户.

我需要单独计算(‘新’,’常规’,’黄金’).这是我想要的输出：

'city'   |new_customers|regular_customers|gold_customers
'Moscow' |1            |1                |1
'Beijing'|1            |2                |0
'London' |1            |1                |0

几周前我一直在挣扎着.
这就是你所需要的.

SELECT 
  Agency.city,count(case when Customer.status = 'new' then 1 else null end) as New_Customers,count(case when Customer.status = 'regular' then 1 else null end) as Regular_Customers,count(case when Customer.status = 'gold' then 1 else null end) as Gold_Customers 
FROM 
  Agency,Customer 
WHERE 
  Agency.id = Customer.agencyID 
GROUP BY
  Agency.city;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!