从mysql表中获得第二位

Question

我有一个有四个字段的桌子，如下所示

(UID是用户ID)

ID UID MUSIC DATE 
1   0   a   2013-10-20
2   0   a   2013-10-21
3   0   a   2013-10-22
4   0   a   2013-10-24
5   0   b   2013-10-11
8   0   b   2013-10-15
10  0   c   2013-10-26
9   0   c   2013-10-25
7   0   c   2013-10-20
6   0   c   2013-10-18
11  0   d   2013-10-10

如何使用MySQL查询从上面的表中检索所有第二次最高日期？

预期结果：

ID UID MUSIC DATE 
3   0   a   2013-10-22
5   0   b   2013-10-11
9   0   c   2013-10-25

或

ID UID MUSIC DATE 
3   0   a   2013-10-22
5   0   b   2013-10-11
9   0   c   2013-10-25
11  0   d   2013-10-10

Answer 1

好吧，我想我有答案了。请检查这个：

SELECT tbl.ID, tbl.UID, tbl.MUSIC, tbl.DATE
FROM tbl
INNER JOIN
(
SELECT MUSIC,
       Substring_index(Substring_index(gdate, ',', 2), ',', -1) AS sec_date
FROM   (SELECT MUSIC               ,
               GROUP_CONCAT(DATE order by DATE desc separator ",") AS gdate
        FROM   tbl
        GROUP  BY MUSIC) t1
) AS tbl2
  ON tbl.MUSIC=tbl2.MUSIC
    AND tbl.DATE=tbl2.sec_date

首先，我按照desc的顺序创建了GROUP_CONCAT on DATE，这样我就可以使用Substring_index获得第二个DATE，当然，还可以按音乐对所有内容进行分组，因此日期是为各自的音乐类别分组的。然后，我编写了实际的查询以获得结果，并使用连接到派生表，以确保为特定的MUSIC和DATE获得了正确的行。

这是SQLFiddle

更新

如果希望通过UID进行进一步筛选，只需将WHERE添加到内部查询，如下所示：

SELECT tbl.ID, tbl.UID, tbl.MUSIC, tbl.DATE
FROM tbl
INNER JOIN
(
SELECT MUSIC,
       Substring_index(Substring_index(gdate, ',', 2), ',', -1) AS sec_date
FROM   (SELECT MUSIC               ,
               GROUP_CONCAT(DATE order by DATE desc separator ",") AS gdate
        FROM   tbl
        WHERE UID=1 -- add filter here
        GROUP  BY MUSIC) t1
) AS tbl2
  ON tbl.MUSIC=tbl2.MUSIC
    AND tbl.DATE=tbl2.sec_date

和更新的SQLFiddle

Answer 2

来自这个SQLFiddle：http://sqlfiddle.com/#!2/fd47a2/7

SELECT tbl.UID, tbl.MUSIC, MAX(tbl.DATE)
FROM tbl
LEFT JOIN (
    SELECT UID, MUSIC, MAX(DATE) as DATE
    FROM tbl
    GROUP BY UID, MUSIC) AS tbl2
  ON tbl.UID = tbl2.UID
    AND tbl.MUSIC = tbl2.MUSIC
    AND tbl.DATE = tbl2.DATE
WHERE tbl2.UID IS NULL
GROUP BY tbl.UID, tbl.MUSIC

但是，它没有ID，如果需要，那么IMO，您需要使用上面的查询作为另一个连接到原始表以获得ID。

Answer 3

您可以尝试以下查询-

SELECT ID, UID, MUSIC, MAX(DATE) FROM TableName
WHERE DATE NOT IN (SELECT MAX(DATE) FROM TableName )

我认为这就像是找到第二高工资的员工-- 示例