在已有的存储查询语句里增加一个条件问题

[BarryW]

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')日期,
应何如可以做到呢？

[paoluo]

try

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 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)

[paoluo]

子查詢改為用關聯來做，效率更優。

另外，你外面的條件“(trs_dd>='2007-04-01' and trs_dd<='2007-05-01') ”也可以加到動態語句裡面去。

[mengmou]

楼主挺面熟

[BarryW]

paoluo(一天到晚游泳的鱼) ( )

上面的条件通不过
提示：
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'Where'.

mengmou()mengmou() ( )
是啊，你有回答过我提的问题。

[paoluo]

不好意思，真是粗心

修改下

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 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)

[paoluo]

再改下


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)

[jefftong]

你有sunlike erp源代码，QQ是多少呀，有QQ交流