SELECT
number
FROM master.dbo.spt_values
WHERE type='p'
AND number BETWEEN startNo AND endNo
number
FROM master.dbo.spt_values
WHERE type='p'
AND number BETWEEN startNo AND endNo
解决方案 »
- 求一SQL语句
- 安装sql server2000个人版的时候遇到的问题 Process Exit Code: (-1) 急!!!!附日志
- 菜鸟问题,解决后给分
- n久没用数据库了不得,问个别简单的确问题,急!
- sqlserver2005 存储事务
- 查询问题!
- 在sqlserver2008上做事务推送发布 其它机器在从发布服务器做订阅时候提示【进程无法向表“"dbo"."Dest_Description"”进行大容量】
- 送分的菜鸟问题。
- 使用insert-select-from-where遇到的问题-每次返回的结果不一致
- 关于compute求和如何合并,在线等待。
- 全文索引查询问题。。。。
- SQL2000 SQL2005 SQL2008 中数据库结构终极提问
set @sql=''
declare @start int
declare @end int
declare @i int
set @i=0
set @start=2
set @end=6
while (@start+@i<=@end)
begin
select @sql=@sql+','+cast(@start+@i as varchar)
set @i=@i+1
end
select @sql
set @sql=''
declare @start int
declare @end int
declare @i int
set @i=0
set @start=2
set @end=6
while (@start+@i<=@end)
begin
select @sql=@sql+','+cast(@start+@i as varchar)
set @i=@i+1
end
select stuff(@sql,1,1,'')----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2,3,4,5,6(1 行受影响)
可是select max(number) from master.dbo.spt_values where type='p'
都只有2047·
那如果startNO是3000,endNo是3005,那不是不能用了?
(
select number from master..spt_values where type='p' and number between 2 and 6
)
select stuff((select ','+ltrim(number) from t for xml path('')),1,1,'') as col
/*
col
---------------
2,3,4,5,6(1 行受影响)*/
那就自己生成临时表啊..SELECT TOP 100000 number=IDENTITY(INT,1,1) INTO # FROM syscolumns,sysobjects,systypes
刚才没有看清楚题目
是一条sql
你用下8楼de
;with t as
(
select rn=row_number() over(order by getdate()) from syscolumns a,syscolumns b,syscolumns c
)
select stuff((select ','+ltrim(rn) from t where rn between 2 and 6 for xml path('')),1,1,'') as col
/*
col
---------------
2,3,4,5,6(1 行受影响)*/用这样看看.
from
(
select top(6) row_number() over(order by getdate()) rn
from syscolumns a,syscolumns b,syscolumns c
) t
where rn>=2
/*
rn
--------------------
2
3
4
5
6(5 行受影响)
*/
你咋知道是 top 6……
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
Select * From Nums Where n Between 2 And 6
-- Author: liangCK 小梁
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (startNo INT,endNO INT)
INSERT INTO @T
SELECT 2,6--SQL查询如下:;WITH Liang AS
(
SELECT TOP(SELECT endNO-startNo+1 FROM @T)
ROW_NUMBER() OVER(ORDER BY o.[object_id])-1 AS ID,startNo
FROM sys.objects AS o,sys.columns,@T
)
SELECT ID+startNo FROM Liang
from
(
select top(SELECT endNO FROM A) row_number() over(order by getdate()) rn
from syscolumns a,syscolumns b,syscolumns c
) t
where rn>=(SELECT startNo FROM A)