select @a= from table2 where ... select * from table1 where a=@a or @a=0
declare cursor cur_1 for select distinct a from table1; open cur_1; fetch cur_1 into :a1; do while sqlca.sqlcode = 0 then select * from table1 where a = a1; 可以定义一个游标,首先取出所有存在的a值,然后根据取出的a值去取得数据。
create proc test @Num int as if exists (select 1 from tempdb..sysobjects where name='##TEMP') select IDENTITY(int,1,1) as id,a into ##Temp from ( select distinct a from table1 ) as xselect x.* from table1 x,##Temp t where x.a=t.a and t.id=@Numgo调用: declare @Num int declare @Cnt int select @cnt=count(distinct a) from table1 set @Num=1 while @Num<=@Cnt begin exec test @Num set @Num=@Num+1 end
调用: declare @Num int declare @Cnt int select @cnt=count(distinct a) from table1 set @Num=1 while @Num<=@Cnt begin exec test @Num set @Num=@Num+1 enddrop table ##Temp
请指教
select * from table1 where a=@a or @a=0
select distinct a from table1;
open cur_1;
fetch cur_1 into :a1;
do while sqlca.sqlcode = 0 then
select * from table1 where a = a1;
可以定义一个游标,首先取出所有存在的a值,然后根据取出的a值去取得数据。
有没有仅从table1就可以实现的办法
@Num int
as
if exists (select 1 from tempdb..sysobjects where name='##TEMP')
select IDENTITY(int,1,1) as id,a
into ##Temp
from (
select distinct a from table1 ) as xselect x.* from table1 x,##Temp t
where x.a=t.a
and t.id=@Numgo调用:
declare @Num int
declare @Cnt int
select @cnt=count(distinct a) from table1
set @Num=1
while @Num<=@Cnt
begin
exec test @Num
set @Num=@Num+1
end
declare @Num int
declare @Cnt int
select @cnt=count(distinct a) from table1
set @Num=1
while @Num<=@Cnt
begin
exec test @Num
set @Num=@Num+1
enddrop table ##Temp