SQL 如何在SQL语句中的select语句中设置标志
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31501644/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to set flag in a select statement in a SQL statement
提问by Sree
I got Employee table where I select employee records when employee id is supplied, with in which I have to enable a flag if the employee record exists with 1 otherwise 0. I'm trying to accomplish this using the following script:
我得到了 Employee 表，我在提供员工 ID 时选择员工记录，如果员工记录存在，则我必须启用一个标志，否则为 0。我正在尝试使用以下脚本完成此操作：
DECLARE @FLAG INT
DECLARE @EMPID VARCHAR(10)
SELECT CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END AS @FLAG,
E.EMPID,
E.EMPNAME,
E.DESIGNATION
FROM
EMPLOYEE E LEFT JOIN
GEO23.EMPLOYEEDETAILS ED ON E.EMPID = ED.EMPID
WHERE E.EMPID = @EMPID
ORDER BY E.EMPID DESC
But this results in error. Can somebody please help. Please note I'm an amateur in SQL
但这会导致错误。有人可以帮忙吗。请注意，我是 SQL 的业余爱好者
回答by Gordon Linoff
You can set a flag or you can return results in SQL Server, but not both. So, the syntax that works for the query you have written:
您可以设置一个标志，也可以在 SQL Server 中返回结果，但不能同时返回。因此，适用于您编写的查询的语法：
SELECT @FLAG = (CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END)
FROM EMPLOYEE E LEFT JOIN
GEO23.EMPLOYEEDETAILS ED
ON E.EMPID = ED.EMPID
WHERE (E.EMPID = @EMPID)
ORDER BY E.EMPID DESC;
I left the query as you wrote it, but it has numerous issues:
我在你写的时候留下了查询，但它有很多问题：
- The
order byis unnecessary, because it only returns one row. - The
left joinis unnecessary, because it keeps all the rows in the first table, and you are only using acount().
- 该
order by是不必要的，因为它只返回一行。 - 该
left join是不必要的，因为它使在第一个表中的所有行，而您只使用一个count()。
So, an equivalent version is:
因此，等效版本是：
SELECT @FLAG = (CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END)
FROM EMPLOYEE E
WHERE E.EMPID = @EMPID;
For this query, I would recommend an index on employee(empid).
对于此查询，我建议在employee(empid).
回答by TLaV
Assuming EMPID is unique, you should just need to do something like this. If it finds the record it will be 1, otherwise it will be 0.
假设 EMPID 是唯一的，你应该只需要做这样的事情。如果找到该记录，则为 1，否则为 0。
DECLARE @FLAG INT
DECLARE @EMPID VARCHAR(10)
SELECT @FLAG = COUNT(*)
FROM EMPLOYEE E
WHERE (E.EMPID = @EMPID)
