如何为每个外键生成序列号并将它们插入到不同的表中？ [英] How to generate serial numbers for each foreign key and insert them in a different table?

问题描述

如何为每个refno no生成序列号并将它们插入另一个

（表B）。将根据表B生成发票号（ - MAX（）+ 1来自表B）？ MS SQL 2012.

表A 
 
 packname refno 
 ------ ----------------------------- ----------- 
演示250频道1 
 qqq 1 
 qqq 3 
 qqq 3 
演示250频道1 
演示250频道1 
 
结果我想要
表B 
 
 packname refno InvoiceNo 
 -----------------------------------  - ------------------- 
演示250频道1 1 
 qqq 1 2 
 qqq 1 3 
 qqq 3 1 
演示250频道3 2 
演示250频道5 1 

我有什么tr ied：



SELECT packname，refno，

ROW_NUMBER（）OVER（PARTITION BY refno ORDER BY（SELECT 1）） InvoiceNo

FROM Table1



这个过程每天运行并获取一些记录并插入Table2所以当我们第一次插入table2然后它好的，但是当我们第二次运行它然后它生成重复的数字

解决方案

试试这个

  SELECT  packname，refno，ROW_NUMBER（） OVER （ PARTITION   BY  refno  ORDER   BY 包名称）InvoiceNo 
  FROM  表 A 

How do I can generate serial no for each refno no and insert them to a different
(table B) .Invoice number will be generate based on Table B( - MAX()+1 from Table B) ? MS SQL 2012.

Table A

packname                            refno
----------------------------------- -----------
Demo 250 Channels                   1
qqq                                 1
qqq                                 3
qqq                                 3
Demo 250 Channels                   1
Demo 250 Channels                   1

Result I want
Table B

packname                            refno      InvoiceNo
----------------------------------- --------------------
Demo 250 Channels                   1                1
qqq                                 1                2
qqq                                 1                3
qqq                                 3                1
Demo 250 Channels                   3                2
Demo 250 Channels                   5                1

What I have tried:

SELECT packname, refno,
ROW_NUMBER() OVER (PARTITION BY refno ORDER BY (SELECT 1)) InvoiceNo
FROM Table1

This process run every day and fetch some record and insert into Table2 so when we insert in table2 first time then its OK, but when we run this on second time then its generate duplicate number

解决方案

Try this

SELECT packname, refno,ROW_NUMBER() OVER (PARTITION BY refno ORDER BY Packname) InvoiceNo
FROM Table A

这篇关于如何为每个外键生成序列号并将它们插入到不同的表中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！