如何为每个外键生成序列号并将它们插入到不同的表中？

问题描述

如何为每个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然后它好的，但是当我们第二次运行它然后它生成重复的数字

解决方案

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

解决方案

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