在 SQL 中查找 IsManager 的最简单方法 [英] Easiest way to find IsManager in SQL

问题描述

员工简单结构表

Employee      Manager
Joe Smith      Jon Smith
Jon Smith      Pete Stevens
Pete Stevens   NULL
Jared Scho     Pete Stevens
....

我只是想返回一些结果，但我想要一个关于此人是否是经理的指标，因此结果应该是:

Im just trying to return some results but I want an indicator on whether the person is a manager or not so the result should be:

Employee       Manager      IsAManager
Joe Smith     Jon Smith         0
Jon Smith     Pete Stevens      1
Pete Stevens    NULL            1
Jared Scho    Pete Stevens      0

结果集显示 Joe Smith 和 Jared Scho 不是经理......

The result set is showing that Joe Smith and Jared Scho are not managers...

如果我有一个简单的 SQL 查询

So If I had a simple SQL Query

SELECT
   Employee,
   Manager,
   As IsAManager   --tried to do a case statement here....
FROM
   Employee

我试图做一个case语句来达到这个效果:

I tried to do a case statement something to this effect:

SELECT CASE ISNULL(COUNT(*), 0) > 0 THEN 1 ELSE 0 END FROM Employee WHERE Manager = Employee

不知道怎么说:)

推荐答案

希望这只是一个演示示例，而不是您真正的表结构.

Hopefully this is just a demo example not your real table structure.

SELECT Employee,
       Manager,
       CASE
         WHEN EXISTS(SELECT *
                     FROM   Employee e2
                     WHERE  e2.Manager = e1.Employee) THEN 1
         ELSE 0
       END As IsAManager
FROM   Employee e1  

有关 SQL Server 如何处理 CASE 表达式中的 EXISTS 子查询的详细信息，请参阅 这篇文章.

For details of how SQL Server processes EXISTS Subqueries in CASE Expressions see this article.

这篇关于在 SQL 中查找 IsManager 的最简单方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！