表test1
id tlgcl tlmoney scgcl scmoney
----------- ----------- ----------- ----------- -----------
1 20 200 30 300
2 40 400 50 500
要求写成函数; 当传入参数id=1时得到
name gcl money
---- ----------- -----------
sc 30 300
tl 20 200
id=2时得到:
name gcl money
---- ----------- -----------
sc 50 500
tl 40 400
id tlgcl tlmoney scgcl scmoney
----------- ----------- ----------- ----------- -----------
1 20 200 30 300
2 40 400 50 500
要求写成函数; 当传入参数id=1时得到
name gcl money
---- ----------- -----------
sc 30 300
tl 20 200
id=2时得到:
name gcl money
---- ----------- -----------
sc 50 500
tl 40 400
create table test1 (id int,tlgcl int,tlmoney int,scgcl int,scmoney int)
insert test1
select 1,20,200,30,300 union all
select 2,40,400,50,500
go--创建内嵌表函数
create function fn_test1(@id int)
returns table
as
return
(
select name='tl',gcl=tlgcl,money=tlmoney from test1 where id=@id
union all
select name='sc',gcl=scgcl,money=scmoney from test1 where id=@id
)
go--查询
select * from fn_test1(2)
select * from fn_test1(2)
--删除测试
/*
drop table test1
drop function fn_test1
*/
create table test1 (id int,tlgcl int,tlmoney int,scgcl int,scmoney int)
insert test1
select 1,20,200,30,300 union all
select 2,40,400,50,500
go--创建内嵌表函数
create function fn_test1(@id int)
returns table
as
return
(
select name='tl',gcl=tlgcl,money=tlmoney from test1 where id=@id
union all
select name='sc',gcl=scgcl,money=scmoney from test1 where id=@id
)
go--查询
select * from fn_test1(1) --笔误
select * from fn_test1(2)
--删除测试
/*
drop table test1
drop function fn_test1
*/
(
id int,
tlgcl int,
tlmoney int,
scgcl int,
scmoney int
)insert test1
select 1, 20, 200, 30, 300
union all
select 2, 40, 400, 50, 500select * from
(
select id,gcl=tlgcl,[money]=tlmoney from test1
union all
select id,scgcl,scmoney from test1
)tmp
where id='1'
可以将下列语句
select name='tl',gcl=tlgcl,money=tlmoney from test1 where id=@id
union all
select name='sc',gcl=scgcl,money=scmoney from test1 where id=@id
写成动态吗?
能将下列语句
select id,gcl=tlgcl,[money]=tlmoney from test1
union all
select id,scgcl,scmoney from test1写成动态吗?
create table a (id int,tlgcl int,tlmoney int,scgcl int,scmoney int)
insert into a select 1,20,200,30,300
union all select 2,40,400,50,500--创建测试过程
create proc caifen
@id int
as
begin
select name='sc',gcl=scgcl,money=scmoney from a where id=@id
union all select name='tl', gcl=tlgcl,money=tlmoney from a where id=@id
end
因此想追求用最简的语句搞定
select * from fn_test1(2)----------------id 通过参数传进去,还不是动态吗如果不是,想实现什么样的动态,告知。另:函数里不支持 EXECUTE(字串)
是啊,字段动态!