SQL使用不同的数据和序列填充 [英] SQL Populating with distinct data and a sequence

问题描述

我的问题如下:

• 我需要根据实例中的信息填充汽车"表 汽车的租金.
• 我需要创建一个主键"car_id"，但仅适用于不同的主键 租金表中的车牌.
• 我正在使用序列创建car_id.

• I need to populate a 'cars' table based on information from instances of rentals of the cars.
• I need to create a primary key 'car_id' but only for distinct registration plates in the rentals table.
• I am creating the car_id with a sequence.

我尝试了以下代码，但收到错误消息:

I have tried the following code but receive an error:

--INSERT INTO cars c (c.plate, c.car_id)
SELECT DISTINCT cr.plate, car_id_seq.nextval
FROM cars_rentals cr
;

尽管这将有效(没有不同的车牌):

Although this will work (without distinct registration plates):

--INSERT INTO cars c (c.plate, c.car_id)
SELECT cr.plate, car_id_seq.nextval
FROM cars_rentals cr
;

(第一行带有注释，因此我可以立即看到要输出的值)

(The top line is commented so I can see the values I'm trying to output straight away)

所以！有谁知道我怎么能做? A)获取上面的代码以使用DISTINCT或B)找到一种方法来获取序列的MAXVALUE作为牌照的DISTINCT COUNT(这样我就可以做两个插入语句)

So! Does anyone know how I can either; A) Get the above code to work with DISTINCT or B) find a way to get MAXVALUE of the sequence as the DISTINCT COUNT of the registration plates (so I can do two insert statements)

提前谢谢！ 杰克

推荐答案

错误是:

ORA-02287:此处不允许使用序列号

ORA-02287: sequence number not allowed here

这将解决该问题:

SELECT cr.plate, car_id_seq.nextval
FROM (SELECT DISTINCT plate FROM cars_rentals) cr

这篇关于SQL使用不同的数据和序列填充的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！