如何在postgres中使用组中的多个筛选器？
如何在postgres中使用组中的多个筛选器？
提问于 2019-06-21 00:44:09
希望创建一个按日期分组但在几个不同筛选器上求和的报表表。
例如，假设我有多个业务地点，并希望按日期对销售进行汇总。
所以说源表
Date | Location | Amount
===========================
Mon | One | 10
Mon | Two | 15
Mon | One | 12
Tue | One | 13
Tue | Three | 17我想把这个变成
Date | Location One Sales | Location Two Sales
================================================
Mon | 22 | 15
Tue | 13 | 0在这上面绞尽脑汁然后撞到墙上。
谢谢!
回答 2
Stack Overflow用户
回答已采纳
发布于 2019-06-21 01:34:38
可以使用CASE进行条件选择。
SQL Fiddle
PostgreSQL 9.6模式设置
CREATE TABLE Sales
("Date" varchar(3), "Location" varchar(5), "Amount" int)
;
INSERT INTO Sales
("Date", "Location", "Amount")
VALUES
('Mon', 'One', 10),
('Mon', 'Two', 15),
('Mon', 'One', 12),
('Tue', 'One', 13),
('Tue', 'Three', 17)
;查询1
SELECT "Date"
, sum(case "Location" when 'One' then "Amount" else 0 end) as "Location One Sales"
, sum(case "Location" when 'Two' then "Amount" else 0 end) as "Location Two Sales"
FROM Sales
GROUP BY "Date"结果
| Date | Location One Sales | Location Two Sales |
|------|--------------------|--------------------|
| Mon | 22 | 15 |
| Tue | 13 | 0 |Stack Overflow用户
发布于 2019-06-21 01:41:27
鉴于这些数据：
CREATE TABLE sales (
id SERIAL PRIMARY KEY,
date TEXT,
location TEXT,
amount INT
);
INSERT INTO sales (date, location, amount) VALUES
('Mon', 'One', 10),
('Mon', 'Two', 15),
('Mon', 'One', 12),
('Tue', 'One', 13),
('Tue', 'Thr', 17)
;您可以手动构建这样的列：
SELECT
date,
SUM(CASE WHEN location='One' THEN amount ELSE 0 END) AS loc_one,
SUM(CASE WHEN location='Two' THEN amount ELSE 0 END) AS loc_two
FROM
sales
GROUP BY date
ORDER BY date;但是，一个更强有力的解决方案可能是在这两个领域中使用组-by。
SELECT
date,
location,
SUM(amount)
FROM
sales
GROUP BY date, location
ORDER BY date, location;SQLFiddle at：http://www.sqlfiddle.com/#!17/7417e/9
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56695521
复制相关文章
相似问题
腾讯云开发者
