日期:2014-05-18  浏览次数:20368 次

高手帮忙看下case end用法
select d4.name retirewagelevelname,d3.name retitypename,d2.name metierfigurename,d1.name politicsvisagename,
o.siwangdate,o.solarbirthday,o.joindutydate,e.tuixiudate,o.id,o.code,o.name,o.sex,o.idcode,a.name orgnames,
o.status, (SELECT TOP 1 c.dischargedate FROM T_HR_CONTRACT c WHERE c.EMPLOYEEID=o.id ) dischargedate 
from t_hr_employee o LEFT JOIN T_HR_RETIREAPPLY e ON e.employeeid=o.id  
LEFT JOIN T_Sys_Dictvalue d1 ON o.politicsvisage=d1.id LEFT JOIN T_Sys_Dictvalue d2 ON o.metierfigure=d2.id 
LEFT JOIN T_Sys_Dictvalue d3 ON e.retitype=d3.id LEFT JOIN T_Sys_Dictvalue d6 ON o.tiptopdegree=d6.id  
LEFT JOIN T_HR_POSTINFO d7 ON o.positiontype=d7.id LEFT JOIN T_Sys_Dictvalue d8 ON o.position=d8.id  
LEFT JOIN (SELECT TOP 1 c.EMPLOYEEID,c.dischargedate FROM T_HR_Employee e1
LEFT JOIN T_HR_CONTRACT c ON e1.id=c.EMPLOYEEID


left join t_hr_employeeborrow eb on eb.dutyorgid='4028807b2ee1f49a012eeab65fdf052b'  
left join t_hr_employeeborrow eb1 on eb1.borroworgid='4028807b2ee1f49a012eeab65fdf052b' 


  where 1=1 ORDER BY c.dischargedate DESC) 
 d5 ON d5.EMPLOYEEID=o.id LEFT JOIN T_Sys_Dictvalue d4 ON e.retirewagelevel=d4.id,  
  F_ORG('4028807b2ee1f49a012eeab65fdf052b') a where o.status=2 and 
  (o.siwangstates =0 OR o.siwangstates IS null ) AND o.orgid=a.id ORDER BY a.code 


上面是原有的语句,现在我是想把下面查询的结果拼到上面的语句里面




关联一张表的两个字段,查询出是两种情况,但是我想让它只用一个字段来显示
 select (select name from T_HR_EmployeeBorrow as eb where dutyorgid='4028807b2f0973f5012f0b32b05f0664'), 
 --(select name from T_HR_EmployeeBorrow as eb where borroworgid='4028807b2f0973f5012f0b32b05f0664') as name2,
  casename=(case when name is not null then '借出' end)
  from t_hr_employee where orgid='4028807b2f0973f5012f0b32b05f0664'

此为一种结果
select name from T_HR_EmployeeBorrow as eb where dutyorgid='4028807b2f0973f5012f0b32b05f0664'  借出;
此为另一种结果
select name from T_HR_EmployeeBorrow as eb where borroworgid='4028807b2f0973f5012f0b32b05f0664' 借入;

每次传进的orgid=dutyorgid=borroworgid


------解决方案--------------------
探讨

引用:
select d4.name retirewagelevelname,d3.name retitypename,d2.name metierfigurename,d1.name politicsvisagename,
o.siwangdate,o.solarbirthday,o.joindutydate,e.tuixiudate,o.id,o.code,……

------解决方案--------------------
你说要传值不定义变量你怎么传呢?
这个你要传的值就是参数