— mysql分組排序取前n條記錄的最簡潔的單條sql。
use test;
drop table if exists test;
CREATE?TABLE?test?( ??id?INT?PRIMARY?KEY, ??cid?INT, ??author?VARCHAR(30) )?ENGINE=MYISAM;
INSERT?INTO?test?VALUES?? (1,1,'test1'), (2,1,'test1'), (3,1,'test2'), (4,1,'test2'), (5,1,'test2'), (6,1,'test3'), (7,1,'test3'), (8,1,'test3'), (9,1,'test3'), (10,2,'test11'), (11,2,'test11'), (12,2,'test22'), (13,2,'test22'), (14,2,'test22'), (15,2,'test33'), (16,2,'test33'), (17,2,'test33'), (18,2,'test33');
INSERT INTO test VALUES ?(200,200,’200test_nagios’);
select?*?FROM?(SELECT?cid,author,COUNT(*)?AS?number?FROM?test?GROUP?BY?cid,author)?a? WHERE?? N >( ????SELECT?COUNT(*)? ????FROM?(SELECT?cid,author,COUNT(*)?AS?number?FROM?test?GROUP?BY?cid,author)?b ????WHERE?a.cid=b.cid?AND?a.number<b.number><p>結果如下：</p> <pre class="sql">mysql>?SELECT?*?FROM?(SELECT?cid,author,COUNT(*)?AS?number?FROM?test?GROUP?BY?cid,author)?a? ????->?WHERE?? ????->?3>( ????->?????SELECT?COUNT(*)? ????->?????FROM?(SELECT?cid,author,COUNT(*)?AS?number?FROM?test?GROUP?BY?cid,author)?b ????->?????WHERE?a.cid=b.cid?AND?a.number<b.number>?)ORDER?BY?cid,number?DESC; +------+----------------+--------+ |?cid??|?author?????????|?number?| +------+----------------+--------+ |????1?|?test3??????????|??????4?| |????1?|?test2??????????|??????3?| |????1?|?test1??????????|??????2?| |????2?|?test33?????????|??????4?| |????2?|?test22?????????|??????3?| |????2?|?test11?????????|??????2?| |??200?|?200test_nagios?|??????1?| +------+----------------+--------+ 7?rows?in?set?(0.00?sec)</b.number>
N就是取分組之后的最前面幾個判斷，N=3就是取前3個
— 生成自動數字序列
SET?@ROW=0; SELECT?a.*,(@ROW:=@ROW?+1)Rank FROM?test?a;
?以上就是MySQL分組排序取前N條記錄 以及 生成自動數字序列 的SQL的內容，更多相關內容請關注PHP中文網（www.php.cn）！
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
