英文:
SQL stored procedures use condition from one table
问题
如何为此存储过程添加对值“Location”的条件？
如果 tblEmployees.Office = USA 并且 tblEmployees.state = West，则 Location = CA，否则 Location = others：
CREATE PROCEDURE spEmployee
AS
BEGIN
SELECT
EmployeeId, Name, Gender, DepartmentName,
CASE
WHEN tblEmployees.Office = 'USA' AND tblEmployees.State = 'West' THEN 'CA'
ELSE 'others'
END AS Location
FROM
tblEmployees
INNER JOIN
tblDepartments ON tblEmployees.EmployeeDepartmentId = tblDepartments.DepartmentId
END
英文:
For this stored procedure, how to add a condition for value Location?
If tblEmployees.Office = USA and tblEmployees.state = West then Location = CA, else Location = others:
CREATE PROCEDURE spEmployee
AS
BEGIN
SELECT
EmployeeId, Name, Gender, DepartmentName, Location
FROM
tblEmployees
INNER JOIN
tblDepartments ON tblEmployees.EmployeeDepartmentId = tblDepartments.DepartmentId
END
答案1
得分: 1
这是一个相当简单的CASE表达式 - 类似于这样的：
CREATE PROCEDURE spEmployee
AS
BEGIN
SELECT
EmployeeId, Name, Gender, DepartmentName,
Location = CASE
WHEN Office = 'USA' AND State = 'West' THEN 'CA'
ELSE 'others'
END
FROM
tblEmployees e
INNER JOIN
tblDepartments d ON e.EmployeeDepartmentId = d.DepartmentId
END
英文:
That's a simple enough CASE expression - something like this:
CREATE PROCEDURE spEmployee
AS
BEGIN
SELECT
EmployeeId, Name, Gender, DepartmentName,
Location = CASE
WHEN Office = 'USA' AND State = 'West' THEN 'CA'
ELSE 'others'
END
FROM
tblEmployees e
INNER JOIN
tblDepartments d ON e.EmployeeDepartmentId = d.DepartmentId
END
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论