MySQL分组排序_MySQL
Jun 01, 2016 pm 12:59 PM一、问题
产品表
有平台id 产品名称
id ptid name
1 100 产品1
2 100 产品2
3 100 产品3
4 100 产品4
5 100 产品5
6 100 产品6
7 101 产品7
8 101 产品8
9 101 产品9
10 101 产品10
11 101 产品11
如何根据某个平台id 只取这个平台的3条数据(顺序可以随机)
id ptid name
1 100 产品1
2 100 产品2
3 100 产品3
7 101 产品7
8 101 产品8
9 101 产品9
二、建表
create table p_a( id int, ptid varchar(20), name varchar(20) ) insert into p_a(id,ptid,name) values(1,'100','产品1'); insert into p_a(id,ptid,name) values(2,'100','产品2'); insert into p_a(id,ptid,name) values(3,'100','产品3'); insert into p_a(id,ptid,name) values(4,'100','产品4'); insert into p_a(id,ptid,name) values(5,'100','产品5'); insert into p_a(id,ptid,name) values(6,'100','产品6'); insert into p_a(id,ptid,name) values(7,'101','产品7'); insert into p_a(id,ptid,name) values(8,'101','产品8'); insert into p_a(id,ptid,name) values(9,'101','产品9'); insert into p_a(id,ptid,name) values(10,'101','产品10'); insert into p_a(id,ptid,name) values(11,'101','产品11');
三、sql
select * from ( select a.*, case when @oldptid=ptid then @lagfield:=@lagfield+1 else @lagfield:=1 end rn, @oldptid:=ptid from p_a a,(select @lagfield:=0,@oldptid:='') r ) a where rn<=3
四、总结
MySQL根据ptid分组,分别对分组内进行标注序号,然后根据序号取出各分组内的记录

Hot Article

Hot tools Tags

Hot Article

Hot Article Tags

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

Reduce the use of MySQL memory in Docker

How do you alter a table in MySQL using the ALTER TABLE statement?

How to solve the problem of mysql cannot open shared library

What is SQLite? Comprehensive overview

Run MySQl in Linux (with/without podman container with phpmyadmin)

Running multiple MySQL versions on MacOS: A step-by-step guide

How do I secure MySQL against common vulnerabilities (SQL injection, brute-force attacks)?

What are some popular MySQL GUI tools (e.g., MySQL Workbench, phpMyAdmin)?
