在列中生成0001到9000的数字，并为另一列生成循环 [英] Generate numbers from 0001 to 9000 in column and loop for another column

问题描述

你好我有2列的表我想要一个用这个格式号生成0001到9000的数字，第二个基于第一列，就像第一列每50个数字这个1和第二个50这是2



预订否收据否

1 0001-0050

2 0051-0100

3 0101 -0150





喜欢这个ofc它将是巨大的数据，但我需要它



谢谢你



我尝试过：



仍然不知道我该怎么办

hello i have table with 2 column i want one to generate numbers from 0001 to 9000 at this format number and second one based on first column like every 50 numbers from first column this 1 and second 50 this is 2

Book No Receipt No
1 0001-0050
2 0051-0100
3 0101-0150


like this ofc it will be huge data but i need it

thank u

What I have tried:

still don't have idea how can i do it

推荐答案

要生成序列，请参阅在SQL中生成序列 [ ^ ]

对于第一列[Book No]，请使用标识列：

To generate a sequence see Generating a Sequence in SQL[^]
For the first column [Book No] use an identity column:

CREATE TABLE Book
(
	[Book No] int identity(1,1),
	[Receipt No] varchar(9)
)

对于第二列，使用生成序列的方法，对其进行格式化（顺便说一下这是不好的做法）并像这样插入到表中

For the second column use the means of generating a sequence, format it (this is poor practice by the way) and insert into the table like this

;WITH q AS
(
    SELECT  1 AS num
    UNION ALL
    SELECT  num + 50
    FROM    q
    WHERE num < 9000 - 49
)
INSERT INTO Book
SELECT  FORMAT(num,'000#') + '-' + FORMAT(num + 49, '000#') -- SQL 2012 or greater 
--SELECT RIGHT('0000'+CAST(num AS VARCHAR(4)),4) + '-' + RIGHT('0000'+CAST(num + 49 AS VARCHAR(4)),4)		-- SQL 2008 or earlier
FROM    q
OPTION (MAXRECURSION 180) -- 900 / 50

请注意，如果您使用的是早于2012年的SQL版本，则需要将最后一个SELECT换成注释中的那个。







OP重申了他们的要求

Note if you are using a version of SQL earlier than 2012 you will need to swap out the last SELECT for the one in the comment.



OP has restated their requirements

CREATE TABLE Book
(
	[Book No] int,
	[Receipt No] int
)

;WITH q AS
(
    SELECT  1 AS num
    UNION ALL
    SELECT  num + 1
    FROM    q
    WHERE num < 9000 
), q2 as
(
	SELECT 1 as num2
	UNION ALL
	SELECT num2 + 1
	FROM q2
	WHERE num2 < 50
)
INSERT INTO Book
SELECT  Q.num, Q2.num2
FROM    q CROSS JOIN q2
OPTION (MAXRECURSION 9000)


SELECT * from Book
order by [Book No], [Receipt No]

......对要求的描述稍微好一些：

... a slightly better description of the requirements has come forth:

;WITH q AS
(
    SELECT  1 AS num
    UNION ALL
    SELECT  num + 1
    FROM    q
    WHERE num < 9000 
)
SELECT  num, FORMAT(NTILE(9000/50) OVER(ORDER BY num),'000#') AS Receipt
FROM    q 
ORDER BY num
OPTION (MAXRECURSION 9000)

这篇关于在列中生成0001到9000的数字，并为另一列生成循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！