declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',sum(case when trs_dd='''+convert(char(10),trs_dd,120)+''' then qty else 0 end) as ['+convert(char(10),trs_dd,120)+']'
from TF_JBTZ where (trs_dd>='2007-04-01' and trs_dd<='2007-05-01') group by trs_dd
select @sql='select yg_no as 员工代号,姓名=(select [name]
from mf_yg where mf_yg.yg_no=tf_jbtz.yg_no),部门名称=(select dept.name
from mf_yg,dept where tf_jbtz.yg_no=mf_yg.yg_no and mf_yg.dep=dept.dep ),
离职日期=(select out_day from mf_yg where mf_yg.yg_no=tf_jbtz.yg_no ) '+@sql+' from TF_JBTZ group by yg_no'
exec (@sql)
请问在上面的存储查询条件里想加一个条件:
显示内容为MF_YG.OUT_DAY大于查询条件里的TF_JBTZ.trs_dd(from TF_JBTZ where (trs_dd>='2007-04-01')日期,
应何如可以做到呢?
set @sql=''
select @sql=@sql+',sum(case when trs_dd='''+convert(char(10),trs_dd,120)+''' then qty else 0 end) as ['+convert(char(10),trs_dd,120)+']'
from TF_JBTZ where (trs_dd>='2007-04-01' and trs_dd<='2007-05-01') group by trs_dd
select @sql='select yg_no as 员工代号,姓名=(select [name]
from mf_yg where mf_yg.yg_no=tf_jbtz.yg_no),部门名称=(select dept.name
from mf_yg,dept where tf_jbtz.yg_no=mf_yg.yg_no and mf_yg.dep=dept.dep ),
离职日期=(select out_day from mf_yg where mf_yg.yg_no=tf_jbtz.yg_no ) '+@sql+' from TF_JBTZ group by yg_no'
exec (@sql)
请问在上面的存储查询条件里想加一个条件:
显示内容为MF_YG.OUT_DAY大于查询条件里的TF_JBTZ.trs_dd(from TF_JBTZ where (trs_dd>='2007-04-01')日期,
应何如可以做到呢?
set @sql=''
select @sql=@sql+',sum(case when trs_dd='''+convert(char(10),trs_dd,120)+''' then qty else 0 end) as ['+convert(char(10),trs_dd,120)+']'
from TF_JBTZ where (trs_dd>='2007-04-01' and trs_dd<='2007-05-01') group by trs_dd
select @sql='select A.yg_no as 员工代号,姓名=B.[name], 部门名称= C.name'+@sql+' from TF_JBTZ A
Left Join mf_yg B On A.yg_no = B.yg_no
Left Join dept C On A.dep = C.dep group by A.yg_no, B.name, C.name
Where (A.trs_dd Between ''2007-04-01'' and ''2007-05-01'')
And B.OUT_DAY > A.trs_dd '
exec (@sql)
提示:
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'Where'.mengmou()mengmou() ( )
是啊,你有回答过我提的问题。
set @sql=''
select @sql=@sql+',sum(case when trs_dd='''+convert(char(10),trs_dd,120)+''' then qty else 0 end) as ['+convert(char(10),trs_dd,120)+']'
from TF_JBTZ where (trs_dd>='2007-04-01' and trs_dd<='2007-05-01') group by trs_dd
select @sql='select A.yg_no as 员工代号,姓名=B.[name], 部门名称= C.name'+@sql+' from TF_JBTZ A
Left Join mf_yg B On A.yg_no = B.yg_no
Left Join dept C On A.dep = C.dep
Where (A.trs_dd Between ''2007-04-01'' and ''2007-05-01'')
And B.OUT_DAY > A.trs_dd
group by A.yg_no, B.name, C.name'
exec (@sql)
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',sum(case when trs_dd='''+convert(char(10),trs_dd,120)+''' then qty else 0 end) as ['+convert(char(10),trs_dd,120)+']'
from TF_JBTZ where (trs_dd>='2007-04-01' and trs_dd<='2007-05-01') group by trs_dd
select @sql='select A.yg_no as 员工代号,姓名=B.[name], 部门名称= C.name'+@sql+' from TF_JBTZ A
Left Join mf_yg B On A.yg_no = B.yg_no
Left Join dept C On B.dep = C.dep
Where (A.trs_dd Between ''2007-04-01'' and ''2007-05-01'')
And B.OUT_DAY > A.trs_dd
group by A.yg_no, B.name, C.name'
exec (@sql)