--自己做临时表来做了create table #t(id int)declare @i int set @i = 1 while (@i < 10000) begin insert into #t select @i set @i = @i + 1 endselect * from #t
--系统表0-2047 select max(number) from master..spt_values where type='p' /* 2047
select isnull((select count(1) from sysobjects where id <t.id),0) as num from sysobjects t
select a.number *100+b.number from (select number from master..spt_values where type='p' and number<100) a cross join (select number from master..spt_values where type='p' and number<100) b
sql2005及以上版本 select top 9999 rn=row_number() over(order by getdate()) from sys.objects,sys.columns
WITH Nums AS ( SELECT 1 n UNION ALL SELECT n+1 FROM Nums where n<9999 ) SELECT * FROM Nums OPTION (MAXRECURSION 10000)递归的,2005以上
WITH cteA AS( SELECT 1 AS digit UNION ALL SELECT 2 ) ,cteB AS( SELECT 1 AS digit UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) ,cteC AS( SELECT a.digit FROM cteA a CROSS JOIN cteB) SELECT TOP 9999 ROW_NUMBER() OVER(ORDER BY a.digit) FROM cteC a CROSS JOIN ctec b CROSS JOIN ctec c CROSS JOIN ctec d 用递归的话,当递归次数很大的时候,就不太合适了,而且递归层次不能超过32767树人的比较快,其实也是做迪卡尔积.只是要保存sysobjects表和syscolumns表有足够多的数据,还好9999足够其他用临时表,子查询都很慢了
sql server 2000select top 9999 identity(int,1,1) as [id] into #T1 from sysobjects,syscolumns select * from #T1
--自己做临时表来做了create table #t(id int)declare @i int
set @i = 1
while (@i < 10000)
begin
insert into #t select @i
set @i = @i + 1
endselect * from #t
--系统表0-2047
select max(number)
from master..spt_values
where type='p'
/*
2047
select a.number *100+b.number from
(select number from master..spt_values where type='p' and number<100) a cross join (select number from master..spt_values where type='p' and number<100) b
select top 9999 rn=row_number() over(order by getdate())
from sys.objects,sys.columns
不,懂;帮,顶;学,习;赚,分。
虽然这么回贴可能会被删除。
谁他妈的删除了就是个王八蛋。
强烈抗议CSDN这种即当婊子又想立贞洁牌坊的恶劣行为!!!
有本事,就封杀我!!!理由在这里:
他妈的一帮管理员有神经病啊???
http://topic.csdn.net/u/20110422/10/1a381057-7a7b-41d9-8969-ad1f5b77f24a.html?38665
不,懂;帮,顶;学,习;赚,分。
虽然这么回贴可能会被删除。
谁他妈的删除了就是个王八蛋。
强烈抗议CSDN这种即当婊子又想立贞洁牌坊的恶劣行为!!!
有本事,就封杀我!!!理由在这里:
他妈的一帮管理员有神经病啊???
http://topic.csdn.net/u/20110422/10/1a381057-7a7b-41d9-8969-ad1f5b77f24a.html?38665
不,懂;帮,顶;学,习;赚,分。
虽然这么回贴可能会被删除。
谁他妈的删除了就是个王八蛋。
强烈抗议CSDN这种即当婊子又想立贞洁牌坊的恶劣行为!!!
有本事,就封杀我!!!理由在这里:
他妈的一帮管理员有神经病啊???
http://topic.csdn.net/u/20110422/10/1a381057-7a7b-41d9-8969-ad1f5b77f24a.html?38665
AS
(
SELECT 1 n
UNION ALL
SELECT n+1 FROM Nums where n<9999
)
SELECT * FROM Nums OPTION (MAXRECURSION 10000)递归的,2005以上
WITH cteA AS(
SELECT 1 AS digit
UNION ALL
SELECT 2
)
,cteB AS(
SELECT 1 AS digit
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5)
,cteC AS(
SELECT a.digit FROM cteA a CROSS JOIN cteB)
SELECT TOP 9999 ROW_NUMBER() OVER(ORDER BY a.digit) FROM cteC a CROSS JOIN ctec b CROSS JOIN ctec c CROSS JOIN ctec d
用递归的话,当递归次数很大的时候,就不太合适了,而且递归层次不能超过32767树人的比较快,其实也是做迪卡尔积.只是要保存sysobjects表和syscolumns表有足够多的数据,还好9999足够其他用临时表,子查询都很慢了
select * from #T1