Home Database Mysql Tutorial MySQL分组排序_MySQL

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

三、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
Copy after login

 

四、总结

MySQL根据ptid分组,分别对分组内进行标注序号,然后根据序号取出各分组内的记录

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

Hot Article Tags

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Reduce the use of MySQL memory in Docker Reduce the use of MySQL memory in Docker Mar 04, 2025 pm 03:52 PM

Reduce the use of MySQL memory in Docker

How do you alter a table in MySQL using the ALTER TABLE statement? How do you alter a table in MySQL using the ALTER TABLE statement? Mar 19, 2025 pm 03:51 PM

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

How to solve the problem of mysql cannot open shared library How to solve the problem of mysql cannot open shared library Mar 04, 2025 pm 04:01 PM

How to solve the problem of mysql cannot open shared library

What is SQLite? Comprehensive overview What is SQLite? Comprehensive overview Mar 04, 2025 pm 03:55 PM

What is SQLite? Comprehensive overview

Run MySQl in Linux (with/without podman container with phpmyadmin) Run MySQl in Linux (with/without podman container with phpmyadmin) Mar 04, 2025 pm 03:54 PM

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

Running multiple MySQL versions on MacOS: A step-by-step guide Running multiple MySQL versions on MacOS: A step-by-step guide Mar 04, 2025 pm 03:49 PM

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

How do I secure MySQL against common vulnerabilities (SQL injection, brute-force attacks)? How do I secure MySQL against common vulnerabilities (SQL injection, brute-force attacks)? Mar 18, 2025 pm 12:00 PM

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)? What are some popular MySQL GUI tools (e.g., MySQL Workbench, phpMyAdmin)? Mar 21, 2025 pm 06:28 PM

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

See all articles