Postgresql - 当一列具有重复值时，在行中递增计数器

Question

Postgresql - 当一列具有重复值时，在行中递增计数器

3

我已经向一个用于调度的表格中添加了一列(seq)，这样前端就可以管理每个项目显示的顺序。是否可能编写一个SQL查询来使用日期列中的公共重复值为该列填充递增计数器？

更改后

------------------------------------
| name | date_time           | seq |
------------------------------------
| ABC1 | 15-01-2017 11:00:00 |     |
| ABC2 | 16-01-2017 11:30:00 |     |
| ABC1 | 16-01-2017 11:30:00 |     |
| ABC3 | 17-01-2017 10:00:00 |     |
| ABC3 | 18-01-2017 12:30:00 |     |
| ABC4 | 18-01-2017 12:30:00 |     |
| ABC1 | 18-01-2017 12:30:00 |     |
------------------------------------

之后

------------------------------------
| name | date_time           | seq |
------------------------------------
| ABC1 | 15-01-2017 11:00:00 | 0   |
| ABC2 | 16-01-2017 11:30:00 | 0   |
| ABC1 | 16-01-2017 11:30:00 | 1   |
| ABC3 | 17-01-2017 10:00:00 | 0   |
| ABC3 | 18-01-2017 12:30:00 | 0   |
| ABC4 | 18-01-2017 12:30:00 | 1   |
| ABC1 | 18-01-2017 12:30:00 | 2   |
------------------------------------

解决了，感谢两位回答者。为了让其他人更容易找到，有效的代码如下：

UPDATE my_table f
    SET seq = seq2
    FROM (
        SELECT ctid, ROW_NUMBER() OVER (PARTITION BY date_time ORDER BY ctid) -1 AS seq2
        FROM my_table
        ) s
    WHERE f.ctid = s.ctid;

- corky

2个回答

3

请查看ROW_NUMBER()。

SELECT name, date_time, ROW_NUMBER() OVER (PARTITION BY date_time ORDER BY name) FROM [table]

- zambonee

这会很好地显示数字，但它不会填充新列。 - corky

这会返回以下错误：错误：关系“t”不存在第1行：UPDATE t set seq = seq2 FROM（ - corky

事实证明，我的UPDATE语句在PostGres中不起作用（但在SQL Server中可以）。您的表需要一个主键才能进行更新，或者您可以像https://dev59.com/uWw15IYBdhLWcg3wfb3i#6578705这样做。 - zambonee

谢谢，我按照Klin提供的链接使用了ctid。 - corky

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- klin · Accepted Answer

使用窗口函数 row_number()：

with my_table (name, date_time) as (
values

('ABC1', '15-01-2017 11:00:00'),
('ABC2', '16-01-2017 11:30:00'),
('ABC1', '16-01-2017 11:30:00'),
('ABC3', '17-01-2017 10:00:00'),
('ABC3', '18-01-2017 12:30:00'),
('ABC4', '18-01-2017 12:30:00'),
('ABC1', '18-01-2017 12:30:00')
)

select *, 
    row_number() over (partition by name order by date_time)- 1 as seq
from my_table
order by date_time;

 name |      date_time      | seq 
------+---------------------+-----
 ABC1 | 15-01-2017 11:00:00 |   0
 ABC1 | 16-01-2017 11:30:00 |   1
 ABC2 | 16-01-2017 11:30:00 |   0
 ABC3 | 17-01-2017 10:00:00 |   0
 ABC1 | 18-01-2017 12:30:00 |   2
 ABC3 | 18-01-2017 12:30:00 |   1
 ABC4 | 18-01-2017 12:30:00 |   0
(7 rows)

关于使用唯一整数更新现有记录的类似问题，请阅读此答案。