如何将此sql查询转换为派生表？
如何将此sql查询转换为派生表？
提问于 2021-10-26 06:44:01
这是最初的查询
SELECT
YEAR(o.order_date) AS year,
COUNT(i.order_id) AS total
FROM
sales.order_items AS i
INNER JOIN
sales.orders AS o ON i.order_id = o.order_id
GROUP BY
YEAR(o.order_date)这就是我迄今所做的
SELECT
YEAR(time) AS year,
SUM(price) AS total
FROM
(SELECT
YEAR (o.order_date) AS time,
SUM (i.quantity * i.list_price) AS price
FROM
sales.order_items AS i
INNER JOIN
sales.orders AS o ON i.order_id = o.order_id) AS derived
GROUP BY
YEAR(time)
ORDER BY
YEAR(time)我知道这个错误：
列'sales.orders.order_date‘在选择列表中无效，因为它既不包含在聚合函数中，也不包含在GROUP BY子句
中。
回答 1
Stack Overflow用户
发布于 2021-10-26 06:46:46
试试这个：
SELECT
year,
SUM(price) AS total
FROM (
SELECT
YEAR (o.order_date) AS year,
SUM (i.quantity * i.list_price) AS price
FROM sales.order_items AS i
JOIN sales.orders AS o
ON i.order_id = o.order_id
GROUP BY YEAR (o.order_date)
) AS derived
GROUP BY year但我认为这也带来了同样的结果：
SELECT
YEAR (o.order_date) AS year,
SUM (i.quantity * i.list_price) AS price
FROM sales.order_items AS i
JOIN sales.orders AS o
ON i.order_id = o.order_id
GROUP BY YEAR (o.order_date)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/69718514
复制相关文章
相似问题
腾讯云开发者
