SQL 插入临时值（选择.... 按 id 排序）

Insert into temp values (select.... order by id)

提问by seFausto

I'm using an Informix (Version 7.32) DB. On one operation I create a temp table with the ID of a regular table and a serial column (so I would have all the IDs from the regular table numbered continuously). But I want to insert the info from the regular table ordered by ID something like:

我使用的是 Informix（7.32 版）数据库。在一次操作中，我创建了一个临时表，其中包含一个常规表的 ID 和一个串行列（因此我将让常规表中的所有 ID 连续编号）。但我想插入按 ID 排序的常规表中的信息，例如：

CREATE TEMP TABLE tempTable (id serial, folio int );

INSERT INTO tempTable(id,folio)
SELECT 0,folio FROM regularTable ORDER BY folio;

But this creates a syntax error (because of the ORDER BY)

但这会产生语法错误（因为 ORDER BY）

Is there any way I can order the info then insert it to the tempTable?

有什么方法可以订购信息然后将其插入到临时表中吗？

UPDATE: The reason I want to do this is because the regular table has about 10,000 items and in a jsp file, it has to show every record, but it would take to long, so the real reason I want to do this is to paginatethe output. This version of Informix doesn't have Limitnor Skip. I can't renumber the serial because is in a relationship, and this is the only solution we could get a fixed number of results on one page (for example 500 results per page). In the Regular table has skipped id's (called folio) because they have been deleted. if i were to put

更新：我想这样做的原因是因为常规表有大约 10,000 个项目，并且在一个 jsp 文件中，它必须显示每条记录，但需要很长时间，所以我想这样做的真正原因是分页输出。这个版本的 Informix 没有也Limit没有Skip。我无法对序列号重新编号，因为处于关系中，这是我们可以在一页上获得固定数量结果的唯一解决方案（例如每页 500 个结果）。在常规表中已跳过 id（称为 folio），因为它们已被删除。如果我要放

SELECT * FROM regularTable WHERE folio BETWEEN X AND Y

I would get maybe 300 in one page, then 500 in the next page

我可能会在一页中得到 300，然后在下一页中得到 500

采纳答案by Dema

You might try it iterating a cursor over the SELECT ... ORDER BY and doing the INSERTs within the loop.

您可以尝试在 SELECT ... ORDER BY 上迭代光标并在循环中执行 INSERT。

回答by YWard

You can do this by breaking up the SQL into two temp tables:

您可以通过将 SQL 分解为两个临时表来实现此目的：

CREATE TEMP TABLE tempTable1 (
id serial,
folio int);

SELECT folio FROM regularTable ORDER BY folio
INTO TEMP tempTable2;

INSERT INTO tempTable1(id,folio) SELECT 0,folio FROM tempTable2;

回答by splattne

In Informix when using a SELECT as a sub-clause in an INSERT statement, you are limited to a subset of the SELECT syntax.

在 Informix 中，当在 INSERT 语句中使用 SELECT 作为子句时，您只能使用 SELECT 语法的一个子集。

The following SELECT clauses are not supported in this case:

在这种情况下不支持以下 SELECT 子句：

• INTO TEMP
• ORDER BY
• UNION.

• 进入温度
• 订购者
• 联盟。

Additionally, the FROM clause of the SELECT can not reference the same table as referenced by the INSERT (not that this matters in your case).

此外，SELECT 的 FROM 子句不能引用与 INSERT 引用的表相同的表（这对您的情况并不重要）。

回答by kurosch

It's been years since I worked on Informix, but perhaps something like this will work:

我在 Informix 上工作已经好几年了，但也许这样的事情会起作用：

INSERT INTO tempTable(id,folio)
SELECT 0, folio 
FROM (
    SELECT folio FROM regularTable ORDER BY folio
);

回答by Bill Karwin

It makes no sense to order the rows as you insert into a table. Relational databases do not allow you to specify the order of rows in a table.

在插入表时对行进行排序是没有意义的。关系数据库不允许您指定表中行的顺序。

Even if you could, SQL does not guarantee a query will return rows in any order, such as the order you inserted them. You must specify an ORDER BYclause to guarantee an order for a query result.

即使可以，SQL 也不保证查询会以任何顺序返回行，例如您插入它们的顺序。您必须指定一个ORDER BY子句来保证查询结果的顺序。

So it would do you no good to change the order in which you insert the rows.

因此，更改插入行的顺序对您没有好处。

回答by RET

As stated by Bill, there's not a lot of point ordering the input, you really need to order the output. In the simplistic example you've provided, it just makes no sense, so I can only assume that the real problem you're trying to solve is more complex - deduplication perhaps?

正如 Bill 所说，对输入进行排序并不多，您确实需要对输出进行排序。在您提供的简单示例中，它没有任何意义，所以我只能假设您要解决的真正问题更复杂 - 也许是重复数据删除？

The functionality you're after is CREATE SEQUENCE, but I'm pretty sure it's not available in such an old version of Informix.

您所追求的功能是CREATE SEQUENCE，但我很确定它在如此旧的 Informix 版本中不可用。

If you really need to do what you're asking, you could look into UNLOADing the data in the required order, and then LOADing it again. That would ensure the SERIAL values get allocated sequentially.

如果您确实需要执行您所要求的操作，则可以UNLOAD按所需顺序查看数据，然后LOAD再重新输入。这将确保 SERIAL 值按顺序分配。

回答by kurosch

Would something like this work?

这样的东西会起作用吗？

SELECT
    folio
FROM
    (
        SELECT
            ROWNUM n,
            folio
        FROM
            regularTable
        ORDER BY 
            folio
    )
WHERE
    n BETWEEN 501 AND 1000

It may not be terribly efficient if the table grows larger or you're fetching later "pages", but 10K rows is pretty small.

如果表变大或者您正在获取稍后的“页面”，它可能不是非常有效，但 10K 行非常小。

I don't recall if Informix has a ROWNUM concept, I use Oracle.

我不记得 Informix 是否有 ROWNUM 概念，我使用 Oracle。