mysql数据查询，按照分组

武小凡 2018-01-18 01:45:28

如图，上面的一张表怎么按照年龄段进行分组？
...全文
746 13 打赏 收藏 转发到动态 举报
写回复
用AI写文章
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
吉普赛的歌 2018-01-19
  • 打赏
  • 举报
 回复
引用 11 楼 wuxiao_fan 的回复:
可不可以是这个样子的。每一条数据都有年龄段。
drop table if exists userInfo;
drop table if exists ageSeg;
create table userInfo(
	id int PRIMARY key,
	`name` varchar(20),
	`age` int
);
create table ageSeg(
	seg varchar(10)
);
insert into userInfo values(1,'啊一',18);
insert into userInfo values(2,'啊二',30);
insert into userInfo values(3,'啊三',50);
insert into userInfo values(4,'啊四',28);
insert into userInfo values(5,'啊五',36);
insert into userInfo values(6,'啊六',47);
insert into userInfo values(7,'啊七',31);
insert into userInfo values(8,'啊八',24);
insert into ageSeg values('15-20');
insert into ageSeg values('21-24');
insert into ageSeg values('25-30');
insert into ageSeg values('30-34');
insert into ageSeg values('35-40');
insert into ageSeg values('41-44');
insert into ageSeg values('45-50');

SELECT *,
(
	SELECT COUNT(1)
	FROM   userInfo b
	WHERE  b.age BETWEEN LEFT(a.seg, 2) AND RIGHT(a.seg, 2)
) AS cnt 
,(
	select GROUP_CONCAT(b.`name`)
	from userInfo b
	WHERE  b.age BETWEEN LEFT(a.seg, 2) AND RIGHT(a.seg, 2)
) as unames
,(
	select GROUP_CONCAT(b.age)
	from userInfo b
	WHERE  b.age BETWEEN LEFT(a.seg, 2) AND RIGHT(a.seg, 2)
) as uAges
FROM ageSeg AS a
WHERE exists(
	SELECT 1
	FROM   userInfo b
	WHERE  b.age BETWEEN LEFT(a.seg, 2) AND RIGHT(a.seg, 2)
)
;
/*
+-------+-----+-----------+-------+
| seg   | cnt | unames    | uAges |
+-------+-----+-----------+-------+
| 15-20 |   1 | 啊一      | 18    |
| 21-24 |   1 | 啊八      | 24    |
| 25-30 |   2 | 啊二,啊四 | 30,28 |
| 30-34 |   2 | 啊二,啊七 | 30,31 |
| 35-40 |   1 | 啊五      | 36    |
| 45-50 |   2 | 啊三,啊六 | 50,47 |
+-------+-----+-----------+-------+
*/
RINK_1 2018-01-18
  • 打赏
  • 举报
 回复
引用 11 楼 wuxiao_fan 的回复:
[quote=引用 8 楼 yenange 的回复:] 
drop table if exists userInfo;
drop table if exists ageSeg;
create table userInfo(
	id int PRIMARY key,
	`name` varchar(20),
	`age` int
);
create table ageSeg(
	seg varchar(10)
);
insert into userInfo values(1,'啊一',18);
insert into userInfo values(2,'啊二',30);
insert into userInfo values(3,'啊三',50);
insert into userInfo values(4,'啊四',28);
insert into userInfo values(5,'啊五',36);
insert into userInfo values(6,'啊六',47);
insert into userInfo values(7,'啊七',31);
insert into userInfo values(8,'啊八',24);
insert into ageSeg values('15-20');
insert into ageSeg values('21-24');
insert into ageSeg values('25-30');
insert into ageSeg values('30-34');
insert into ageSeg values('35-40');
insert into ageSeg values('41-44');
insert into ageSeg values('45-50');

SELECT *,
(
	SELECT COUNT(1)
	FROM   userInfo b
	WHERE  b.age BETWEEN LEFT(a.seg, 2) AND RIGHT(a.seg, 2)
) AS cnt 
,(
	select GROUP_CONCAT(b.`name`)
	from userInfo b
	WHERE  b.age BETWEEN LEFT(a.seg, 2) AND RIGHT(a.seg, 2)
) as unames
,(
	select GROUP_CONCAT(b.age)
	from userInfo b
	WHERE  b.age BETWEEN LEFT(a.seg, 2) AND RIGHT(a.seg, 2)
) as uAges
FROM ageSeg AS a;
/*
+-------+-----+-----------+-------+
| seg   | cnt | unames    | uAges |
+-------+-----+-----------+-------+
| 15-20 |   1 | 啊一      | 18    |
| 21-24 |   1 | 啊八      | 24    |
| 25-30 |   2 | 啊二,啊四 | 30,28 |
| 30-34 |   2 | 啊二,啊七 | 30,31 |
| 35-40 |   1 | 啊五      | 36    |
| 41-44 |   0 | NULL      | NULL  |
| 45-50 |   2 | 啊三,啊六 | 50,47 |
+-------+-----+-----------+-------+
*/
可不可以是这个样子的。每一条数据都有年龄段。[/quote] 外面再套一层，把ID为空的去除掉。
武小凡 2018-01-18
  • 打赏
  • 举报
 回复
