Oracle SQL：使用Select语句插入序列

发布时间：2020-12-12 13:55:16 所属栏目：百科来源：网络整理

导读：基本上我想运行以下查询： INSERT INTO historical_car_stats (historical_car_stats_id,year,month,make,model,region,avg_msrp,count) SELECT my_seq.nextval,'2010','12','ALL',sum(avg_msrp * count) / sum(count),sum(count) FROM historical_car_stats

基本上我想运行以下查询：

INSERT INTO historical_car_stats (historical_car_stats_id,year,month,make,model,region,avg_msrp,count)
  SELECT
    my_seq.nextval,'2010','12','ALL',sum(avg_msrp * count) / sum(count),sum(count)
  FROM historical_car_stats
  WHERE year = '2010'
        AND month = '12'
        AND make != 'ALL'
  GROUP BY region;

它不工作，因为“序列号不允许在这里”SQL错误。我如何写这个，所以Oracle会让我做我想做的事情？

假设您要在按序列生成密钥之前对数据进行分组，那么就像您想要的那样

INSERT INTO HISTORICAL_CAR_STATS (
    HISTORICAL_CAR_STATS_ID,YEAR,MONTH,MAKE,MODEL,REGION,AVG_MSRP,CNT) 
SELECT MY_SEQ.nextval,cnt
  FROM (SELECT '2010' year,'12' month,'ALL' make,'ALL' model,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)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!