如何查询SQL案例语句中的许多情况

Question

我正在尝试解决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个客户怎么办？

Answer 1

只需加入表格，然后按成员汇总：

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;

您提到member.member_id and 成员。如果这是您请求中的错别字，则只需将错误的列名更改为我查询中的正确列名即可。