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

求解:如何通过判断,在视图中获取不同的列值
建了一个por_inv的视图:
SELECT dbo.invdtf.fitemno, dbo.invdtf.fwhcd, dbo.invdtf.flotno2, dbo.invdtf.flotno, 
  dbo.invdtf.fcoilno1, dbo.invdtf.fvendinvc, dbo.porecf_porlsf.mfcoilno1, 
  dbo.porecf_porlsf.fitemno AS fitemno_b
FROM dbo.invdtf RIGHT OUTER JOIN
  dbo.porecf_porlsf ON dbo.invdtf.fcoilno1 = dbo.porecf_porlsf.mfcoilno1

现在想通过判断表invdtf中fvendinvc的值是否是"以S开头并且len(fvendinvc)= 18"来取值
如果是,则取视图中的fitemno,如果不是,在取fitemno_b

------解决方案--------------------
SQL code
SELECT CASE WHEN fvendinvc LIKE 'S%' AND len(fvendinvc)= 18 THEN fitemno ELSE fitemno_b END AS fitemno
FROM por_inv

------解决方案--------------------
SQL code
select (case when left(dbo.invdtf.fvendinvc,1)='S' then dbo.invdtf.fitemno else dbo.porecf_porlsf.fitemno end) AS fitemno,
  dbo.invdtf.fwhcd, dbo.invdtf.flotno2, dbo.invdtf.flotno,  
  dbo.invdtf.fcoilno1, dbo.invdtf.fvendinvc, dbo.porecf_porlsf.mfcoilno1
FROM dbo.invdtf RIGHT OUTER JOIN
  dbo.porecf_porlsf ON dbo.invdtf.fcoilno1 = dbo.porecf_porlsf.mfcoilno1

------解决方案--------------------
SQL code
CASE WHEN fvendinvc LIKE 'S%' AND len(fvendinvc)= 18 THEN fitemno ELSE fitemno_b END AS fitemno