mysql - 表格加入Mysql

Question

我的表结构是:

SELECT * FROM `MYTABLE` WHERE `USERID` = 148 ORDER BY `TABLEDATE` DESC;

ID  USERID STATE_ID   TABLEDATE              
96  148    13         2013-01-08 06:11:34   
554 148     3         2013-01-03 04:46:08   
25  148     2         2013-01-02 04:46:07   

我想要表“MYTABLE”(=> 按 STATE_ID DESC 限制 1,1 排序)中用户的第二个最高条目，其最后条目日期为日期“2013-01-08”。

我尝试形成查询但失败了。请帮忙？

SELECT F1.USERID,
  MAX(F2.TABLEDATE),
  F2.STATE_ID
FROM MYTABLE F1 
 JOIN MYTABLE F2 ON F1.USERID = F2.USERID WHERE
   F1.STATE_ID = 13 AND
   F2.TABLEDATE < F1.TABLEDATE AND
   F1.TABLEDATE <= '2013-01-08 23:59:59' AND
   F1.TABLEDATE >=  '2013-01-08 00:00:00' 
 GROUP BY F1.USERID 

输出

148 2013-01-03 04:46:08 2

预期输出

148 2013-01-03 04:46:08 3

Answer 1

如果您想要 State_ID 值第二高的行，您可以简单地这样做:

SELECT *
FROM 
(
   SELECT *
   FROM yourTable
   LIMIT 2
) AS sub
ORDER BY State_ID 
LIMIT 1;