mysql SQL：SELECT AS具有相同FROM和不同WHERE的多个值

x759pob2 于 2022-10-31 发布在 Mysql

关注(0)|答案(1)|浏览(198)

所以我有这个代码：

SELECT a.total_sales AS July, b.total_sales AS August, c.total_sales AS September
FROM
(SELECT EXTRACT(month FROM delivered_at) AS month, ROUND(SUM (sale_price),2) AS total_sales
FROM `bigquery-public-data.thelook_ecommerce.order_items` 
WHERE status = 'Complete' AND delivered_at BETWEEN "2022-01-01" AND "2022-10-01"
GROUP BY month
ORDER BY month) a,
(SELECT EXTRACT(month FROM delivered_at) AS month, ROUND(SUM (sale_price),2) AS total_sales
FROM `bigquery-public-data.thelook_ecommerce.order_items` 
WHERE status = 'Complete' AND delivered_at BETWEEN "2022-01-01" AND "2022-10-01"
GROUP BY month
ORDER BY month) b,
(SELECT EXTRACT(month FROM delivered_at) AS month, ROUND(SUM (sale_price),2) AS total_sales
FROM `bigquery-public-data.thelook_ecommerce.order_items` 
WHERE status = 'Complete' AND delivered_at BETWEEN "2022-01-01" AND "2022-10-01"
GROUP BY month
ORDER BY month) c
WHERE a.month = 7 AND b.month = 8 AND c.month = 9

我得到了我想要的结果，这就是：

Row July      August    September
1   148622.29 169310.62 209339.57

有没有更简单的方法？

mysql

来源：https://stackoverflow.com/questions/74169216/sql-select-as-multiple-value-with-the-same-from-with-different-where

1条答案

按热度按时间

7xzttuei1#

我们可以将3个子查询缩减为1个子查询

SELECT 
  SUM(IF(t.month=7,t.total_sales,0)) AS July,
  SUM(IF(t.month=8,t.total_sales,0)) AS August,
  SUM(IF(t.month=9,t.total_sales,0)) AS September
  FROM
  (
    SELECT EXTRACT(month FROM delivered_at) AS month, ROUND(SUM (sale_price),2) AS total_sales
    FROM `bigquery-public-data.thelook_ecommerce.order_items` 
    WHERE status = 'Complete' AND delivered_at BETWEEN "2022-01-01" AND "2022-10-01"
    AND month in(7,8,9)
    GROUP BY month
  ) t

赞(0）回复(0）举报 2022-10-31

我来回答

mysql SQL：SELECT AS具有相同FROM和不同WHERE的多个值

1条答案

相关问题

热门标签

最新问答