mysql相关查询

Question

我需要编写一个查询，其中我需要从一个表中检索数据。

Table 
===================================
ID | userID | Status |   date | 
===================================
1     3333     Queued    xxxx
2     4444     Queued    yyyy
3     5555     Finished  zzzzz
5     6666     Queued    iiiii
6     7777     Queued    kkkkk

现在，我希望仅当status=“排队”和status=“排队”的行数大于2且具有最新ID的行时才检索行。即我想要的答案是ID =6

我尝试使用以下查询

select  * from t1 where status = "Queued" GROUP BY status HAVING count(status) > 2 ORDER BY ID DESC limit 1

Answer 1

为此，您可以使用子查询。问题是排序是在分组之前完成的。像这样的东西应该是有效的：

select * from t1 where ID = (select max(id) from t1 where status = "Queued" group by status having count(status) > 2)

Answer 2

下面的查询应该可以解决这个问题：

select * from t1 
where id = (select max(id) from t1 where Status = 'Queued' group by Status having count(1)>2);

Answer 3

这个变种也是可能的-

SELECT * FROM (
  SELECT * FROM t1 WHERE status = 'Queued' ORDER BY ID DESC) t
GROUP BY status HAVING count(status) > 2;