引用 8 楼 yenange 的回复:
drop table if exists userInfo;

drop table if exists ageSeg;

create table userInfo(

	id int PRIMARY key,

	`name` varchar(20),

	`age` int

);

create table ageSeg(

	seg varchar(10)

);

insert into userInfo values(1,'啊一',18);

insert into userInfo values(2,'啊二',30);

insert into userInfo values(3,'啊三',50);

insert into userInfo values(4,'啊四',28);

insert into userInfo values(5,'啊五',36);

insert into userInfo values(6,'啊六',47);

insert into userInfo values(7,'啊七',31);

insert into userInfo values(8,'啊八',24);

insert into ageSeg values('15-20');

insert into ageSeg values('21-24');

insert into ageSeg values('25-30');

insert into ageSeg values('30-34');

insert into ageSeg values('35-40');

insert into ageSeg values('41-44');

insert into ageSeg values('45-50');



SELECT *,

(

	SELECT COUNT(1)

	FROM   userInfo b

	WHERE  b.age BETWEEN LEFT(a.seg, 2) AND RIGHT(a.seg, 2)

) AS cnt 

,(

	select GROUP_CONCAT(b.`name`)

	from userInfo b

	WHERE  b.age BETWEEN LEFT(a.seg, 2) AND RIGHT(a.seg, 2)

) as unames

,(

	select GROUP_CONCAT(b.age)

	from userInfo b

	WHERE  b.age BETWEEN LEFT(a.seg, 2) AND RIGHT(a.seg, 2)

) as uAges

FROM ageSeg AS a;

/*

+-------+-----+-----------+-------+

| seg   | cnt | unames    | uAges |

+-------+-----+-----------+-------+

| 15-20 |   1 | 啊一      | 18    |

| 21-24 |   1 | 啊八      | 24    |

| 25-30 |   2 | 啊二,啊四 | 30,28 |

| 30-34 |   2 | 啊二,啊七 | 30,31 |

| 35-40 |   1 | 啊五      | 36    |

| 41-44 |   0 | NULL      | NULL  |

| 45-50 |   2 | 啊三,啊六 | 50,47 |

+-------+-----+-----------+-------+

*/

可不可以是这个样子的。每一条数据都有年龄段。
二月十六 2018-01-18
  • 打赏
  • 举报
 回复 
--测试数据

if not object_id(N'Tempdb..#T1') is null

    drop table #T1

Go

Create table #T1([name] int,[age] int)

Insert #T1

select 1,18 union all

select 2,30 union all

select 3,50 union all

select 4,28

GO

if not object_id(N'Tempdb..#T2') is null

    drop table #T2

Go

Create table #T2([start] int,[end] int)

Insert #T2

select 15,20 union all

select 21,24 union all

select 25,30 union all

select 30,50 union all

select 51,150

Go

--测试数据结束

SELECT  RTRIM([start])+'-'+RTRIM([END]) AS 年龄段 ,

        #T1.*

FROM    #T2

        LEFT JOIN #T1 ON age BETWEEN start AND [end]


武小凡 2018-01-18
  • 打赏
  • 举报
 回复
引用 6 楼 yenange 的回复:
drop table if exists userInfo;

drop table if exists ageSeg;

create table userInfo(

	id int PRIMARY key,

	`name` varchar(20),

	`age` int

);

create table ageSeg(

	seg varchar(10)

);

insert into userInfo values(1,'啊一',18);

insert into userInfo values(2,'啊二',30);

insert into userInfo values(3,'啊三',50);

insert into userInfo values(4,'啊四',28);

insert into userInfo values(5,'啊五',36);

insert into userInfo values(6,'啊六',47);

insert into userInfo values(7,'啊七',31);

insert into userInfo values(8,'啊八',24);

insert into ageSeg values('15-20');

insert into ageSeg values('21-24');

insert into ageSeg values('25-30');

insert into ageSeg values('30-34');

insert into ageSeg values('35-40');

insert into ageSeg values('41-44');

insert into ageSeg values('45-50');



SELECT *,

(

	SELECT COUNT(1)

	FROM   userInfo b

	WHERE  b.age BETWEEN LEFT(a.seg, 2) AND RIGHT(a.seg, 2)

) AS cnt 

FROM ageSeg AS a;


引用 5 楼 sinat_28984567 的回复:
[quote=引用 4 楼 wuxiao_fan 的回复:]
[quote=引用 3 楼 sinat_28984567 的回复:]
--测试数据

