我的table foobar
包含以下列: val: tinyint NOT NULL date: timestamp NOT NULL type: enum('A', 'B', 'C') NOT NULL extra: tinyint NOT NULL
对于每个 type
我想找到与列上的任意条件匹配的行(例如。 extra > 12 AND val > 0
),使 val
在同等条件下 val
,最小化 date
. 我想每个人 type
这样一行存在并且是唯一的。最后,我希望得到结果(尽可能多的行) type
值)由 val
, date
.
如果 foobar
包含以下行:
+------+---------------------+------+-------+
| val | date | type | extra |
+------+---------------------+------+-------+
| -1 | 2014-04-10 00:00:00 | A | 40 |
| 1 | 2014-04-15 00:00:00 | A | 15 |
| 2 | 2014-04-12 00:00:00 | A | 77 |
| 1 | 2014-04-11 00:00:00 | A | 2 |
| 1 | 2014-04-14 00:00:00 | A | 22 |
| 1 | 2014-04-10 00:00:00 | B | 40 |
| 1 | 2014-04-15 00:00:00 | B | 15 |
| 1 | 2014-04-12 00:00:00 | B | 77 |
| 1 | 2014-04-11 00:00:00 | B | 2 |
| 1 | 2014-04-14 00:00:00 | B | 22 |
| 4 | 2014-04-10 00:00:00 | C | 40 |
| 3 | 2014-04-15 00:00:00 | C | 15 |
| 3 | 2014-04-12 00:00:00 | C | 77 |
| 1 | 2014-04-11 00:00:00 | C | 2 |
| 3 | 2014-04-14 00:00:00 | C | 22 |
+------+---------------------+------+-------+
查询应返回:
+------+---------------------+------+-------+
| val | date | type | extra |
+------+---------------------+------+-------+
| 1 | 2014-04-10 00:00:00 | B | 40 |
| 1 | 2014-04-14 00:00:00 | A | 22 |
| 3 | 2014-04-12 00:00:00 | C | 77 |
+------+---------------------+------+-------+
这似乎奏效了:
SELECT a.* FROM (
SELECT MIN(val * 4294967296 + UNIX_TIMESTAMP(date)) AS score
FROM foobar WHERE extra > 12 AND val > 0
GROUP BY type
) AS b
INNER JOIN foobar AS a
ON a.val * 4294967296 + UNIX_TIMESTAMP(a.date) = b.score
ORDER BY val, date;
但我觉得太复杂了,我怀疑一定有更好的办法。此外,将多列条件转换为单个数值( val * 4294967296 + UNIX_TIMESTAMP(date)
)在这种简单的情况下有效,但在更复杂的情况下可能更困难。
有没有其他更通用的方案也会这样做?
1条答案
按热度按时间ghhkc1vu1#