I need to present a flag - 0 if a condition is not meet, 1 if it is - and I can do it in two different ways :
Get Employee ID, name, 1 if has others in suborder - 0 if not :
SELECT e.ID
, e.Name
, ISNULL ( ( SELECT TOP 1 1 FROM Employee se WHERE se.ManagerID = e.ID ) , 0 ) AS HasSubordinates
FROM Employee e
or
SELECT e.ID
, e.Name
, ISNULL ( ( SELECT 1 WHERE EXISTS ( SELECT * FROM Employee se WHERE se.ManagerID = e.ID ) ) , 0 ) AS HasSubordinates
FROM Employ开发者_运维知识库ee e
Which version would you choose and why ?
Update 1
How about this one ?
SELECT o.ID
, o.Name
, o.StartDate
, o.EndDate
, ISNULL ( ( SELECT TOP 1 1 FROM changes c WHERE c.ChangeDate BETWEEN o.StartDate AND o.EndDate ) , 0 ) AS IsChanged
FROM Orders o
or
SELECT o.ID
, o.Name
, o.StartDate
, o.EndDate
, ISNULL ( ( SELECT 1 WHERE EXISTS ( SELECT * FROM changes c WHERE c.ChangeDate BETWEEN o.StartDate AND o.EndDate ) ), 0 ) AS IsChanged
FROM Orders o
Neither, I'd use:
SELECT t.id,
t.name,
CASE WHEN x.managerid IS NULL THEN 0 ELSE 1 END AS HasSubordinates
FROM EMPLOYEE t
LEFT JOIN (SELECT DISTINCT
e.managerid
FROM EMPLOYEE e) x ON x.managerid = t.id
...because correlated SELECTS in the SELECT
clause are bad - they do not scale, because they execute for every row returned. Which means the more rows you have, the more times the correlated SELECT will be called.
I wouldn't do either...
The reason being that (and this is as far as I know) when you have a subselect in the select statement, that subselect will be executed once for every row that is returned. Hence if you had 100 rows returned by the main query you would in effect be running 101 queries. When you use a join you are only doing one query that needs to match the left and the right together. Note it would help if you have an index on ManagerId.
Try something like this:
SELECT e.ID,
e.Name,
COUNT(se.ID) AS TotalStaff
FROM Employee e
LEFT JOIN Employee se
ON se.ManagerID = e.ID
GROUP BY e.ID, e.Name
This is slightly different to what you had as I am returning the total and not just a 0|1 but that is easy to change if you need the 1|0...
Let me know if this helps
精彩评论