case when 报错,啥回事解决办法

case when 报错,啥回事
select *
FROM dbo.t_Other AS a LEFT OUTER JOIN
  dbo.t_BillType AS b ON a.F_Type = b.F_Name LEFT OUTER JOIN
  dbo.t_Emp AS c ON a.F_EmpID = c.F_ID LEFT OUTER JOIN 
  (case when b.F_Mode='供应商' then t_Supplier when d.F_Mode='客户' then t_Client else t_Dept end)
  AS d ON a.F_UnitID = d.F_ID

我是根据不同选项选择不同的表连接,case when 错了,怎么改

[解决办法]
CASE WHEN 的结果是个值，不是集。所以不能用 LEFT JOIN 关联
[解决办法]

SQL code

--case when不能那样用的select a.*,    case when b.F_Mode='供应商'         then (select top 1 t_Supplier.你的列 from t_Supplier)        else (select top 1 t_Client.你的列 from t_Client)        end 此列的别FROM dbo.t_Other AS a LEFT OUTER JOIN  dbo.t_BillType AS b ON a.F_Type = b.F_Name LEFT OUTER JOIN  dbo.t_Emp AS c ON a.F_EmpID = c.F_ID
[解决办法]
case when 没你那样的用法。

select *
FROM dbo.t_Other AS a LEFT OUTER JOIN
 dbo.t_BillType AS b ON a.F_Type = b.F_Name LEFT OUTER JOIN
 dbo.t_Emp AS c ON a.F_EmpID = c.F_ID
  LEFT OUTER JOIN  t_Supplier d ON a.F_UnitID = d.F_ID and b.F_Mode='供应商'
  LEFT OUTER JOIN  t_Client e ON a.F_UnitID = e.F_ID and b.F_Mode='客户'
  LEFT OUTER JOIN  t_Dept f ON a.F_UnitID = f.F_ID and b.F_Mode not in('客户','供应商')

结果列自已打出来。
[解决办法]
CASE WHEN 的结果是个值，不是结果集，不能进行关联。楼主最好把case when 放到select后面或者group by后面实现想要的效果，比如类似下面的写法

SQL code
select a.*,    case when b.F_Mode='供应商'         then (select top 1 t_Supplier.你的列 from t_Supplier)        else (select top 1 t_Client.你的列 from t_Client)        end 此列的别FROM dbo.t_Other AS a LEFT OUTER JOIN  dbo.t_BillType AS b ON a.F_Type = b.F_Name LEFT OUTER JOIN  dbo.t_Emp AS c ON a.F_EmpID = c.F_ID