刚刚解决,谢谢大家关注, 正确的语句是 select case when isnumeric(cvalue)=1 then (select name from hr_depart where hr_depart.id=cvalue) else cvalue end cvalue, case (select count(*) from hr_jbda_r where hr_jbda_r.todate=dateadd(d,-1,r.fromdate) and hr_jbda_r.id=r.id and hr_jbda_r.citem=r.citem) when 1 then case isnumeric((select cvalue from hr_jbda_R where hr_jbda_r.todate=dateadd(d,-1,r.fromdate) and hr_jbda_r.id=r.id and hr_jbda_r.citem=r.citem )) when 1 then (select name from hr_depart where hr_depart.id=(select cvalue from hr_jbda_R where hr_jbda_r.todate=dateadd(d,-1,r.fromdate) and hr_jbda_r.id=r.id and hr_jbda_r.citem=r.citem )) else (select cvalue from hr_jbda_R where hr_jbda_r.todate=dateadd(d,-1,r.fromdate) and hr_jbda_r.id=r.id and hr_jbda_r.citem=r.citem ) end else '' end lastvalue, r.id,r.citem,convert(char(10),r.fromdate,121) fromdate,convert(char(10),r.todate,121) todate, r.bz ,da.name from hr_jbda_r r inner join hr_jbda da on da.id=r.id where citem='工作部门' and FromDate between '2008-1-1' and '2009-4-20' order by r.ID desc,fromdate
select case when isnumeric(r.cvalue)=1 then (select name from depart where hr_depart.id=cvalue) else cvalue end cvalue, case (select count(*) from jbda_r where todate=dateadd(d,-1,r.fromdate) and id=r.id and citem=r.citem) when 1 then case isnumeric((select cvalue from jbda_r where todate=dateadd(d,-1,r.fromdate) and id=r.id and citem=r.citem)) when 1 then (select name from hr_depart where hr_depart.id=r.cvalue) else r.cvalue end else '' end lastvalue, r.id,r.citem,convert(char(10),r.fromdate,121) fromdate,convert(char(10),r.todate,121) todate, r.bz ,da.name from jbda_r r where citem='工作部门' and FromDate between '2008-12-1' and '2009-4-20' 试试
正确的语句是 select
case when isnumeric(cvalue)=1
then (select name from hr_depart where hr_depart.id=cvalue)
else cvalue end cvalue,
case (select count(*) from hr_jbda_r where hr_jbda_r.todate=dateadd(d,-1,r.fromdate) and hr_jbda_r.id=r.id and hr_jbda_r.citem=r.citem)
when 1 then
case isnumeric((select cvalue from hr_jbda_R where hr_jbda_r.todate=dateadd(d,-1,r.fromdate) and hr_jbda_r.id=r.id and hr_jbda_r.citem=r.citem ))
when 1
then (select name from hr_depart where hr_depart.id=(select cvalue from hr_jbda_R where hr_jbda_r.todate=dateadd(d,-1,r.fromdate) and hr_jbda_r.id=r.id and hr_jbda_r.citem=r.citem ))
else (select cvalue from hr_jbda_R where hr_jbda_r.todate=dateadd(d,-1,r.fromdate) and hr_jbda_r.id=r.id and hr_jbda_r.citem=r.citem ) end else ''
end lastvalue,
r.id,r.citem,convert(char(10),r.fromdate,121) fromdate,convert(char(10),r.todate,121) todate,
r.bz ,da.name
from hr_jbda_r r
inner join hr_jbda da on da.id=r.id
where citem='工作部门' and FromDate between '2008-1-1' and '2009-4-20'
order by r.ID desc,fromdate