SQL:为具有多个 ID 的项目创建唯一 ID [英] SQL: creating unique id for item with several ids

问题描述

这个问题我琢磨了好久都没有找到解决办法(可能很简单.)

I've been pondering this problem for a while and can't find the solution (It might be simple.)

我有一个包含两列的表格，其中显示了哪些 ID 是相关联的，即属于同一个人.

I have a table with two columns which shows which ID's are connected, that is, belonging to the same person.

在这个例子中只有三个人，但其中一个人有三个唯一的 ID.

In this example there are only three individuals, but one of them has three unique IDs.

PID      | EPID
---------+--------
10004835 | 10004835
10015375 | 10015375
10015375 | 10019859
10019859 | 10015375
10019859 | 10019859
10019859 | 10000000
10000000 | 10019859
10020104 | 10020104

我想要做的只是在此表中添加一列，为每个唯一的个体提供唯一的代码.这就像

What I want to do is simply to add a column to this table which gives each unique individual a unique code. That is something like

PID      | EPID     | NPID
---------+----------+-----
10004835 | 10004835 | 1
10015375 | 10015375 | 2
10015375 | 10019859 | 2
10019859 | 10015375 | 2
10019859 | 10019859 | 2
10019859 | 10000000 | 2
10000000 | 10019859 | 2
10020104 | 10020104 | 3

附言.我正在使用 sqlite3，所以请不要在答案中使用递归.

Ps. I'm using sqlite3 so no recursion in answers please.

除非我能找到适用于 SQLITE3 的解决方案，否则我将不得不使用 MYSQL.在这种情况下，有人知道包含递归的解决方案吗?

Unless I can find a solution which works for SQLITE3 I will have to use MYSQL instead. In that case, does anyone know a solution which includes recursion?

推荐答案

如果您对任何连接的 ID 链的长度有上限，您可以多次自加入该表并获得最少(或最大的)所有 ID:

if you have an upper limit on how long any connected IDs chain can be, you can self-join the table that many times and get the least (or the greatest) of all the ids:

select pid, epid,
  min(t1.epid,
      coalesce(t2.epid, t1.epid),
      coalesce(t3.epid, t1.epid),
      coalesce(t4.epid, t1.epid),
      coalesce(t5.epid, t1.epid)) npid
from table t1
join table t2 on t1.epid = t2.pid and t2.epid not in (t1.epid)
join table t3 on t2.epid = t3.pid and t3.epid not in (t1.epid, t2.epid)
join table t4 on t3.epid = t4.pid and t4.epid not in (t1.epid, t2.epid, t3.epid)
join table t5 on t4.epid = t5.pid and t5.epid not in (t1.epid, t2.epid, t3.epid, t4.epid)
group by pid, epid

这篇关于SQL:为具有多个 ID 的项目创建唯一 ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！