select name,
sum(if(subject='语文',grade,0) as 语文, sum(if(subject='数学',grade,0) as 数学,
sum(if(subject='英语',grade,0) as 英语
from scores
1、创建三张测试表,
create table pw_memberinfo(uid int, name varchar(20))
create table pw_members(companyid int, uid int)
create table pw_memberdata(uid int, deposit int, ddeposit int, money int)
2、三张表,分别插入测试数据,
insert into pw_members values(1, 1)
insert into pw_members values(1, 2)
insert into pw_members values(1, 3)
insert into pw_memberinfo values(1, 'name_1')
insert into pw_memberinfo values(2, 'name_2')
insert into pw_memberinfo values(3, 'name_3')
insert into pw_memberdata values(1,30,50,150)
insert into pw_memberdata values(2,77,50,12)
insert into pw_memberdata values(3,44,50,82)
3、查看pw_memberdata表中的记录,select * from pw_memberdata t,
4、编写sql语句,
select * from (SELECT i.uid, sum(deposit+ddeposit+money) as allmoney
FROM pw_memberinfo i
LEFT JOIN pw_members m ON m.uid=i.uid
LEFT JOIN pw_memberdata d ON i.uid=d.uid
group by i.uid
) t where allmoney>200
您好,单个select语句实现MySQL查询统计次数的方法用处在哪里呢?用处太多了,比如一个成绩单,你要查询及格得人数与不及格的人数,怎么一次查询出来?MySQL查询统计次数简单的语句肯定是这样了:
select a.name,count_neg,count_plus from
(select count(id) as count_plus,name from score2 where score >=60 group by name) a,
(select count(id) as count_neg,name from score2 where score <=60 group by name) b
where a.name=b.name
即必须至少用2个语句。
今天刚好碰到发现mysql支持if,那就创造性的用if来实现吧:
select name, sum(if(score>=60,1,0)),sum(if(score<60,1,0)) from score2 group by name
单个select语句实现MySQL查询统计次数的方法简单吧。
原理就是大于60,就赋值为1,那么sum就是计数了。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)