How to group SQL in MYSQL
This article is about how SQL is grouped in MYSQL. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.
The grouping statistics function is not provided in mysql, but the use of variables in mysql is very flexible, and variables can be used flexibly in sql, which brings great convenience to the way mysql implements grouping.
It is not difficult for mysql to implement the function of grouping statistics, and it is relatively easy to understand. For example, the data we provide is used to describe and query the word frequency of the keywords, and then according to the keyword
Type, grouping the top three data of the frequency of word occurrence in the group
CREATE TABLE `policy_keywords_ rel` (
`id`int (11) NOT NULL AUTO_INCREMENT COMMENT 'self-adding ID'
`content_ id`int (11) NOT NULL COMMENT 'article id'
`keyword_ id`int (11) NOT NULL COMMENT 'keyword id'
`cnt` int (11) NOT NULL COMMENT 'keyword frequency'
`n`varchar (10) DEFAULT NULL
`keyword` varchar (90) DEFAULT NULL COMMENT 'keyword name'
PRIMARY KEY (`content_ id`, `keyword_ id`)
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
INSERT INTO dwsurvey.policy_keywords_rel (id,content_id,keyword_id,cnt,n,keyword)
VALUES (3pyrine 1pc68860 pyrrine, 3pyrrine tetracycline, 'intellectual property')
INSERT INTO dwsurvey.policy_keywords_rel (id,content_id,keyword_id,cnt,n,keyword)
VALUES (13pyrrine 1 49258 pyrrine 5 pyrrynchrine 'scientific and technological innovation')
INSERT INTO dwsurvey.policy_keywords_rel (id,content_id,keyword_id,cnt,n,keyword)
VALUES (1meme, 1pyrm, 44177, pyrrine, 19pyrum, etc.)
INSERT INTO dwsurvey.policy_keywords_rel (id,content_id,keyword_id,cnt,n,keyword)
VALUES (4pyrrine 1pr 42982pr 3recorder testamente 'industry standard')
INSERT INTO dwsurvey.policy_keywords_rel (id,content_id,keyword_id,cnt,n,keyword)
VALUES (10, 1, 7, 4, 5, 7, 4, 5, 6, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 5, 1, 1, 1, 7, 4, 5, 7, 4, 7, 7, 4, 5, 7, 4, 7, 4, 7, 4, 7, 4, 7, 4, 4,
Select * from policy_keywords_rel
The ranking of word frequency data in the group can be queried from the following sql, and the ranking of data with the same word frequency is the same.
SELECT
T2.*
FROM
(
SELECT
T.*
, CASE
WHEN @ MID = N and @ TEMP_SCNT! = SCNT THEN @ ROW: = @ ROW + 1
WHEN @ MID = N and @ TEMP_SCNT = SCNT THEN @ ROW: = @ ROW
ELSE @ ROW: = 1
END ROWNUM
, @ MID: = N MID
, @ TEMP_SCNT: = SCNT
FROM
(
SELECT
KEYWORD_ID
KEYWORD
SUM (CNT) as SCNT
N
FROM
POLICY_KEYWORDS_REL
GROUP BY
KEYWORD_ID
N
ORDER BY
N
SUM (CNT) DESC
) AS T
) AS T2
WHERE
T2.ROWNUM
