我有一張這樣的表，我們稱之為行動：
id | amt | time_stamp | act_id |
--- ----- ------------ --------
int| int | timestamp | int |
和另一個表（我們稱之為 action_type）：
id | name
--- -----
int| str
其中（這很重要）是兩個條目：給予和接受
現在我需要做的是創建選擇陳述句，最后我將得到如下結果：
sum_give | sum_take | time_stamp |
--------- ---------- ------------
總和是
select
sum(amt) as sum_give,
cast (date_trunc('hour', time_stamp) as timestamp) as hr
from action
where act_id=1
group_by date_trunc('hour' ...);
另一個是
select
sum(amt) as sum_take,
cast (date_trunc('hour', time_stamp) as timestamp) as hr
from action
where act_id=2
...
你明白了......
我不知道如何創建我需要的結果
uj5u.com熱心網友回復：
在這里使用條件聚合：
SELECT
SUM(amt) FILTER (WHERE act_id = 1) AS sum_give,
SUM(amt) FILTER (WHERE act_id = 2) AS sum_take,
CAST(DATE_TRUNC('hour', time_stamp) AS timestamp) AS hr
FROM action
GROUP BY 3;
uj5u.com熱心網友回復：
看來您只需要一個 CASE 陳述句-
SELECT sum(CASE WHEN act_id=1 THEN amt END) as sum_give,
sum(CASE WHEN act_id=2 THEN amt END) as sum_take,
cast (date_trunc('hour', time_stamp) as timestamp) as hr
FROM action
WHERE act_id IN (1, 2)
GROUP BY date_trunc('hour' ...);
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/498444.html
標籤：sql PostgreSQL 选择