有谁能解释一下甲骨文的局限性,直到为什么以下语句在MySQL中有效,但在Oracle中却收到“非组按表达式”?
SELECT order1.user_id,
order1.order_datetime,
SUM(order2.order_total)
FROM order_table order1
JOIN order_table order2 ON order1.user_id = order2.user_id
GROUP BY order1.user_id是因为Oracle不知道如何处理order_datetime列吗?它不能像在GROUP BY order1.user_id行中那样,从它从MySQL行接收的任何行返回列结果吗?
编辑:
我理解所有列都应该是按组排列的,但是我试图理解为什么Oracle不返回类似于MySQL的结果(而MySQL不需要每个组by,而MySQL需要每个组)。
发布于 2013-02-27 18:10:15
Oracle实际上正在执行正确的行为。使用GROUP BY时,选择列表中的项必须出现在GROUP BY或聚合函数中。
SELECT order1.user_id,
order1.order_datetime,
SUM(order2.order_total)
FROM order_table order1
JOIN order_table order2 ON order1.user_id = order2.user_id
GROUP BY order1.user_id, order1.order_datetimeMySQL使用一个允许不强制执行全组的行为的EXTENSION TO GROUP BY。在MySQL中使用这个值并不能保证order1.order_datetime的值是什么,MySQL只是选择一个值,结果可能是意外的。
您需要对GROUP BY列表中的所有项使用SELECT或聚合(类似于上面的内容),或者重写查询。您可以使用下列任何一种方法:
SELECT order1.user_id,
min(order1.order_datetime) order_datetime,
SUM(order2.order_total)
FROM order_table order1
JOIN order_table order2 ON order1.user_id = order2.user_id
GROUP BY order1.user_id它将聚合应用于order_datetime,则不必按日期分组。
您可以使用sum() over()
SELECT order1.user_id,
order1.order_datetime,
SUM(order2.order_total) over(partition by order1.user_id) order_total
FROM order_table order1
JOIN order_table order2 ON order1.user_id = order2.user_id或者可以使用子查询重写。
SELECT order1.user_id,
order1.order_datetime,
order2.order_total
FROM order_table order1
JOIN
(
select SUM(order_total) order_total, user_id
from order_table
group by user_id
) order2
ON order1.user_id = order2.user_id发布于 2013-02-27 18:11:44
在甲骨文中,需要在Select中指定group by中的所有列,如果您只想按一个列分组,而不是所有的列都这样做的话
SELECT order1.user_id,
order1.order_datetime,
SUM(order2.order_total) OVER (PARTITION BY order1.user_id) order_total
FROM order_table order1
JOIN order_table order2 ON order1.user_id = order2.user_id发布于 2013-02-27 18:19:44
见我的相关问题这里
Mysql为非分组函数/字段提供“任意”值,而Oracle强制执行分组函数或按字段分组的需要。
来自Mysql手册
MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values the server chooses.
https://stackoverflow.com/questions/15119152
复制相似问题