当前位置：文江博客话题详情

如何在查询中生成序列号？

发布于 2024-10-13 04:33:20 字数 158 浏览 2 评论 0原文

我们使用的是 PostgreSQL v8.2.3。

如何在查询输出中生成序列号？我想显示查询返回的每一行的序列号。

示例：SELECTemployeeid,nameFROMemployee

我希望生成并显示每行从一开始的序列号。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

黑寡妇 2024-10-20 04:33:20

你有两个选择。

升级到 PostgreSQL v8.4 并使用 row_number() 函数：

SELECT row_number() over (ORDER BY something) as num_by_something, *
FROM table
ORDER BY something;

或者按照模拟 PostgreSQL Pre 8.4 中的行号。

You have two options.

Either upgrade to PostgreSQL v8.4 and use the row_number() function:

SELECT row_number() over (ORDER BY something) as num_by_something, *
FROM table
ORDER BY something;

Or jump through some hoops as described in Simulating Row Number in PostgreSQL Pre 8.4.

回复收藏 0 原文

跨年 2024-10-20 04:33:20

SELECT row_number() over (order by employeeid) as serial_number,
       employeeid,
       name
FROM employee

如果要根据名称排序分配数字，请在 over 子句中更改顺序

SELECT row_number() over (order by employeeid) as serial_number,
       employeeid,
       name
FROM employee

If you want to assign the numbers according to the sorting of the name, change the order by in the over clause

回复收藏 0 原文

眼藏柔 2024-10-20 04:33:20

如果您只想将行号添加到当前未排序选择结果中，那么您并不真的想对它们进行排序 - 这会(显然）改变那些无序行的顺序。

在这种情况下，只需将一个常量传递给 order by：

    SELECT row_number() over (order by 1) as serial_number,
           employeeid,
           name
    FROM employee

最可能的用例是默认表顺序使用行插入的顺序，并且您希望保留它（例如，也许有人给你一个来自 Excel 的 .csv 文件，它在左侧隐式地包含行号（在工作表之外），并且期望看到的行顺序与在那里输入的顺序相同（...我的东西。只需要处理:-)

然后，您可以使用它来保留无序的行序列
INSERT INTO<带有序列列的新表>;

在屏幕共享或代码黑客马拉松期间，有一种快速方法来引用特定行也可能很有用。

If you just want a row number added to your current unordered select results, then you don't really want to order them - that will (obviously) change the sequence of those unordered rows.

In that case, just pass a constant to order by:

    SELECT row_number() over (order by 1) as serial_number,
           employeeid,
           name
    FROM employee

The most likely use case would be where the default table order is using the order of row insertions, and you want to preserve that (eg, perhaps someone gave you a .csv from Excel that implicitly has row numbers on the left (outside the sheet) and is expecting to see the rows in the same order as they were entered there. (...something I just had to deal with :- )

Then, you can use that to preserve the unordered row sequence by
INSERT INTO<new table with sequence column> <above select query>;

It may also be useful during a screen share or code hackathon to have a quick way to reference specific rows.

回复收藏 0 原文

~没有更多了~