如何循环查询多张表(grade1,grade2..grade10)?能不能把我10次查询写成一个循环
我每次都是 select * from grade1 where sex=1
select * from grade2 where sex=1
select * from grade3 where sex=1
....
select * from grade10 where sex=1
------解决方案--------------------
类似这样的东西，lz自己调整一下
declare @i INT
DECLARE @sql VARCHAR(100)
set @i = 1
while @i <=10
begin
set @sql ='select * from grade'+@i+' where sex = 1'
exec(@sql)
if @@error = 0
SET @i=@i+1
CONTINUE;
else
BREAK;
end
------解决方案--------------------
Declare @sql Nvarchar(max),@num smallint,@loop smallint
Set @num=10
------------ 1.
Select
@sql=isnull(@sql+' Union all ','')+'select * from grade'+ltrim(number)+' where sex=1'
From master.dbo.spt_values
Where type='P' And number >0 And number<=@num
Exec (@sql)
------------ 2.
Select
@sql=isnull(@sql+nchar(13)+nchar(10),'')+'select * from grade'+ltrim(number)+' where sex=1'
From master.dbo.spt_values
Where type='P' And number >0 And number<=@num
Exec (@sql)
------------ 3.
Set @loop=1
while @loop <= @num
Begin
Select @sql='select * from grade'+ltrim(@loop),@loop=@loop+1
Exec (@sql)
End
------解决方案--------------------
select (select ' select * from ['+name+'] ' from sys.tables where name like 'grade%' for xml path(''))+''