MySQL·Ö×éÈ»ºóȡÿ¸ö·Ö×éÖа´ÕÕijЩ×Ö¶ÎÅÅÐòµÄtopNÌõÊý¾Ý

MySQL·Ö×éÈ»ºóȡÿ¸ö·Ö×éÖа´ÕÕijЩ×Ö¶ÎÅÅÐòµÄtopNÌõÊý¾Ý

MySQL·Ö×éÈ»ºóȡÿ¸ö·Ö×éÖа´ÕÕijЩ×Ö¶ÎÅÅÐòµÄtopNÌõÊý¾Ý

½¨±í

CREATE TABLE `t` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `itime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO  t(`a`, `b`, `c`, `itime`) VALUES ('1', '1', '1', '2014-12-04 19:07:01');
INSERT INTO  t(`a`, `b`, `c`, `itime`) VALUES ('1', '1', '2', '2014-12-04 19:07:02');
INSERT INTO t (`a`, `b`, `c`, `itime`) VALUES ('1', '1', '3', '2014-12-04 19:07:03');
INSERT INTO t (`a`, `b`, `c`, `itime`) VALUES ('1', '1', '4', '2014-12-04 19:07:04');
INSERT INTO  t(`a`, `b`, `c`, `itime`) VALUES ('1', '2', '1', '2014-12-04 19:07:01');
INSERT INTO t (`a`, `b`, `c`, `itime`) VALUES ('1', '2', '2', '2014-12-04 19:07:02');
INSERT INTO  t(`a`, `b`, `c`, `itime`) VALUES ('1', '2', '3', '2014-12-04 19:07:03');
INSERT INTO t (`a`, `b`, `c`, `itime`) VALUES ('1', '2', '4', '2014-12-04 19:07:04');
INSERT INTO  t(`a`, `b`, `c`, `itime`) VALUES ('1', '2', '5', '2014-12-04 19:07:05');
INSERT INTO t (`a`, `b`, `c`, `itime`) VALUES ('1', '2', '6', '2014-12-04 19:07:06');

°´ÕÕa,b·Ö×飬²¢ÇÒ°´ÕÕitime×ֶε¹ÐðÅÅÁУ¬È¡Ã¿×éµÄtop3

SELECT
	t.a,
  	t.b,
	substring_index(
		group_concat(
			IFNULL(t.c,0)
			ORDER BY
				t.itime DESC
		),
		",",
		3
	) c,
	substring_index(
		group_concat(
			t.itime
			ORDER BY
				t.itime DESC
		),
		",",
		3
	) time
FROM
	t t
GROUP BY
	t.a ,t.b;

×¢Ò⣺

Ò»¡¢´Ë·½·¨ÐèÒªÉϲãÓ¦ÓÃÔÙ×öÒ»´Î´¦Àí£ºÉϲãÓ¦ÓÃÈ¡³ö½á¹û¼¯ÒԺ󣬽«Êý¾Ý°´ÕÕ¶ººÅ£¬ÔÙÇзֳÉtZ†·Ÿ"http://www.Bkjia.com/kf/ware/vc/" target="_blank" class="keylink">vcE633cr9vt2jrKOo16LS4qOs09C/ycTc09DQqdfpw7vT0HRvcE633cr9vt2jqTwvcD4KPHA+tv6hoklGTlVMTMXQts+63NbY0qqjrLfx1PJNeVNRTLvhvavPwtK7zPXK/b7dt8W9+MiloaPI57n7Y8HQzqpOVUxMLNTyyejWw9K7uPbErMjPJiMyMDU0MDvOqjCjrLfA1rm9q7Xay8TM9cr9vt0oMSm3xb34yKWhozwvcD4KCjxwPiAgICAgICDPws28ysfDu9PQSUZOVUxMtcS07c7ztPCwuKO6PC9wPgo8cD48L3A+CjxwPjxpbWcgc3JjPQ=="" alt="\">

PS£ºÈç¹û²»ÊÇÍò²»µÃÒÑ£¬ÎÞ·¨ÊµÏִ˹¦ÄÜ¡£²»½¨ÒéÔÚMySQLÖÐʹÓÃÈç´Ë¸´ÔÓµÄSQLÓï¾ä¡£

Èç¹ûÄãÓиüºÃµÄд·¨£¬Çë»Ø¸´Ò»Ï£¬ÈÃÎÒÖªµÀ£¬Ð»Ð»