27,580
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
--优点:效率高,占用空间小,一目了然
CREATE TABLE t(
searchNum INT,
startNum INT,
endNum INT,
addTime DATETIME DEFAULT(GETDATE())
)
ALTER TABLE t ADD CONSTRAINT ck_t_searchNum CHECK ( searchNum BETWEEN startNum AND endNum )
INSERT INTO t (searchNum,startNum,endNum) VALUES (2,1,6)
INSERT INTO t (searchNum,startNum,endNum) VALUES (3,1,6)
INSERT INTO t (searchNum,startNum,endNum) VALUES (5,2,10)
SELECT
searchNum
,startNum
,endNum
,STUFF(
(SELECT ','+CAST(sv.number AS VARCHAR(10))
FROM [master].dbo.spt_values AS sv
WHERE sv.[type]='P' AND sv.number BETWEEN t.startNum
AND t.endNum AND sv.number!=t.searchNum FOR XML PATH(''))
,1,1,'') AS result
,addTime
FROM t
/*
searchNum startNum endNum result addTime
2 1 6 1,3,4,5,6 2018-01-23 08:25:07.933
3 1 6 1,2,4,5,6 2018-01-23 08:25:07.933
5 2 10 2,3,4,6,7,8,9,10 2018-01-23 08:25:07.933
*/
LOT
ID Counted CreateDate
1 6 2018-01-22
2 6 2018-01-22
LotItem
ID LotID Num
1 1 1
2 1 2
3 1 3
......
7 2 1