我要查询t_change 表中 特定范围内 employ_id 的和
于是用了以下的语句
selectsum(t.employ_id) over( order by t.employ_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
from t_change t
group by t.card_code;
但执行的时候抛出了异常： not a group by expression.
执行 selectsum(t.employ_id) over( order by t.employ_id)
from t_change t
group by t.card_code; 抛出了同样的异常。
但执行 selectsum(t.employ_id) from t_change tgroup by t.card_code;
确可以得到正确的结果。
请问这是什么原因？ 如何解决。
谢谢 jiqing1004 的回复。
selectsum(t.employ_id) over( partition by t.card_code order by t.employ_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
from t_change t
这样确实是可以的，但我想是将group by 与over 一起使用。
在《Oracle Database 11g SQL开发指南 》 中 聚合函数，over group by 是可以一起使用的。
比如在 Page 255
SELECT
month, SUM(amount) AS month_amount,
SUM(SUM(amount)) OVER
(ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS cumulative_amount
FROM all_sales
WHERE year = 2003
GROUP BY month
ORDER BY month;
但我用t_change 表仿照这种用法确会报错。
selectsum(t.employ_id) over( order by sum(t.employ_id) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
from t_change t
group by t.card_code;
请问这是为什么？
表错的地方在sum(t.employ_id)，
换成
selectsum(t.card_code) over( order by sum(t.employ_id) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
from t_change t
group by t.card_code;
可以，难道聚合函数里的参数只能是group by 的参数，没道理啊。
而且 在《Oracle Database 11g SQL开发指南 》 中
SELECT
month, SUM(amount) AS month_amount,
AVG(SUM(amount)) OVER
(ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
AS moving_average
FROM all_sales
WHERE year = 2003
GROUP BY month
ORDER BY month;
这样的例子很多，由于我没有 Oracle Database 11g SQL开发指南 》的脚本，所以没有执行过，但我想应该是没有问题的。
怎么没人啊， 只好自己再顶一下。
刚才发现：select
t.card_code,
sum(avg(t.employ_id)) over (order by t.card_code)
from t_change t
group by t.card_code;
可以成功。
难道 over 和group by 一起使用，一定要两个聚合函数一起使用吗？
select
t.card_code,
avg(t.employ_id) over (order by t.card_code)
from t_change t
group by t.card_code;
为什么会报错？
selectsum(t.card_code) over( order by sum(t.employ_id) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
from t_change t
group by t.card_code;
it should be:
selectt.card_code,sum(t.card_code) over( order by sum(t.employ_id) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
from t_change t
group by t.card_code;
谢谢 durexlollipop，
select t.card_code,
sum(t.card_code) over(order by sum(t.employ_id) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
from t_change t
group by t.card_code;
确实没有抛出异常，但我要求的是 employ_id 的和， 用card_code 分组。
改成 select t.card_code,
sum(t.employ_id) over(order by sum(t.employ_id) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
from t_change t
group by t.card_code;
还是抛出一样的异常。
麻烦大家再帮我看看！
