请各位大侠帮助
表1:
bz_id(int) bz_name(varchar(50))
表2:
pbz_id(int主键) u_id(int) u_name(varchar(50)) bz_id(int)
存储过程如下:
declare @sql varchar(8000)
set @sql='select a.u_id as '+'登录名,'+'a.u_name as '+'值班人'
select @sql=@sql+',max(case a.pbz_date when cast(a.pbz_date as varchar) then b.bz_name else '' end)['+cast(a.pbz_date as varchar(10))+']'
from (select distinct a.pbz_date from a ) as c
set @sql=@sql+'from personbanzhong a,banzhong b where a.bz_id=b.bz_id group by a.u_id,a.u_name'
print(@sql)
错误信息:
服务器: 消息 208,级别 16,状态 1,行 3
对象名 'a' 无效。
表1:
bz_id(int) bz_name(varchar(50))
表2:
pbz_id(int主键) u_id(int) u_name(varchar(50)) bz_id(int)
存储过程如下:
declare @sql varchar(8000)
set @sql='select a.u_id as '+'登录名,'+'a.u_name as '+'值班人'
select @sql=@sql+',max(case a.pbz_date when cast(a.pbz_date as varchar) then b.bz_name else '' end)['+cast(a.pbz_date as varchar(10))+']'
from (select distinct a.pbz_date from a ) as c
set @sql=@sql+'from personbanzhong a,banzhong b where a.bz_id=b.bz_id group by a.u_id,a.u_name'
print(@sql)
错误信息:
服务器: 消息 208,级别 16,状态 1,行 3
对象名 'a' 无效。
set @sql='select a.u_id as '+'登录名,'+'a.u_name as '+'值班人'
select @sql=@sql+',max(case a.pbz_date when cast(a.pbz_date as varchar) then b.bz_name else '' end)['+cast(a.pbz_date as varchar(10))+']'
from (select distinct a.pbz_date from a ) as c
set @sql=@sql+'from personbanzhong a,banzhong b where a.bz_id=b.bz_id group by a.u_id,a.u_name'将@sql拼接的时候 就会出现两个from c from a这样的了
set @sql='select a.u_id as '+'登录名,'+'a.u_name as '+'值班人'
select @sql=@sql+',max(case a.pbz_date when cast(a.pbz_date as varchar) then b.bz_name else '' end)['+cast(a.pbz_date as varchar(10))+']'
from (select distinct a.pbz_date from a ) as c
set @sql=@sql+',personbanzhong a,banzhong b where a.bz_id=b.bz_id group by a.u_id,a.u_name'
print(@sql)
declare @sql varchar(8000)
set @sql='select a.u_id as '+'登录名,'+'a.u_name as '+'值班人'
select @sql=@sql+',max(case a.pbz_date when cast(a.pbz_date as varchar) then b.bz_name else '' end)['+cast(a.pbz_date as varchar(10))+']'
from (select distinct a.pbz_date from personbanzhong a ) as c
set @sql=@sql+'from a,banzhong b where a.bz_id=b.bz_id group by a.u_id,a.u_name'
print(@sql)