为具有相同数据组合的行分配相同的ID [英] Assign same id to rows with same combination of data

问题描述

我有一个包含很多列的表，其中 year ， folder 和 seq_no 字段用作记录的标识方法。我想为具有相同组合的记录分配相同的ID，并且（如果可能）ID的总数必须是连续的，以充分利用ID列的值。示例：

I have a table with many columns, among which the fields year, folder and seq_no serve as an identification method for the record. I'd like to assign the same id to those records that have this combination the same, and (if possible) the total of ids must be sequential, to make good use of the id column values. An example :

+-----+-----+------+------+-----+
|  id |year |folder|seq_no|count|
+=====+=====+======+======+=====+
|  1  |1973 |  5   |  11  |  2  | << 1973, 5, 11
+-----+-----+------+------+-----+
|  2  |2010 |  4   |  7   |  2  | << 2010, 4, 7
+-----+-----+------+------+-----+
|  3  |1973 |  11  |  12  |  1  | << 1973, 11, 12
+-----+-----+------+------+-----+
|  1  |1973 |  5   |  11  |  2  | << 1973, 5, 11
+-----+-----+------+------+-----+
|  4  |1500 |  4   |  7   |  1  | << 1500, 4, 7
+-----+-----+------+------+-----+
|  2  |2010 |  4   |  7   |  2  | << 2010, 4, 7
+-----+-----+------+------+-----+

但是，我希望ID不是通过php部分中的计算分配的，而是每个新条目检查时的表本身如果此条目具有与其他条目相同的组合。如果是，请分配相同的ID。如果不是，请分配下一个可用的ID。

However, I'd prefer that the id is not assigned by calculation in the php part, but that the table itself on every new entry checks if this entry has the same combination as other ones. If yes, assign same id. If not, assign the next available id.

此外，我想为每一行计数有多少个具有相同ID的记录，这也应该自动完成。

Also, i would like to count for each row how many records are there with the same id, and this should also be done automatically.

我正在考虑使用触发器或函数...不确定如何执行。

I was thinking of using triggers or functions ... not sure how to do that.

推荐答案

我不会尝试在表中存储此类ID，尤其是COUNT。

I would not try to store such IDs and especially COUNT in the table.

想象一下，在您的示例中，您想使用

Imagine, that in your example you want to insert one more row with

+-----+------+------+
|year |folder|seq_no|
+-----+------+------+
|1973 |  5   |  11  |
+-----+------+------+

服务器必须找到具有相同组合的所有现有行，并使用COUNT的新值对其进行更新。

The server would have to find all existing rows with the same combination and update them with the new value of COUNT.

每个 INSERT ， UPDATE 和 Delete 变得非常昂贵。

Each INSERT, UPDATE and DELETE becomes really expensive.

可以在需要时使用 DENSE_RANK 和 COUNT 计算此类信息：

This kind of information can be calculated when needed with DENSE_RANK and COUNT:

SELECT
    year
    ,folder
    ,seq_no
    ,DENSE_RANK() OVER(ORDER BY year, folder, seq_no) AS ID
    ,COUNT(*) OVER(PARTITION BY year, folder, seq_no) AS cnt
FROM YourTable

这篇关于为具有相同数据组合的行分配相同的ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！