我正在嘗試解決 SQL 在線挑戰
我有三張桌子：
- 銷售：customer_id、order_date、product_id。
- 成員：join_date，member_id。
- 選單：product_id、product_name、價格。
其中一個問題是：每個會員在成為會員之前的總物品和消費金額是多少？
我想我在以下查詢中得到了正確的答案：
with cte as
(
SELECT
CASE WHEN s.customer_id = 'A' THEN count(s.product_id)*m.price END AS purchases_A,
CASE WHEN s.customer_id = 'B' THEN count(s.product_id)*m.price END AS purchases_B,
CASE WHEN s.customer_id = 'C' THEN count(s.product_id)*m.price END AS purchases_C,
case when s.customer_id = 'A' THEN count(s.product_id) END AS total_A,
case when s.customer_id = 'B' THEN count(s.product_id) END AS total_B,
case when s.customer_id = 'C' THEN count(s.product_id) END AS total_C
from sales s
join menu m on s.product_id = m.product_id
join members mb on mb.customer_id = s.customer_id and mb.join_date > s.order_date
group by s.customer_id, m.price
)
select
sum(purchases_A) as total_spendings_a,
sum (total_A) as total_items_A,
sum(purchases_B) as total_spendings_b,
sum (total_B) as total_items_B,
sum(purchases_C) as total_spendings_c,
sum (total_C) as total_items_C
from cte;
我的問題是。撰寫此查詢是否有更好的方法或更有效的方法？感覺太長太重復了。在這種情況下，我只有三個客戶：A、B 和 C 如果我有 100 或 1000 個客戶怎么辦？
提前謝謝了
uj5u.com熱心網友回復：
只需加入表格并按成員聚合：
select
m.member_id,
coalesce(count(*), 0) as total_amount,
coalesce(sum(price), 0) as total_price
from members m
left join sales s on s.customer_id = m.customer_id and s.order_date < m.join_date
left join menu p on p.product_id = s.product_id
group by m.member_id
order by by m.member_id;
您同時提到members.member_idand members.customer_id，所以我都使用了它們。如果這是您的請求中的拼寫錯誤，那么只需在我的查詢中將錯誤的列名更改為正確的列名。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/482504.html
標籤：sql PostgreSQL 案子
上一篇：查詢同一張表以創建搜索螢屏的排序
下一篇：在條件聚合中對列值求和