if not object_id(N'Tempdb..#T1') is null

	drop table #T1

Go

Create table #T1([name] int,[age] int)

Insert #T1

select 1,18 union all

select 2,30 union all

select 3,50 union all

select 4,28

GO

if not object_id(N'Tempdb..#T2') is null

	drop table #T2

Go

Create table #T2([start] int,[end] int)

Insert #T2

select 15,20 union all

select 21,24 union all

select 25,30 union all

select 30,100

Go

--测试数据结束

SELECT  [start] ,

        [END] ,

        COUNT(1) AS 个数

FROM    #T1

        JOIN #T2 ON age BETWEEN start AND [end]

		GROUP BY

		[start] ,

        [END]



分组显示年龄段，和每一个年龄段的姓名，年龄可以吗？[/quote]
想怎么显示？[/quote]

这个样子显示
吉普赛的歌 2018-01-18
  • 打赏
  • 举报
 回复 
drop table if exists userInfo;
drop table if exists ageSeg;
create table userInfo(
	id int PRIMARY key,
	`name` varchar(20),
	`age` int
);
create table ageSeg(
	seg varchar(10)
);
insert into userInfo values(1,'啊一',18);
insert into userInfo values(2,'啊二',30);
insert into userInfo values(3,'啊三',50);
insert into userInfo values(4,'啊四',28);
insert into userInfo values(5,'啊五',36);
insert into userInfo values(6,'啊六',47);
insert into userInfo values(7,'啊七',31);
insert into userInfo values(8,'啊八',24);
insert into ageSeg values('15-20');
insert into ageSeg values('21-24');
insert into ageSeg values('25-30');
insert into ageSeg values('30-34');
insert into ageSeg values('35-40');
insert into ageSeg values('41-44');
insert into ageSeg values('45-50');

SELECT *,
(
	SELECT COUNT(1)
	FROM   userInfo b
	WHERE  b.age BETWEEN LEFT(a.seg, 2) AND RIGHT(a.seg, 2)
) AS cnt 
,(
	select GROUP_CONCAT(b.`name`)
	from userInfo b
	WHERE  b.age BETWEEN LEFT(a.seg, 2) AND RIGHT(a.seg, 2)
) as unames
,(
	select GROUP_CONCAT(b.age)
	from userInfo b
	WHERE  b.age BETWEEN LEFT(a.seg, 2) AND RIGHT(a.seg, 2)
) as uAges
FROM ageSeg AS a;
/*
+-------+-----+-----------+-------+
| seg   | cnt | unames    | uAges |
+-------+-----+-----------+-------+
| 15-20 |   1 | 啊一      | 18    |
| 21-24 |   1 | 啊八      | 24    |
| 25-30 |   2 | 啊二,啊四 | 30,28 |
| 30-34 |   2 | 啊二,啊七 | 30,31 |
| 35-40 |   1 | 啊五      | 36    |
| 41-44 |   0 | NULL      | NULL  |
| 45-50 |   2 | 啊三,啊六 | 50,47 |
+-------+-----+-----------+-------+
*/
吉普赛的歌 2018-01-18
  • 打赏
  • 举报
 回复
吉普赛的歌 2018-01-18
  • 打赏
  • 举报
 回复 
drop table if exists userInfo;
drop table if exists ageSeg;
create table userInfo(
	id int PRIMARY key,
	`name` varchar(20),
	`age` int
);
create table ageSeg(
	seg varchar(10)
);
insert into userInfo values(1,'啊一',18);
insert into userInfo values(2,'啊二',30);
insert into userInfo values(3,'啊三',50);
insert into userInfo values(4,'啊四',28);
insert into userInfo values(5,'啊五',36);
insert into userInfo values(6,'啊六',47);
insert into userInfo values(7,'啊七',31);
insert into userInfo values(8,'啊八',24);
insert into ageSeg values('15-20');
insert into ageSeg values('21-24');
insert into ageSeg values('25-30');
insert into ageSeg values('30-34');
insert into ageSeg values('35-40');
insert into ageSeg values('41-44');
insert into ageSeg values('45-50');

SELECT *,
(
	SELECT COUNT(1)
	FROM   userInfo b
	WHERE  b.age BETWEEN LEFT(a.seg, 2) AND RIGHT(a.seg, 2)
) AS cnt 
FROM ageSeg AS a;
二月十六 2018-01-18
  • 打赏
  • 举报
 回复
引用 4 楼 wuxiao_fan 的回复:
[quote=引用 3 楼 sinat_28984567 的回复:] 
--测试数据
if not object_id(N'Tempdb..#T1') is null
	drop table #T1
Go
Create table #T1([name] int,[age] int)
Insert #T1
select 1,18 union all
select 2,30 union all
select 3,50 union all
select 4,28
GO
if not object_id(N'Tempdb..#T2') is null
	drop table #T2
