Home > Database > Mysql Tutorial > body text

MySQL group sorting to get the first N records and generate automatic number sequence SQL

黄舟
Release: 2017-02-16 13:24:45
Original
1212 people have browsed it

--MySQL group sorting takes the simplest single SQL of the first N records.


USE test;


DROP TABLE IF EXISTS test;

CREATE TABLE test (
  id INT PRIMARY KEY,
  cid INT,
  author VARCHAR(30)
) ENGINE=MYISAM;
Copy after login
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');
Copy after login

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
)ORDER BY cid,number DESC;
Copy after login


The results are as follows:


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)
Copy after login



##N is to take the first few judgments after grouping, N=3 is to take the first 3




##--Generate automatic number sequence

SET @ROW=0;
SELECT a.*,(@ROW:=@ROW +1)Rank
FROM test a;
Copy after login
The above is the SQL content of MySQL group sorting, fetching the first N records and generating automatic number sequence. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!


source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template