求助：用SQL逐行查询记录里面的2个字段，并将这两个字段作为初始和结果值进行循环和另外一个表比对判断是否存在另外一个表里面

[lotusz]

循环的起始和终止值分别为图里的cstartbillcode和cendcode,我写好的代码如下，使用的游标（也只会这个），效率感觉很低，求高手提高效率，拆分出来会上千万行都不止，数据量很大。我的思路是先把这个数据拆分出来形成一个表，然后再和另外一个表来个连接查询应该比在这个拆分的过程中直接比对要效率高，技术不好，请大家多多指点

CREATE TABLE 已领发票号码库(
id INT IDENTITY,
billregisterid NVARCHAR(50),
cbillcode nvarchar(50)
)
DECLARE @billstart VARCHAR(50)
DECLARE @billend nVARCHAR(50)
DECLARE @curbillregisterid NVARCHAR(50)
DECLARE billcode CURSOR FOR (SELECT iBillRegisterID FROM allbill WHERE cstartbillcode<>cEndBillCode)
OPEN billcode
FETCH NEXT FROM billcode INTO @curbillregisterid
WHILE (@@fetch_status=0)
BEGIN
SET @billstart=(SELECT cstartbillcode FROM allbill WHERE iBillRegisterID=@curbillregisterid)
SET @billend=(SELECT cEndBillCode FROM allbill WHERE iBillRegisterID=@curbillregisterid )
WHILE @billstart<=@billend
BEGIN
INSERT INTO 已领发票号码库(billregisterid,cbillcode) VALUES (@curbillregisterid,@billstart)
SET @billstart=@billstart+1
END
FETCH NEXT FROM billcode INTO @curbillregisterid
END
INSERT INTO 已领发票号码库
SELECT iBillRegisterID,cEndBillCode FROM allbill WHERE iBillRegisterID=@curbillregisterid AND cstartbillcode=cEndBillCode
DROP TABLE 已领发票号码库

[yoan2014]

WITH a AS ( SELECT 2 AS ib , 537137501 AS cstart  , 537138000 AS cend UNION ALL
			SELECT 3 AS ib , 537138001 AS cstart  , 537138500 AS cend) , 
	b AS (SELECT number FROM MASTER.dbo.spt_values WHERE TYPE = 'P' )
			
SELECT cstart + number ,* FROM a CROSS JOIN b WHERE a.cend - cstart >= b.number

结果就不贴了，太长

[Ginnnnnnnn]

给个sample你看看是不是你想要的效果

;WITH CTE AS
(
	SELECT 1 AS iBillRegisteridid,2 AS cstartbillcode, 10 AS cendbillcode
	UNION ALL
	SELECT 2,5,8
	UNION ALL
	SELECT 3,10,10
)
SELECT a.*,
		a.cendbillcode-b.row AS cbillcode
	FROM CTE a
		INNER JOIN (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS row FROM sys.objects ) b ON b.row <= a.cendbillcode - cstartbillcode
	WHERE cstartbillcode <> cendbillcode
	ORDER BY a.iBillRegisteridid
	

/* 表数据 iBillRegisteridid cstartbillcode cendbillcode 1 2 10 2 5 8 3 10 10 结果 iBillRegisteridid cendbillcode cbillcode 1 10 9 1 10 8 1 10 7 1 10 6 1 10 5 1 10 4 1 10 3 1 10 2 2 8 7 2 8 6 2 8 5 */

[lotusz]

引用 2 楼 KanzakiOrange 的回复:

给个sample你看看是不是你想要的效果

;WITH CTE AS
(
	SELECT 1 AS iBillRegisteridid,2 AS cstartbillcode, 10 AS cendbillcode
	UNION ALL
	SELECT 2,5,8
	UNION ALL
	SELECT 3,10,10
)
SELECT a.*,
		a.cendbillcode-b.row AS cbillcode
	FROM CTE a
		INNER JOIN (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS row FROM sys.objects ) b ON b.row <= a.cendbillcode - cstartbillcode
	WHERE cstartbillcode <> cendbillcode
	ORDER BY a.iBillRegisteridid
	

/* 表数据 iBillRegisteridid cstartbillcode cendbillcode 1 2 10 2 5 8 3 10 10 结果 iBillRegisteridid cendbillcode cbillcode 1 10 9 1 10 8 1 10 7 1 10 6 1 10 5 1 10 4 1 10 3 1 10 2 2 8 7 2 8 6 2 8 5 */

不能全部出来，会丢失数据，我按楼上兄弟的方法修改了一下成功了

[lotusz]

引用 1 楼 yoan2014 的回复:

WITH a AS ( SELECT 2 AS ib , 537137501 AS cstart  , 537138000 AS cend UNION ALL
			SELECT 3 AS ib , 537138001 AS cstart  , 537138500 AS cend) , 
	b AS (SELECT number FROM MASTER.dbo.spt_values WHERE TYPE = 'P' )
			
SELECT cstart + number ,* FROM a CROSS JOIN b WHERE a.cend - cstart >= b.number

结果就不贴了，太长

谢谢兄弟，你这个思路很好，虽然不能完成我的需求。但是这个思路解决了我的问题。，我另外建立一张表来保存这个数字。主要是我这个开始票号和结束票号的差距很大。spt_alues值只能读到2047.我最大的间隔有上千万谢谢