MySQL 优化查询 COUNT-ing DISTINCT 值或 NULL IF NO ROWS

Question

我需要从时间表中获取不同运动员的数量，其中时间小于其他时间。但棘手的部分是如果根本没有时间(为了比较，我需要返回 NULL)。

举个例子:

CREATE TABLE `teams` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO teams 
VALUES (NULL, 'Texas'), (NULL,'Oklahoma');

mysql> select * from teams;
+----+----------+
| id | name     |
+----+----------+
|  1 | Texas    |
|  2 | Oklahoma |
+----+----------+


CREATE TABLE `times` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `team_id` int(11) NOT NULL,
  `time` decimal(8,2) NOT NULL,
  `athlete` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `team_id` (`team_id`)
);

INSERT INTO times VALUES 
  (NULL, 1, 19.10, 'Dave'),
  (NULL, 1, 19.09, 'Dave'),
  (NULL, 1, 19.07, 'Dave'),
  (NULL, 1, 19.56, 'John'),
  (NULL, 1, 19.60, 'John'),
  (NULL, 1, 19.75, 'John');

mysql> select * from times;
+----+---------+-------+---------+
| id | team_id | time  | athlete |
+----+---------+-------+---------+
|  1 |       1 | 19.10 | Dave    |
|  2 |       1 | 19.09 | Dave    |
|  3 |       1 | 19.07 | Dave    |
|  4 |       1 | 19.56 | John    |
|  5 |       1 | 19.60 | John    |
|  6 |       1 | 19.75 | John    |
+----+---------+-------+---------+

到目前为止，我们有两个团队。 Team 1有两名运动员 6 次 和Team 2没有运动员(分别是时间)。

如果我想知道How many Athletes from Texas are faster than 19.50我能做到:

SELECT COUNT(DISTINCT athlete) FROM times WHERE time < 19.50 and team_id = 1;
+-------------------------+
| COUNT(DISTINCT athlete) |
+-------------------------+
|                       1 |
+-------------------------+

这是正确的。

如果我想检查 How many Athletes from Texas are faster than 19.00

SELECT COUNT(DISTINCT athlete) FROM times WHERE time < 19.00 and team_id = 1;
+-------------------------+
| COUNT(DISTINCT athlete) |
+-------------------------+
|                       0 |
+-------------------------+

也是正确的(因为我们有来自 Texas 的两名运动员)。

但如果我想检查:How many Athletes from Oklahoma are faster than 19.00

SELECT COUNT(DISTINCT athlete) FROM times WHERE time < 19.00 and team_id = 2;
+-------------------------+
| COUNT(DISTINCT athlete) |
+-------------------------+
|                       0 |
+-------------------------+

不正确因为我们没有时间来自Oklahoma .所以在这里我需要得到 NULL作为返回。

我设法找到了一个带有子查询的解决方案:

SELECT
  IF(
    EXISTS(
      SELECT 1 FROM times WHERE team_id = 2
    ),
    COUNT(DISTINCT athlete),
    NULL
  ) as count
FROM `times`
WHERE
  team_id = 2 AND time < 19.00;
+-------+
| count |
+-------+
|  NULL |
+-------+

这是正确的，如果我针对 Texas 进行测试，我得到:

SELECT 
  IF(
    EXISTS(
      SELECT 1 FROM times WHERE team_id = 1
    ),
    COUNT(DISTINCT athlete),
    NULL
  ) as count
FROM `times`
WHERE
  team_id = 1 AND time < 19.00;

+-------+
| count |
+-------+
|     0 |
+-------+

它返回正确的答案。

但问题是我使用了 sub-query这必须模仿主查询的所有过滤器，除了time < 19.00 .在我的实际应用程序中有更多的过滤器，我正在寻找没有子查询的解决方案。

想到的一件事是使用 SUM(CASE)

SELECT SUM(CASE 
  WHEN time < 19.50 
    THEN 1 
    ELSE 0 
  END) as count
FROM `times`
WHERE team_id = 2;

+-------+
| count |
+-------+
|  NULL |
+-------+

问题是这是计算时间而不是不同的运动员，因此对于Texas我会得到错误的计数；

SELECT SUM(CASE 
  WHEN time < 19.50 
    THEN 1 
    ELSE 0 
  END) as count
FROM `times`
WHERE team_id = 1;

+-------+
| count |
+-------+
|     3 |
+-------+

我比 19.50 快 3 倍而不是 1 名运动员 比 19.50 快.

Answer 1

怎么样...

SELECT a.name
     , MAX(b.athlete < 19.50) faster 
  FROM teams a 
  LEFT 
  JOIN times b 
    ON b.team_id = a.id 
 GROUP BY a.name;