22,302
社区成员




USE tempdb
GO
IF OBJECT_ID('dbo.[t1]') IS NOT NULL
DROP TABLE dbo.[t1]
GO
IF OBJECT_ID('dbo.[t2]') IS NOT NULL
DROP TABLE dbo.[t2]
GO
CREATE TABLE dbo.[t1](
[id] INT PRIMARY KEY
,[name] NVARCHAR(20)
,[num] INT
,[unit] NVARCHAR(10)
)
GO
CREATE TABLE dbo.[t2](
[t1Id] INT,
[no] INT
)
SET NOCOUNT ON
INSERT INTO dbo.[t1] VALUES(N'1',N'电脑',N'10',N'台')
INSERT INTO dbo.[t1] VALUES(N'2',N'空调',N'3',N'台')
GO
--注意局限:用 master.dbo.spt_values , 则 数量 <= 2047
INSERT INTO t2(t1Id,[no])
SELECT t1.id,sv.number
FROM t1 CROSS APPLY [master].dbo.spt_values AS sv WHERE sv.[type]='P' AND sv.number>0 AND sv.number<=t1.num
SELECT * FROM t2
/*
t1Id no
----------- -----------
1 1
1 2
1 3
1 4
1 5
1 6
1 7
1 8
1 9
1 10
2 1
2 2
2 3
*/
INSERT INTO 表2
SELECT id,
ROW_NUMBER() OVER (ORDER BY id) 编码
FROM 表1,
master.dbo.spt_values
WHERE type = 'P'
AND number
BETWEEN 1 AND num;
USE tempdb
GO
IF OBJECT_ID('dbo.[t1]') IS NOT NULL
DROP TABLE dbo.[t1]
GO
IF OBJECT_ID('dbo.[t2]') IS NOT NULL
DROP TABLE dbo.[t2]
GO
CREATE TABLE dbo.[t1](
[id] INT PRIMARY KEY
,[name] NVARCHAR(20)
,[num] INT
,[unit] NVARCHAR(10)
)
GO
CREATE TABLE dbo.[t2](
[t1Id] INT,
[no] NVARCHAR(20)
)
SET NOCOUNT ON
INSERT INTO dbo.[t1] VALUES(N'1',N'电脑',N'10',N'台')
INSERT INTO dbo.[t1] VALUES(N'2',N'空调',N'3',N'台')
GO
------------ 以上为测试数据 -----------
---- 1. 增加一个获取序数的表值函数 ----
IF OBJECT_ID('dbo.Fun_GetNumList') IS NOT NULL
DROP FUNCTION dbo.Fun_GetNumList
GO
-- =============================================
-- Author: yenange
-- Create date: 2018-11-26
-- Description: 构建序数 表值函数
-- =============================================
CREATE FUNCTION dbo.Fun_GetNumList
(
@n BIGINT
)
RETURNS TABLE
AS
RETURN
(
WITH cte AS (
SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
UNION ALL
SELECT 6 AS n UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
)
--1000
,cte2 AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rid FROM cte AS a CROSS APPLY cte AS b CROSS APPLY cte AS c
)
--10万
,cte3 AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rid FROM cte2 AS a CROSS APPLY cte2 AS b
)
--100亿
,cte4 AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS rid FROM cte2 AS a CROSS APPLY cte2 AS b CROSS APPLY cte2 AS c
CROSS APPLY
cte AS d
)
SELECT * FROM cte2 WHERE @n<=1000 AND rid<=@n
UNION ALL
SELECT * FROM cte3 WHERE @n>1000 AND @n<=100000 AND rid<=@n
UNION ALL
SELECT * FROM cte4 WHERE @n>100000 AND rid<=@n
)
GO
--2. 插入语句
INSERT INTO t2(t1Id,[no])
SELECT t1.id,''
FROM t1 CROSS APPLY dbo.Fun_GetNumList(t1.num)
SELECT * FROM t2
/*
t1Id no
----------- --------------------
1
1
1
1
1
1
1
1
1
1
2
2
2
*/
--测试数据
if not object_id(N'Tempdb..#T1') is null
drop table #T1
Go
Create table #T1([id] int,[name] nvarchar(22),[num] int,[unit] nvarchar(21))
Insert #T1
select 1,N'电脑',10,N'台' union all
select 2,N'空调',3,N'台'
GO
if not object_id(N'Tempdb..#T2') is null
drop table #T2
Go
Create table #T2([Id] int,[编码] NVARCHAR(20))
Go
--测试数据结束
INSERT INTO #T2(id)
SELECT id
FROM #T1,(SELECT ROW_NUMBER()OVER(ORDER BY a.number) number FROM master.dbo.spt_values a,
master.dbo.spt_values b
WHERE a.type = 'P'
AND b.type='P'
AND a.number BETWEEN 0 AND 200 --根据实际控制大小
AND b.number BETWEEN 0 AND 200 --根据实际控制大小
)t
WHERE t.number BETWEEN 1 AND num
SELECT * FROM #T2 ORDER BY Id