create table persionInfo(zjid int,name varchar(10))
insert into persionInfo select 1,'李四'
insert into persionInfo select 2,'李三'create table PersionScope(zjid int,scope int,zjname varchar(10))
insert into PersionScope select 1,100,'a'
insert into PersionScope select 1,20 ,'b'
insert into PersionScope select 2,50 ,'a'
insert into PersionScope select 2,30 ,'c'create table [user](zjname varchar(10))
insert into [user] select 'a'
insert into [user] select 'b'
insert into [user] select 'c'
insert into [user] select 'd'
insert into [user] select 'e'declare @s varchar(8000)
set @s = ''
select @s = @s + ',[zjname' + zjname + ' ''Scope] = max(case b.zjname when '''+zjname+''' then b.scope end)'
from [user] order by zjname
set @s = 'select a.zjid,a.name'+@s+' from persionInfo a,PersionScope b where a.zjid=b.zjid group by a.zjid,a.name order by a.zjid'
exec(@s)
insert into persionInfo select 1,'李四'
insert into persionInfo select 2,'李三'create table PersionScope(zjid int,scope int,zjname varchar(10))
insert into PersionScope select 1,100,'a'
insert into PersionScope select 1,20 ,'b'
insert into PersionScope select 2,50 ,'a'
insert into PersionScope select 2,30 ,'c'create table [user](zjname varchar(10))
insert into [user] select 'a'
insert into [user] select 'b'
insert into [user] select 'c'
insert into [user] select 'd'
insert into [user] select 'e'declare @s varchar(8000)
set @s = ''
select @s = @s + ',[zjname' + zjname + ' ''Scope] = max(case b.zjname when '''+zjname+''' then b.scope end)'
from [user] order by zjname
set @s = 'select a.zjid,a.name'+@s+' from persionInfo a,PersionScope b where a.zjid=b.zjid group by a.zjid,a.name order by a.zjid'
exec(@s)
create table persionInfo(zjid int,name varchar(10))
insert into persionInfo select 1,'李四'
insert into persionInfo select 2,'李三'create table PersionScope(zjid int,scope int,zjname varchar(10))
insert into PersionScope select 1,100,'a'
insert into PersionScope select 1,20 ,'b'
insert into PersionScope select 2,50 ,'a'
insert into PersionScope select 2,30 ,'c'create table user(zjname varchar(10))
insert into user select 'a'
insert into user select 'b'
insert into user select 'c'
insert into user select 'd'
insert into user select 'e'
--执行查询
declare @s varchar(8000)
set @s = ''
select @s = @s + ',[zjname(' + zjname + ' ''Scope)] = max(case b.zjname when '''+zjname+''' then b.scope end)'
from [user] order by zjname
exec('select a.zjid,a.name'+@s+' from persionInfo a,PersionScope b where a.zjid=b.zjid group by a.zjid,a.name order by a.zjid')
--输出结果
zjid name zjname(a 'Scope) zjname(b 'Scope) zjname(c 'Scope) zjname(d 'Scope) zjname(e 'Scope)
------- ------- ---------------- ---------------- ---------------- ---------------- ----------------
1 李四 100 20 NULL NULL NULL
2 李三 50 NULL 30 NULL NULL
能不能在加一些功能zjname'scopeX的求和呢sum'Scope)?
虽然不知道什么意思,一边学习,一边用。
libin_ftsafe(子陌红尘) !
libin_ftsafe(子陌红尘) ,在发一贴吧
libin_ftsafe(子陌红尘) ,帮帮啦!
--------------------------------------------------------------------------------
这个表达式最后还是等价于
{zjname(a 'Scope)+name(b 'Scope)+name(c 'Scope)+name(d 'Scope)+name(e'Scope)}*50%=sum'Scope 吧?直接用 sum(isnull(Scope,1)/2)不就可以了?
create table persionInfo(zjid int,name varchar(10))
insert into persionInfo select 1,'李四'
insert into persionInfo select 2,'李三'
insert into persionInfo select 3,'小张'
insert into persionInfo select 4,'小王'
insert into persionInfo select 5,'小刘'
create table PersionScope(zjid int,scope int,zjname char(1))
insert into PersionScope select 1,100,'a'
insert into PersionScope select 1,20 ,'b'
insert into PersionScope select 2,50 ,'a'
insert into PersionScope select 2,30 ,'c'
create table [user](zjname char(1))
insert into [user] select 'a'
insert into [user] select 'b'
insert into [user] select 'c'
insert into [user] select 'd'
--执行查询
declare @s varchar(8000)
set @s = ''
select @s = @s + ',[zjname('+zjname+'''scope)]=max(case b.zjname when '''+zjname+''' then b.scope else 0 end)' from [user]
exec('select a.zjid,a.name'+@s+',[sum{a*50+(b+c+D)*50%}]=sum(isnull(b.scope,0)/2) from persionInfo a left join PersionScope b on a.zjid=b.zjid group by a.zjid,a.name order by a.zjid')
--输出结果
zjid name zjname(a'scope) zjname(b'scope) zjname(c'scope) zjname(d'scope) sum{a*50+(b+c+D)*50%}
---- ---- --------------- --------------- --------------- --------------- ---------------------
1 李四 100 20 0 0 60
2 李三 50 0 30 0 40
3 小张 0 0 0 0 0
4 小王 0 0 0 0 0
5 小刘 0 0 0 0 0