MySQL SQL Union 给出奇怪的结果

Question

我在 MySQL 中有一个表，我想在其中获取有关查找 3 种情况的用户使用限制的统计信息

1. 过去一小时内用于登录的不同 IP 地址(ip 列)
2. 过去一天用户发帖数(cnt 列)
3. 过去 10 分钟内用户的 session 数(authid 列是用户名)

由于要提供控制对象，我需要这些三行，因此我开始使用 MySQL UNION，但得到了奇怪的结果。这是我所拥有的

mysql> desc senderlimits;
+----------+--------------+------+-----+-------------------+-------+
| Field    | Type         | Null | Key | Default           | Extra |
+----------+--------------+------+-----+-------------------+-------+
| authid   | varchar(128) | NO   |     | NULL              |       |
| ip       | varchar(40)  | NO   |     | NULL              |       |
| cnt      | int(11)      | NO   |     | 1                 |       |
| datetime | timestamp    | NO   |     | CURRENT_TIMESTAMP |       |
+----------+--------------+------+-----+-------------------+-------+
4 rows in set (0.00 sec)

mysql> select count(distinct ip) as ipl from senderlimits where authid='bob@nsd.clear.net' and datetime > date_sub(now(),INTERVAL 1 hour) UNION select sum(cnt) as cntl from senderlimits where authid='bob@nsd.clear.net' and datetime > date_sub(now(),INTERVAL 24 hour) UNION select sum(cnt) as vcnt from senderlimits where authid='bob@nsd.clear.net' and datetime > date_sub(now(),INTERVAL 10 minute) ;
+------+
| ipl  |
+------+
|    1 |
|   95 |
+------+
2 rows in set (0.04 sec)

mysql> select count(distinct ip) as ipl from senderlimits where authid='cincinnati@psmail.net' and datetime > date_sub(now(),INTERVAL 1 hour);
+-----+
| ipl |
+-----+
|   0 |
+-----+
1 row in set (0.01 sec)

mysql> select sum(cnt) as cntl from senderlimits where authid='bob@nsd.clear.net' and datetime > date_sub(now(),INTERVAL 24 hour) ;
+------+
| cntl |
+------+
|   95 |
+------+
1 row in set (0.02 sec)

mysql> select count(authid) as vcnt from senderlimits where authid='bob@nsd.clear.net' and datetime > date_sub(now(),INTERVAL 10 minute);
+------+
| vcnt |
+------+
|    1 |
+------+
1 row in set (0.02 sec)

mysql>

当我使用 UNION 选项时，结果非常奇怪。有高手知道为什么吗？或者我如何才能达到相同的结果0951

按这个顺序？

Answer 1

UNION 删除重复项。您想要UNION ALL:

select count(distinct ip) as ipl
from senderlimits
where authid = 'bob@nsd.clear.net' and datetime > date_sub(now(),INTERVAL 1 hour)
UNION ALL
select sum(cnt) as cntl
from senderlimits
where authid = 'bob@nsd.clear.net' and datetime > date_sub(now(),INTERVAL 24 hour)
UNION ALL
select sum(cnt) as vcnt
from senderlimits
where authid = 'bob@nsd.clear.net' and datetime > date_sub(now(),INTERVAL 10 minute) ;

但是，您不应该依赖于结果的顺序与子查询的顺序相同(这不能保证，即使在实践中经常发生)。您应该包含一列，指定哪个数字的含义:

select '1 hour' as which, count(distinct ip) as ipl
from senderlimits
where authid = 'bob@nsd.clear.net' and datetime > date_sub(now(),INTERVAL 1 hour)
UNION ALL
select '24 hour' as which, sum(cnt) as cntl
from senderlimits
where authid = 'bob@nsd.clear.net' and datetime > date_sub(now(),INTERVAL 24 hour)
UNION ALL
select '10 minutes' as which, sum(cnt) as vcnt
from senderlimits
where authid = 'bob@nsd.clear.net' and datetime > date_sub(now(),INTERVAL 10 minute) ;