mysql 每组记录前n条_MySQL取每组的前N条记录

￡神魔★判官ぃ 2022-11-01 01:51 225阅读 0赞

mysql 分组后取每组前N条数据

与oracle的rownumber() over(partition by xxx  order by xxx )语句类似，即：对表分组后排序

CREATE TABLE\`mygoods\` (

\`goods\_id\`int(11) unsigned NOT NULLAUTO\_INCREMENT,

\`cat\_id\`int(11) NOT NULL DEFAULT '0',

\`price\`tinyint(3) NOT NULL DEFAULT '0',

\`status\`tinyint(3) DEFAULT '1',PRIMARY KEY(\`goods\_id\`),KEY\`icatid\` (\`cat\_id\`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO \`mygoods\` VALUES (1, 101, 90, 0);INSERT INTO \`mygoods\` VALUES (2, 101, 99, 1);INSERT INTO \`mygoods\` VALUES (3, 102, 98, 0);INSERT INTO \`mygoods\` VALUES (4, 103, 96, 0);INSERT INTO \`mygoods\` VALUES (5, 102, 95, 0);INSERT INTO \`mygoods\` VALUES (6, 102, 94, 1);INSERT INTO \`mygoods\` VALUES (7, 102, 93, 1);INSERT INTO \`mygoods\` VALUES (8, 103, 99, 1);INSERT INTO \`mygoods\` VALUES (9, 103, 98, 1);INSERT INTO \`mygoods\` VALUES (10, 103, 97, 1);INSERT INTO \`mygoods\` VALUES (11, 104, 96, 1);INSERT INTO \`mygoods\` VALUES (12, 104, 95, 1);INSERT INTO \`mygoods\` VALUES (13, 104, 94, 1);INSERT INTO \`mygoods\` VALUES (15, 101, 92, 1);INSERT INTO \`mygoods\` VALUES (16, 101, 93, 1);INSERT INTO \`mygoods\` VALUES (17, 101, 94, 0);INSERT INTO \`mygoods\` VALUES (18, 102, 99, 1);INSERT INTO \`mygoods\` VALUES (19, 105, 85, 1);INSERT INTO \`mygoods\` VALUES (20, 105, 89, 0);INSERT INTO \`mygoods\` VALUES (21, 105, 99, 1);

说明：

表mygoods为商品表，cat\_id为分类id，goods\_id为商品id，status为商品当前的状态位(1:有效，0:无效)。

需求：每个分类下，找出两个价格最高的有效的商品。

1.每个分类找出价格最高的两个商品

mysql> select a.\*

\->from mygoods a-> where (select count(\*)->from mygoods-> where cat\_id = a.cat\_id and price > a.price ) <2

\-> order by a.cat\_id,a.price desc;

SQL解析：

cat\_id = a.cat\_id  --是为了确定分组字段

price > a.price  --确定排序条件，由于是大于，则最大出现0次，第二大的出现1次，所以count(\*)<2

\+----------+--------+-------+--------+

\+----------+--------+-------+--------+

| 2 | 101 | 99 | 1 |

| 17 | 101 | 94 | 0 |

| 18 | 102 | 99 | 1 |

| 3 | 102 | 98 | 0 |

| 8 | 103 | 99 | 1 |

| 9 | 103 | 98 | 1 |

| 11 | 104 | 96 | 1 |

| 12 | 104 | 95 | 1 |

| 21 | 105 | 99 | 1 |

| 20 | 105 | 89 | 0 |

\+----------+--------+-------+--------+

10 rows in set (0.00 sec)

2.每个分类找出价格最高的有效的两个商品(正确)

mysql> select a.\*

\->from mygoods a-> where (select count(\*) from mygoods-> where cat\_id = a.cat\_id and price > a.price and status=1 ) <2

\-> and status=1

\-> order by a.cat\_id,a.price desc ;

\+----------+--------+-------+--------+

\+----------+--------+-------+--------+

| 2 | 101 | 99 | 1 |

| 16 | 101 | 93 | 1 |

| 18 | 102 | 99 | 1 |

| 6 | 102 | 94 | 1 |

| 8 | 103 | 99 | 1 |

| 9 | 103 | 98 | 1 |

| 11 | 104 | 96 | 1 |

| 12 | 104 | 95 | 1 |

| 21 | 105 | 99 | 1 |

| 19 | 105 | 85 | 1 |

\+----------+--------+-------+--------+

10 rows in set (0.00 sec)

3.每个分类找出价格最高的有效的两个商品(正确)

mysql> select a.\*

\->from mygoods a-> left joinmygoods b-> on a.cat\_id = b.cat\_id and a.price < b.price and b.status=1

\-> where a.status=1

\->group by a.goods\_id,a.cat\_id,a.price-> having count(b.goods\_id) < 2

\-> order by a.cat\_id,a.price desc;

\+----------+--------+-------+--------+

\+----------+--------+-------+--------+

| 2 | 101 | 99 | 1 |

| 16 | 101 | 93 | 1 |

| 18 | 102 | 99 | 1 |

| 6 | 102 | 94 | 1 |

| 8 | 103 | 99 | 1 |

| 9 | 103 | 98 | 1 |

| 11 | 104 | 96 | 1 |

| 12 | 104 | 95 | 1 |

| 21 | 105 | 99 | 1 |

| 19 | 105 | 85 | 1 |

\+----------+--------+-------+--------+

10 rows in set (0.00 sec)

4.每个分类找出价格最高的有效的两个商品(错误)

mysql> select a.\*

\->from mygoods a-> where (select count(\*) from mygoods-> where cat\_id = a.cat\_id and price > a.price ) <2 and status=1

\-> order by a.cat\_id,a.price desc;

\+----------+--------+-------+--------+

\+----------+--------+-------+--------+

| 2 | 101 | 99 | 1 |

| 18 | 102 | 99 | 1 |

| 8 | 103 | 99 | 1 |

| 9 | 103 | 98 | 1 |

| 11 | 104 | 96 | 1 |

| 12 | 104 | 95 | 1 |

| 21 | 105 | 99 | 1 |

\+----------+--------+-------+--------+

7 rows in set (0.00 sec)

由上可知，如果需要增加条件的话，需要在两处增加条件。

可以将每个分组下的goods\_id合并。

mysql> select cat\_id,GROUP\_CONCAT(goods\_id) from mygoods group by cat\_id;

\+--------+------------------------+

| cat\_id | GROUP\_CONCAT(goods\_id) |

\+--------+------------------------+

| 101 | 1,2,15,16,17 |

| 102 | 3,5,6,7,18 |

| 103 | 4,8,9,10 |

| 104 | 11,12,13 |

| 105 | 19,20,21 |

\+--------+------------------------+

5 rows in set (0.00 sec)

https://blog.csdn.net/come\_on\_air/article/details/72902592

MySQL取每组的前N条记录：使用自连接的方式

一、对分组的记录取前N条记录：例子：取前 2条最大(小)的记录

1.用子查询： SELECT

FROMright2 aWHERE

2 >(SELECT

COUNT(\*)FROMright2 bWHEREb.id=a.idAND b.account >a.account

)ORDER BYa.id,

a.accountDESC 2.用exists半连接： SELECT

FROMright2 aWHERE

EXISTS(SELECT

COUNT(\*)FROMright2 bWHEREb.id=a.idAND a.account

COUNT(\*) < 2)ORDER BYa.id,

a.accountDESC 同理可以取组内最小的N条记录： SELECT

FROMright2 aWHERE

2 >(SELECT

COUNT(\*)FROMright2 bWHEREb.id=a.idAND b.account

)ORDER BYa.id,

a.accountDESC 用exists： SELECT

FROMright2 aWHERE

EXISTS(SELECT

COUNT(\*)FROMright2 bWHEREb.id=a.idAND a.account >b.accountHAVING

COUNT(\*) < 2)ORDER BYa.id,

a.accountDESC SQLServer支持top - N： SELECTa.\*

FROMtb aWHEREval=(SELECT

top 3valFROMtbWHERENAME=a. NAME

)ORDER BYa. NAME

如果取每组的最大(小)一条记录我常用：

SELECTid,

valFROMt bINNER JOIN(SELECT

FROMt aWHERE

ORDER BYvalDESC) aON a.id =b.idGROUP BYa.idORDER BYid;

二.实例：取每组最大的前 N条

CREATE TABLEt2 (

idINT PRIMARY KEY,

gidCHAR,

col1INT,

col2INT) ENGINE=INNODB;INSERT INTOt2VALUES(1, 'A', 31, 6),

(2, 'B', 25, 83),

(3, 'C', 76, 21),

(4, 'D', 63, 56),

(5, 'E', 3, 17),

(6, 'A', 29, 97),

(7, 'B', 88, 63),

(8, 'C', 16, 22),

(9, 'D', 25, 43),

(10, 'E', 45, 28),

(11, 'A', 2, 78),

(12, 'B', 30, 79),

(13, 'C', 96, 73),

(14, 'D', 37, 40),

(15, 'E', 14, 86),

(16, 'A', 32, 67),

(17, 'B', 84, 38),

(18, 'C', 27, 9),

(19, 'D', 31, 21),

(20, 'E', 80, 63),

(21, 'A', 89, 9),

(22, 'B', 15, 22),

(23, 'C', 46, 84),

(24, 'D', 54, 79),

(25, 'E', 85, 64),

(26, 'A', 87, 13),

(27, 'B', 40, 45),

(28, 'C', 34, 90),

(29, 'D', 63, 8),

(30, 'E', 66, 40),

(31, 'A', 83, 49),

(32, 'B', 4, 90),

(33, 'C', 81, 7),

(34, 'D', 11, 12),

(35, 'E', 85, 10),

(36, 'A', 39, 75),

(37, 'B', 22, 39),

(38, 'C', 76, 67),

(39, 'D', 20, 11),

(40, 'E', 81, 36);

取每组gid 最大的前N条记录：使用自连接或则半连接：

\*N=1时：

自连接：降序排好后group by取每组最大的一条。

SELECT\*FROM

(

SELECT\*FROM

ORDER BY

col2 DESC

) AS a

GROUP BY

gid

ORDER BY

gid;

半连接方式：找不到比最大值还大的。

SELECT\*FROM

t2 a

WHERE

NOT EXISTS (

SELECT1FROM

t2 b

WHERE

b.gid=a.gid

AND b.col2>a.col2

)

ORDER BY

a.gid;

\*N=3时：

自连接：

SELECT\*FROM

t2 a

WHERE3 >(

SELECT

count(\*)

FROM

WHERE

gid=a.gid

AND col2>a.col2

)

ORDER BY

a.gid,

a.col2 DESC;

半连接：

SELECT\*FROM

t2 a

WHERE

EXISTS (

SELECT

count(\*)

FROM

t2 b

WHERE

b.gid=a.gid

AND a.col2

HAVING

(count(\*)) < 3)

ORDER BY

a.gid,

a.col2 DESC

Oracle取每组的前N条记录:可以使用分析函数，hive中也能使用

SELECT \*FROM(

SELECT z.type , z.code ,ROW\_NUMBER()

OVER(PARTITION BY z.type ORDER BY z.code desc) AS code\_id

FROM group\_info z

)

WHERE code\_id<4; \#取每组最大的前四条记录

https://blog.csdn.net/kuodannie1668/article/details/79756964

以上就是MySQL取每组的前N条记录的全部内容。

mysql 每组记录前n条_MySQL取每组的前N条记录

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 MySQL 选择前n个记录

相关 mysql 每组记录前n条_MySQL取每组的前N条记录

相关 sql分组后查找每组的前几条记录语句

相关 mysql获取第n条记录

相关 oracle查询:取出每组中的第一条记录

相关 Mongo分组取每组最大的一条记录

相关 pandas groupby 分组取每组的前几行记录

相关 SQL分组取每组前一(或几)条记录(排名)

相关 SQL查询之分组后取每组的前N条记录

相关 mysql分组取前N条数据，mysql排名