如何根据总值创建序列号

Question

想了解如何根据总值/计数创建序列号字段吗？

有没有任何可能的方法可以在不使用while循环的情况下生成结果？谢谢

3

CustID	EmpID	TotalCount
40	1001
50	1002	5

解决此类问题的一种常见方法是使用Numbers表——一个只将数字从1递增到任意极限的表。这个表非常有用，所以在每个数据库中都有一个表是个好主意。从链接文章复制：

DECLARE @UpperBound INT = 1000000;
;WITH cteN(Number) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY s1.[object_id]) - 1
FROM sys.all_columns AS s1
CROSS JOIN sys.all_columns AS s2
)
SELECT [Number] INTO dbo.Numbers
FROM cteN WHERE [Number] <= @UpperBound;
CREATE UNIQUE CLUSTERED INDEX CIX_Number ON dbo.Numbers([Number]);

如果您有这样一个表，生成重复行可以使用CROSS JOIN:来完成

SELECT MyTable.*
FROM MyTable CROSS join Numbers 
WHERE  TotalCount < Numbers.Number

要添加序列号，可以使用ROW_number((OVER((。要为每个CustID获得不同的起点，可以将MIN((与OVER子句一起使用。聚合函数可以与限制其应用范围的OVER关闭一起使用：

SELECT 
MIN(CustID) OVER (PARTITION BY CustID)
+ ROW_NUMBER() OVER(ORDER BY CustId,Number)
AS SeqNo,
MyTable.*
FROM MyTable CROSS join Numbers 
WHERE  TotalCount < Numbers.Number

在我看来，最简单的方法是使用"numbers"表并将它们交叉连接以创建适当数量的行。

一个简单的数字表是

CREATE TABLE #n (n int PRIMARY KEY);
INSERT INTO #n (n) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);

数字表的一种更好的方法(实际上，与这个答案类似的总体方法(可以在"如何将列数据透视到不能超过最大数量总和的行中？"中找到？

现在，进行交叉连接从根本上说会在两个表之间"相乘"。主要是将数字表中的数字限制为TotalCount。

SELECT    MyTable.CustID + n.n AS SeqNum, MyTable.CustID, MyTable.EmpID, MyTable.TotalCount
FROM      MyTable
CROSS JOIN #n AS n
WHERE     n.n < TotalCount

您也可以使用内部联接来执行此操作，并将where子句移动为联接本身的一部分。

SELECT    MyTable.CustID + n.n AS SeqNum, MyTable.CustID, MyTable.EmpID, MyTable.TotalCount
FROM      MyTable
INNER JOIN #n AS n ON n.n < TotalCount

编辑：请注意，SeqNum在上面并不一定是唯一的——类似于@Luuk关于TotalCount超过10时会发生什么的评论/问题。还增加了INNER JOIN版本。

@seanb@pana

对于总计数，我将从我的表中获得最小和最大客户ID

CREATE TABLE #n (n int);
declare @start int, @end int
select @start= min(rk) from MyTable
select @end= max(rk) from MyTable
while @start <=@end
begin 
INSERT INTO #n (n) VALUES (@start)
set @start=@start+1
end

我只需要使用递归CTE:

with cte as (
select CustID, EmpID, TotalCount, 1 as n
from t
union all
select CustID, EmpID, TotalCount, n + 1
from cte
where n < TotalCount
)
select (CustId + n - 1) as seqno, CustID, EmpID, TotalCount
from cte;

如果需要添加100多行，则需要option (maxrecursion 0)作为查询中的最后一行。