表格如下:
id ss
01 g1
05 h1
08 g2
11 g2
25 h2
36 h1
.. ..想根据ss的不同值,输出不同的结果,比如:
ss='h1',select n2 from tb where id='05'
ss='h2',select n1 from tb where id='08'
id ss
01 g1
05 h1
08 g2
11 g2
25 h2
36 h1
.. ..想根据ss的不同值,输出不同的结果,比如:
ss='h1',select n2 from tb where id='05'
ss='h2',select n1 from tb where id='08'
@SS NCHAR(2)
AS
BEGIN
IF @SS='H1'
BEGIN
select n2 from tb where id='05'
END IF @SS='H2'
BEGIN
select n1 from tb where id='08'
END
END
GO
id,
ss,
case ss when 'h1' then n2
when 'h2' then n1
...
end as n
from tb这样的语句
declare mycursor cursor for select ss form table1
open mycursor
declare @ss varchar(10)
fetch next from mycursor into @ss
choose case @ss
case 'h1'
@sql= 'select n2 from tb where id='+@ss
end choose
exec(@sql)
close mycursor
deallocate mycursor
insert tb select '01', 'g1'
union all select '05', 'h1'
union all select '08', 'g2'
union all select '11', 'g2'
union all select '25', 'h2'
union all select '36', 'h1'select SQL=case when ss='h1' then 'select n2 from tb where id='+quotename(id, '''')
when ss='h2' then 'select n1 from tb where id='+quotename(id, '''')
else '' end
from tb--result
SQL
-----------------------------------------------------select n2 from tb where id='05'
select n1 from tb where id='25'
select n2 from tb where id='36'(6 row(s) affected)
case when ss = 'h1' then n2
when ss = 'h2' then n1
end as ss
from tb
if exists(select * from sysobjects where id=object_id('temp'))
drop table temp
create table temp
(
id varchar(10) primary key,
ss varchar(10)
)insert into temp select '01','g1'
union all select '05','h1'
union all select '08','g2'
union all select '11','g2'
union all select '25','h2'
union all select '36','h1'
select * from temp
select id,ss,n=(case when ss='g1' then (select ss from temp where id='05') else case when ss='h1' then (select ss from temp where id='08') else case when ss='g2' then (select ss from temp where id='11') end end end)from temp