Shulou(Shulou.com)05/31 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.