選擇
SELECT pp.id, TO_CHAR(pp.created_dt::date, 'dd.mm.yyyy') AS "Date", CAST(pp.created_dt AS time(0)) AS "Time",
au.username AS "User", ss.name AS "Service", pp.amount, REPLACE(pp.status, 'SUCCESS', ' ') AS "Status",
pp.account AS "Props", pp.external_id AS "External", COALESCE(pp.external_status, null, 'indefined') AS "External status"
FROM payment AS pp
INNER JOIN auth_user AS au ON au.id = pp.creator_id
INNER JOIN services_service AS ss ON ss.id = pp.service_id
WHERE pp.created_dt::date = (CURRENT_DATE - INTERVAL '1' day)::date
AND ss.name = 'Some Name' AND pp.status = 'SUCCESS'
id | Date | Time | Service |amount | Status |
------ ----------- ----------- ------------ ------- -------- ---
9 | 2021.11.1 | 12:20:01 | some serv | 100 | stat |
10 | 2021.12.1 | 12:20:01 | some serv | 89 | stat |
------ ----------- ----------- ------------ ------- -------- -----
Total | | | | 189 | |
我有一個這樣的選擇。我需要得到類似上面顯示的東西。也就是說，我需要得到一列的總數。我已經嘗試了很多東西，但對我來說沒有任何效果。
uj5u.com熱心網友回復：
如果我理解正確，您需要一個結果，其中在原始查詢結果之后附加具有聚合值的額外行。您可以通過多種方式實作它：
1.（推薦）最簡單的方法可能是將您的原始查詢與輔助查詢合并：
with t(id,other_column1,other_column2,amount) as (values
(9,'some serv','stat',100),
(10,'some serv','stat',89)
)
select t.id::text, t.other_column1, t.other_column2, t.amount from t
union all
select 'Total', null, null, sum(amount) from t
2.你也可以使用group by rollup其目的正是 this 的子句。由于您的查詢包含許多與聚合無關的列，因此您的情況變得更加困難。因此，最好先計算聚合，然后再加入不重要的資料：
with t(id,other_column1,other_column2,amount) as (values
(9,'some serv','stat',100),
(10,'some serv','stat',89)
)
select case when t.id is null then 'Total' else t.id::text end as id
, t.other_column1
, t.other_column2
, case when t.id is null then ext.sum else t.amount end as amount
from (
select t.id, sum(amount) as sum
from t
group by rollup(t.id)
) ext
left join t on ext.id = t.id
order by ext.id
3.為完整起見，我只是向您展示應該如何避免加入。在這種情況下，group by子句必須使用除amount（以保留原始行）和聚合（以獲取總和行）之外的所有列，因此grouping sets具有 2 個集合的子句很方便。（該rollup子句grouping sets畢竟是特例。）明顯的缺點是case grouping...對不涉及聚合的每一列重復運算式。
with t(id,other_column1,other_column2,amount) as (values
(9,'some serv','stat',100),
(10,'some serv2','stat',89)
)
select case grouping(t.id) when 0 then t.id::text else 'Total' end as id
, case grouping(t.id) when 0 then t.other_column1 end as other_column1
, case grouping(t.id) when 0 then t.other_column2 end as other_column2
, sum(t.amount) as amount
from t
group by grouping sets((t.id, t.other_column1, t.other_column2), ())
order by t.id
請參閱示例（db fiddle）：
（坦率地說，除了簡單的報告之外，我幾乎無法想象任何其他目的，其中列將number型別id 與型別標簽 Total of texttype混合在一起。）
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/389910.html
標籤：sql PostgreSQL的