我需要从几个表中选择数据。第一个表包含两列Paid From
和Payer Code
。根据Paid From
,我必须返回Student Name
(从学生表)或Employee Name
(从雇员表)或Account Name
(从帐户表)返回。到目前为止,我在SELECT
查询中使用了case表达式。我想知道是否有可能这样做LEFT JOIN
。
SELECT RV.PayerCode ,
( SELECT CASE WHEN PaidFrom = 1
THEN ( SELECT FullName
FROM Students
WHERE StudentID = RV.PayerCode
)
WHEN paidFrom = 2
THEN ( SELECT AccountName
FROM Accounts
WHERE AccountNumber = RV.PayerCode
)
WHEN paidfrom = 3 THEN ''
WHEN paidFrom = 4
THEN ( SELECT EmployeeName
FROM Employee
WHERE EmployeeID = RV.PayerCode
)
END
) AS PayerName
FROM dbo.Finance RV
试试这个
SELECT RV.PayerCode,
CASE
WHEN RV.paidfrom = 3 THEN ''
ELSE COALESCE(s.FullName, a.AccountName, e.EmployeeName)
END PayerName,
FROM dbo.Finance RV
LEFT OUTER JOIN dbo.Students s
ON s.StudentID = RV.PayerCode
AND RV.PaidFrom = 1
LEFT OUTER JOIN dbo.Accounts a
ON a.AccountNumber = RV.PayerCode
AND RV.PaidFrom = 2
LEFT OUTER JOIN dbo.Employee e
ON e.EmployeeID = RV.PayerCode
AND RV.PaidFrom = 4
注意:请考虑更改数据库结构
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句