Получить все второе место из таблицы MySQL

Question

У меня есть таблица с четырьмя полями следующим образом,

(UID - это идентификатор пользователя)

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 Query?

Ожидаемый результат:

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

Прежде всего я создал GROUP_CONCAT на DATE, упорядоченный по desc, так что я могу использовать Substring_index чтобы получить второй DATEи, конечно, все сгруппированы по MUSIC, поэтому даты сгруппированы по соответствующей категории MUSIC. Затем я написал реальный запрос для получения результатов с соединением с производной таблицей, чтобы убедиться, что получаю правильную строку для этого конкретного 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/

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

Однако у него нет идентификатора, который, если вы хотите, то IMO, вам нужно использовать вышеупомянутый запрос как еще одно соединение с исходной таблицей, чтобы получить идентификатор.

Answer 3

Возможно, есть более элегантное решение, но вот один из способов...

 SELECT a.* 
   FROM
      ( SELECT x.*
          FROM my_music x 
          JOIN my_music y 
            ON y.uid = x.uid 
           AND y.music = x.music 
           AND y.date >= x.date 
         GROUP 
            BY id
        HAVING COUNT(*) <= 2
      ) a
   JOIN
      ( SELECT x.*
          FROM my_music x 
          JOIN my_music y 
            ON y.uid = x.uid 
           AND y.music = x.music 
           AND y.date >= x.date 
         GROUP 
            BY id
        HAVING COUNT(*) <= 2
      ) b
     ON b.uid = a.uid
    AND b.music = a.music
    AND b.date <= a.date
  GROUP 
     BY id
 HAVING COUNT(*) = 1;

Answer 4

Вы можете попробовать следующий запрос -

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

Я думаю, что это что-то вроде поиска второй по величине зарплаты работника - пример

Answer 5

Выберите id,music,[uid] из (выберите row_number() более ( PARTITION BY по порядку идентификаторов по [date] desc) как rn,id,music,[uid] из table_name) a, где rn = 2 - введите 2 u можно изменить на любое число, которое вы хотите

Answer 6

Попробуй это

Это SQL-запрос

SELECT * FROM 
(SELECT 
ROW_NUMBER() OVER (PARTITION BY Music ORDER BY Date DESC) NO,
*
FROM UrTable) AS T1 WHERE no = 2