在所有行均符合条件的MySQL中选择 [英] Select in MySQL where all rows meet a condition

问题描述

在MySQL中，如何选择每行满足特定条件的数据?例如，假设我有一个表显示员工何时上班，它具有三个字段:

In MySQL, how can you select data where every row meets a certain condition? For example lets say I have a table showing when employees arrived at work, it has three fields:

CREATE TABLE ArrivalTimes
(UserID INT
,Day DATE 
,ArrivalTime TIME
);

我想选择从未迟到(早上9点或更早到达)的员工的所有用户ID，什么是最好的方法?

I want to select all UserIDs of employees who have never been late (arrived 9am or earlier), what's the best way to do this?

推荐答案

@jjclarkson和@ davethegr8的答案很接近，但是您不能将聚合函数放在WHERE子句中.将为每行评估WHERE子句.

The answers from @jjclarkson and @davethegr8 are close, but you can't put aggregate functions in the WHERE clause. The WHERE clause is evaluated for each row.

您需要为每个组评估MAX()表达式，因此您需要使用HAVING子句.

You need to evaluate the MAX() expression for each group, so you need to use a HAVING clause.

尝试一下:

SELECT UserID 
FROM ArrivalTimes
GROUP BY UserID
HAVING MAX(ArrivalTime) <= '09:00:00';

---

@MBCook注释HAVING可能很慢.没错，这可能不是绝对快捷的方法来产生所需的结果.但是HAVING解决方案是最清晰的 .在某些情况下，性能的优先级低于清晰度和可维护性.

---

@MBCook comments that HAVING can be slow. You're right, it might not be the absolute quickest way to produce the desired result. But the HAVING solution is the most clear. There are situations where performance has lower priority than clarity and maintainability.

我查看了HAVING解决方案的EXPLAIN输出(在MySQL 5.1.30上):未使用任何索引，并且额外的注释为``Using temporary; Using filesort''，这通常意味着性能会很差.

I looked at the EXPLAIN output (on MySQL 5.1.30) for the HAVING solution: no indexes were used, and the extra notes said "Using temporary; Using filesort," which usually means performance will be poor.

考虑以下查询:

SELECT DISTINCT a1.UserID
FROM ArrivalTimes a1
  LEFT OUTER JOIN ArrivalTimes a2 
  ON (a1.UserID = a2.UserID AND a2.ArrivalTime > '09:00:00')
WHERE a2.UserID IS NULL;

这会生成一个优化计划，该计划使用UserID上的索引并说:

This generates an optimization plan that uses an index on UserID and says:

• a1:"Using index; Using temporary"
• a2:"Using where; Distinct"

• a1: "Using index; Using temporary"
• a2: "Using where; Distinct"

最后，以下查询生成了一个优化计划，该计划似乎最有效地使用了索引，并且没有临时表或文件排序.

Finally, the following query generates an optimization plan that appears to use indexes most effectively, and no temp tables or filesort.

SELECT DISTINCT a1.UserID
FROM ArrivalTimes a1
WHERE NOT EXISTS (SELECT * FROM ArrivalTimes a2 
                  WHERE a1.UserID = a2.UserID 
                    AND a2.ArrivalTime > '09:00:00'); 

• a1:"Using where; Using index"
• a2:"Using where"
• a1: "Using where; Using index"
• a2: "Using where"

这似乎最有可能表现最佳.诚然，我的测试表中只有四行，所以这不是代表性的测试.

This appears most likely to have the best performance. Admittedly, I only have four rows in my test table, so this isn't a representative test.

这篇关于在所有行均符合条件的MySQL中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！