Go
Create table #T2([start] int,[end] int)
Insert #T2
select 15,20 union all
select 21,24 union all
select 25,30 union all
select 30,100
Go
--测试数据结束
SELECT  [start] ,
        [END] ,
        COUNT(1) AS 个数
FROM    #T1
        JOIN #T2 ON age BETWEEN start AND [end]
		GROUP BY
		[start] ,
        [END]
分组显示年龄段，和每一个年龄段的姓名，年龄可以吗？[/quote] 想怎么显示？
武小凡 2018-01-18
  • 打赏
  • 举报
 回复
引用 3 楼 sinat_28984567 的回复:
--测试数据
if not object_id(N'Tempdb..#T1') is null
	drop table #T1
Go
Create table #T1([name] int,[age] int)
Insert #T1
select 1,18 union all
select 2,30 union all
select 3,50 union all
select 4,28
GO
if not object_id(N'Tempdb..#T2') is null
	drop table #T2
Go
Create table #T2([start] int,[end] int)
Insert #T2
select 15,20 union all
select 21,24 union all
select 25,30 union all
select 30,100
Go
--测试数据结束
SELECT  [start] ,
        [END] ,
        COUNT(1) AS 个数
FROM    #T1
        JOIN #T2 ON age BETWEEN start AND [end]
		GROUP BY
		[start] ,
        [END]
分组显示年龄段，和每一个年龄段的姓名，年龄可以吗？
二月十六 2018-01-18
  • 打赏
  • 举报
 回复 
--测试数据

if not object_id(N'Tempdb..#T1') is null

	drop table #T1

Go

Create table #T1([name] int,[age] int)

Insert #T1

select 1,18 union all

select 2,30 union all

select 3,50 union all

select 4,28

GO

if not object_id(N'Tempdb..#T2') is null

	drop table #T2

Go

Create table #T2([start] int,[end] int)

Insert #T2

select 15,20 union all

select 21,24 union all

select 25,30 union all

select 30,100

Go

--测试数据结束

SELECT  [start] ,

        [END] ,

        COUNT(1) AS 个数

FROM    #T1

        JOIN #T2 ON age BETWEEN start AND [end]

		GROUP BY

		[start] ,

        [END]


武小凡 2018-01-18
  • 打赏
  • 举报
 回复
引用 1 楼 sinat_28984567 的回复:
建立一个年龄段表，开始年龄，结束年龄，然后两个表关联分组查询
如果不建立表就不能进行查询了吗？
二月十六 2018-01-18
  • 打赏
  • 举报
 回复
建立一个年龄段表，开始年龄，结束年龄，然后两个表关联分组查询
MySQL实操极简入门
MySQL的快速使用入门。19节微课，不求全面，只为上手。帮助初学者，在最短的时间内上手使用MySQL。内容包括：1-客户端登录与退出MySQL2-操作数据库3-创建表4-主键5-主键自增6-非空7-修改表8-插入数据9-修改数据10-删除数据11-查询数据一12-查询数据二（条件查询）13-查询数据三（模糊查询）14-查询数据四（字段控制查询）15-查询数据五（排序）16-查询数据六（聚合函数）17-Limit18-分组查询19-having的使用
Mysql：分组查询
分组查询 1、分组查询是对数据按照某个或多个字段进行分组，在MYSQL中使用GROUP BY关键字对数据进行分组 2、GROUP BY关键字可以将查询结果按照某个字段或多个字段进行分组。字段中值相等的为一组 ⑴分组的核心是：在查询SQL中指定分组的列名，然后根据该列的值进行分组，值相等的为一组 3、分组查询的基本的语法格式如下： GROUP BY 字段名 [HAVING 条件表达式] 参数： 1、字段名：是指按照该字段的值进行分组(分组是所依据的列名称) 2、HAVING条件表达式：用来...
Mysql 将数据按照年月分组 统计
Mysql 将数据按照年月分组 统计
mysql千万级数据分组查询_mysql千万级数据查询
1. mysql 的数据查询 , 大小字段要分开 , 这个还是有必要的 , 除非一点就是你查询的都是索引内容而不是表内容 , 比如只查询 id 等等 2. 查询速度和索引有很大关系也就是索引的大小直接影响你的查询效果 , 但是查询条件一定要建立索引 , 这点上注意的是索引字1.mysql的数据查询,大小字段要分开,这个还是有必要的,除非一点就是你查询的都是索引内容而不是表内容,比如只查询id等等2...
mysql 分组查询（分组后取对应数据行）
mysql分组查询
community_281

590

社区成员

254,054

社区内容

发帖
与我相关
我的任务
社区描述
提出问题
其他 技术论坛（原bbs）
社区管理员
  • community_281
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章