Oracle SQL：在带有 Select 语句的插入中使用序列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7125936/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle SQL: Use sequence in insert with Select Statement
提问by Muhd
Basically I want to run the following query:
基本上我想运行以下查询：
INSERT INTO historical_car_stats (historical_car_stats_id, year, month, make, model, region, avg_msrp, count)
SELECT
my_seq.nextval,
'2010',
'12',
'ALL',
'ALL',
region,
sum(avg_msrp * count) / sum(count),
sum(count)
FROM historical_car_stats
WHERE year = '2010'
AND month = '12'
AND make != 'ALL'
GROUP BY region;
It doesn't work because "sequence number not allowed here" SQL error. How can I write this so Oracle will let me do what I want to do?
它不起作用，因为“此处不允许使用序列号”SQL 错误。我怎样才能写这个让 Oracle 让我做我想做的事？
回答by Justin Cave
Assuming that you want to group the data before you generate the key with the sequence, it sounds like you want something like
假设您想在使用序列生成密钥之前对数据进行分组，听起来您想要类似的东西
INSERT INTO HISTORICAL_CAR_STATS (
HISTORICAL_CAR_STATS_ID,
YEAR,
MONTH,
MAKE,
MODEL,
REGION,
AVG_MSRP,
CNT)
SELECT MY_SEQ.nextval,
year,
month,
make,
model,
region,
avg_msrp,
cnt
FROM (SELECT '2010' year,
'12' month,
'ALL' make,
'ALL' model,
REGION,
sum(AVG_MSRP*COUNT)/sum(COUNT) avg_msrp,
sum(cnt) cnt
FROM HISTORICAL_CAR_STATS
WHERE YEAR = '2010'
AND MONTH = '12'
AND MAKE != 'ALL'
GROUP BY REGION)
回答by Trieu Tuan Nghia
I tested and the script run ok!
我测试过，脚本运行正常！
INSERT INTO HISTORICAL_CAR_STATS (HISTORICAL_CAR_STATS_ID, YEAR,MONTH,MAKE,MODEL,REGION,AVG_MSRP,COUNT)
WITH DATA AS
(
SELECT '2010' YEAR,'12' MONTH ,'ALL' MAKE,'ALL' MODEL,REGION,sum(AVG_MSRP*COUNT)/sum(COUNT) AVG_MSRP,sum(Count) COUNT
FROM HISTORICAL_CAR_STATS
WHERE YEAR = '2010' AND MONTH = '12'
AND MAKE != 'ALL' GROUP BY REGION
)
SELECT MY_SEQ.NEXTVAL, YEAR,MONTH,MAKE,MODEL,REGION,AVG_MSRP,COUNT
FROM DATA;
you can read this article to understand more! http://www.orafaq.com/wiki/ORA-02287
你可以阅读这篇文章来了解更多！ http://www.orafaq.com/wiki/ORA-02287
