Mysql怎么统计所有学生的文具数

Jessica丶 发布于 01/27 17:42
阅读 491
收藏 0

软件工程师和算法工程师速来!正在直播:如何使用英特尔oneAPI工具实现PyTorch 优化>>>

学生表:student(id,name)

铅笔表:pencil(id,stu_id,name)

书包表:bag(id,stu_id,name)

书本表:book(id,stu_id,name)

现在想统计每个学生的文具总数(包含铅笔、书包、书本)

例:学生表数据:(1,张三)(2,李四),铅笔表数据:(1,1,黑字笔),(2,1,红字笔)(3,2,蓝字笔),书包表数据:(1,1,双肩包),(2,2,单肩包),书本表数据:(1,1,英语书),(2,2,数学书)

需要一条sql查出来的数据是:(张三,4),(李四,3),即张三有4个文具,李四有3个文具

加载中
0
kis龍
kis龍
 
select s.name,sum(c.ct) 
from student s
  left join (
    select stu_id,count(*) ct from pencil
    union (select stu_id,count(*) ct from bag)
    union (select stu_id,count(*) ct from book)
    ) c

试试看,但是不建议这样做。做上复合索引可以提升一些性能,但是学生特别多的时候,建议用实体表存储统计结果来查询(或其他缓存查询结果)

kis龍
kis龍
楼下指出了错误,left join 里面的加上 group by stu_id 分组
0
自由PHP
自由PHP

可以用union联表后再通过join联student表,再进行分组求和;也可以先前置分组求和,这样临时表需要处理的数据量减少

0
sxgkwei
sxgkwei

这个简单,直接查主表学生表,然后,在 select 后面的显示的列上,查另外3个表形成3个列,3个列相加,表的 stu_id 等于当前数据的 id,使用count 统计总条数。就能直接一句 sql 查出来你需要的数据

0
F
Francesca
select s.name,sum(c.n) from (select stu_id,count(1) n from pencil GROUP BY stu_id union all select stu_id,count(1) n from book GROUP BY stu_id union all select stu_id,count(1) n from bag GROUP BY stu_id) c INNER join student s on s.id = c.stu_id GROUP BY s.id 仅供参考
0
ArchitectureMaster
ArchitectureMaster

1.楼很明显是有问题的。

1。在left join 里 直接去count总数,而没有group by的情况 下,只能出现一条记录。优先计算()括号里的内容。

2.把count聚合函数和普通列放在一起是会报错的,可以将要显示的列头分组,如按学生id分组统计个数这就求出每个学生id对应的笔数

select stu_id,count(*) ct 
from pencil 
group by stu_id

3.同理可求出每个学生有的书包数及书本数

--求每个学生的书包数
select stu_id,count(*) ct 
from bag
group by stu_id

--求每个学生的书本数
select stu_id,count(*) ct 
from book
group by stu_id

3.然后将这三个sql查出的结果集再用left join连接到一起,注意这里使用stu_id连接时有可能会有空值,因为有的学生没有书本,有的学生没有书包,有的学生没有笔。这三者是或的关系,所以还需要过滤掉为空值,即使用ifnull将空转为0。

最后要做的是将这三个SQL连在一起然后再right join右连接上student表就可以了。

SELECT
	s.NAME NAME,
	ifnull( p_count, 0 ) p_count,
	ifnull( bag_count, 0 ) bag_count,
	ifnull( book_count, 0 ) book_count,
	ifnull( p_count + bag_count + book_count, 0 ) total_count 
FROM
	( SELECT stu_id p_stu_id, count(*) p_count FROM pencil GROUP BY stu_id ) pc
	LEFT JOIN ( SELECT stu_id, count( 0 ) bag_count FROM bag GROUP BY stu_id ) bc ON pc.p_stu_id = bc.stu_id
	LEFT JOIN ( SELECT stu_id, count( 0 ) book_count FROM book GROUP BY stu_id ) boc ON pc.p_stu_id = boc.stu_id
	RIGHT JOIN student s ON pc.p_stu_id = s.id

 

0
邓草
邓草

楼上已经有正确答案

OSCHINA
登录后可查看更多优质内容
返回顶部
顶部
返回顶部
顶部