I have a problem with a SQL select query, I can't figure out what it needs to be.
我有一个SQL select查询的问题,我不知道它需要什么。
This is what my items
table look like:
这就是我的项目表:
| id | i_id | last_seen | spot |
----------------------------------------------------
| 1 | ls100 | 2017-03-10 15:30:40 | spot800 |
| 2 | ls100 | 2017-03-10 16:20:15 | spot753 |
| 3 | ls200 | 2017-03-10 16:33:10 | spot800 |
| 4 | ls300 | 2017-03-10 15:30:40 | spot800 |
| 5 | ls300 | 2017-03-10 12:10:30 | spot800 |
| 6 | ls400 | 2017-03-10 10:30:10 | spot800 |
This is what I'm trying to obtain:
这就是我想要得到的:
| id | i_id | last_seen | spot |
----------------------------------------------------
| 3 | ls200 | 2017-03-10 16:33:10 | spot800 |
| 5 | ls300 | 2017-03-10 12:10:30 | spot800 |
So I need to have the rows where spot= 'spot800', last_seen = MAX(but only if the DateTime is the newest compared to all spots with the same
i_id`), and at last the DateTime must be bigger than '2017-03-10 11:00:00'.
所以我需要有spot= 'spot800'、last_seen = MAX的行(但前提是DateTime与所有使用samei_id的点相比是最新的),最后DateTime必须大于'2017-03-10 11:00:00'。
This is what I have so far:
这是我目前所拥有的:
SELECT *
FROM items
WHERE spot = 'spot800'
HAVING MAX(`last_seen`)
AND `last_seen` > '2017-03-10 11:00:00'
3 个解决方案
#1
2
E.g.:
例如:
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,i_id INT NOT NULL
,last_seen DATETIME NOT NULL
,spot INT NOT NULL
);
INSERT INTO my_table VALUES
(1,100,'2017-03-10 15:30:40',800),
(2,100,'2017-03-10 14:20:15',753),
(3,200,'2017-03-10 16:33:10',800),
(4,300,'2017-03-10 15:30:40',800),
(5,300,'2017-03-10 12:10:30',800),
(6,400,'2017-03-10 10:30:10',800);
SELECT [DISTINCT] x.*
FROM my_table x
LEFT
JOIN my_table y
ON y.i_id = x.i_id
AND y.last_seen < x.last_seen
WHERE x.last_seen > '2017-03-10 11:00:00'
AND x.spot = 800
AND y.id IS NULL;
----+------+---------------------+------+
| id | i_id | last_seen | spot |
+----+------+---------------------+------+
| 3 | 200 | 2017-03-10 16:33:10 | 800 |
| 5 | 300 | 2017-03-10 12:10:30 | 800 |
+----+------+---------------------+------+
2 rows in set (0.00 sec)
#2
0
Use MAX
and GROUP BY
.
使用MAX和GROUP BY。
SELECT id, i_id, MAX(last_seen), spot
FROM items
WHERE spot = 'spot800'
AND last_seen > '2017-03-10 11:00:00'
GROUP BY id, i_id, spot
#3
0
There is several things wrng with your statement.
你的陈述中有几件事是错误的。
Firstly, HAVING must be accompanied with a GROUP BY clause, so it's not what you are looking for.
首先,必须有一组子句,所以这不是你想要的。
Also, MAX is an aggregate, not a boolean, function. That is, it cannot be used in filters, such as a where clause or a having clause. Also, if it did work, MAX would only return the entry that contains the time as '2017-03-10 16:33:10'. Not what you expected.
此外,MAX是一个集合,而不是布尔函数。也就是说,不能在筛选器中使用它,比如where子句或have子句。而且,如果它确实有效,MAX只会返回包含时间的条目,如“2017-03-10 16:33:10”。不是你的预期。
Try this instead:
试试这个:
SELECT * FROM items WHERE (spot='spot800' AND last_seen > '2017-03-10 11:00:00');
#1
2
E.g.:
例如:
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,i_id INT NOT NULL
,last_seen DATETIME NOT NULL
,spot INT NOT NULL
);
INSERT INTO my_table VALUES
(1,100,'2017-03-10 15:30:40',800),
(2,100,'2017-03-10 14:20:15',753),
(3,200,'2017-03-10 16:33:10',800),
(4,300,'2017-03-10 15:30:40',800),
(5,300,'2017-03-10 12:10:30',800),
(6,400,'2017-03-10 10:30:10',800);
SELECT [DISTINCT] x.*
FROM my_table x
LEFT
JOIN my_table y
ON y.i_id = x.i_id
AND y.last_seen < x.last_seen
WHERE x.last_seen > '2017-03-10 11:00:00'
AND x.spot = 800
AND y.id IS NULL;
----+------+---------------------+------+
| id | i_id | last_seen | spot |
+----+------+---------------------+------+
| 3 | 200 | 2017-03-10 16:33:10 | 800 |
| 5 | 300 | 2017-03-10 12:10:30 | 800 |
+----+------+---------------------+------+
2 rows in set (0.00 sec)
#2
0
Use MAX
and GROUP BY
.
使用MAX和GROUP BY。
SELECT id, i_id, MAX(last_seen), spot
FROM items
WHERE spot = 'spot800'
AND last_seen > '2017-03-10 11:00:00'
GROUP BY id, i_id, spot
#3
0
There is several things wrng with your statement.
你的陈述中有几件事是错误的。
Firstly, HAVING must be accompanied with a GROUP BY clause, so it's not what you are looking for.
首先,必须有一组子句,所以这不是你想要的。
Also, MAX is an aggregate, not a boolean, function. That is, it cannot be used in filters, such as a where clause or a having clause. Also, if it did work, MAX would only return the entry that contains the time as '2017-03-10 16:33:10'. Not what you expected.
此外,MAX是一个集合,而不是布尔函数。也就是说,不能在筛选器中使用它,比如where子句或have子句。而且,如果它确实有效,MAX只会返回包含时间的条目,如“2017-03-10 16:33:10”。不是你的预期。
Try this instead:
试试这个:
SELECT * FROM items WHERE (spot='spot800' AND last_seen > '2017-03-10 11:00:00');