mysql - 如果sql查询中的记录相同，如何分配相同的唯一行号？

Question

关闭。这个问题需要details or clarity .它目前不接受答案。












想改善这个问题吗？通过 editing this post 添加详细信息并澄清问题.

9 个月前关闭。




Improve this question




我正在尝试为记录生成行号，但对于同一条记录，我得到了 2 个唯一的行号。如果记录相同，是否有可能生成相同的唯一编号？
例子:

GENERIC_ID|Generic Name|HTA_INSERT_DT
2|Eculizumab|2021-02-10 05:50:28
7|Eculizumab|2021-02-10 05:50:28
9|Eculizumab|2021-02-10 05:50:28

在我想要的最终输出中-

GENERIC_ID|Generic Name|HTA_INSERT_DT
2|Eculizumab|2021-02-10 05:50:28
2|Eculizumab|2021-02-10 05:50:28
2|Eculizumab|2021-02-10 05:50:28

我在 sql 查询下运行-

SELECT ROW_NUMBER() OVER(Order by (select NULL)) as GENERIC_ID
     , a.`Generic Name`
     , date_format(current_timestamp(),'yyyy-MM-dd hh:mm:ss') as HTA_INSERT_DT 
  from 
     ( 
Select distinct `Generic Name` from vw_non_onco_pharma 
 Union 
Select distinct `Generic Name` from vw_plasma_protein 
 Union 
Select distinct `Generic Name` from vw_non_onco_cell_gene 
 Union 
Select distinct `Generic Name` from vw_onco_cell_gene 
 Union 
Select distinct `Generic Name` from vw_onco_pharma 
 Union 
Select distinct `Generic Name` from vw_non_onco_no_id 
 Union 
Select distinct `Generic Name` from vw_onco_no_id
) a

Answer 1

您可以 DENSE_RANK()按您的关键字段排序的记录以获取集合中的匹配 ID。如果您希望从顺序计数器中删除重复项，请使用 RANK()反而。

 DECLARE @X TABLE(ID1 INT, ID2 INT)
INSERT @X VALUES(1,1),(2,1),(2,2),(1,2),(1,2),(1,3),(1,1)

SELECT 
    R = RANK() OVER(PARTITION BY 1 ORDER BY ID1,ID2),
    DR = DENSE_RANK() OVER(PARTITION BY 1 ORDER BY ID1,ID2),
    ID1,
    ID2
FROM 
    @X

R   DR ID1  ID2
1   1   1   1
1   1   1   1
3   2   1   2
3   2   1   2
5   3   1   3
6   4   2   1
7   5   2   2

但是，在重新阅读您的问题后。我现在认为您正在寻找类似于添加派生 key 来标识您的联合记录的内容，如下所示。

SELECT 
DENSE_RANK() OVER(PARTITION BY 1 ORDER BY a.`Generic Name`,date_format(current_timestamp(),'yyyy-MM-dd hh:mm:ss')) as GENERIC_ID
     , a.`Generic Name`
     , date_format(current_timestamp(),'yyyy-MM-dd hh:mm:ss') as HTA_INSERT_DT 
  from 
     ( 
Select distinct `Generic Name` from vw_non_onco_pharma 
 Union 
Select distinct `Generic Name` from vw_plasma_protein 
 Union 
Select distinct `Generic Name` from vw_non_onco_cell_gene 
 Union 
Select distinct `Generic Name` from vw_onco_cell_gene 
 Union 
Select distinct `Generic Name` from vw_onco_pharma 
 Union 
Select distinct `Generic Name` from vw_non_onco_no_id 
 Union 
Select distinct `Generic Name` from vw_onco_no_id
) a