首页新闻找找看学习计划

有关MySQL语句

0
悬赏园豆:20 [待解决问题]

有一张记录人员信息的表
字段:pf_education:记录学历
字段:createtime:记录年份
字段:id
....
通过这张表按照年份查询不同学历的人数,如:
年度 本科人数 大专人数
2012 20 40
2013 26 42
2014 30 35
2015 40 20

SQL语句应该怎么写?

sjklasd的主页 sjklasd | 初学一级 | 园豆:143
提问于:2019-02-26 16:50
< >
分享
所有回答(3)
1

如果仅查询某年的某些具体学历:

select count(1) pfEducation, sum(if(pf_education='学历值1',1,0)) pfEducation1 , sum(if(pf_education='学历值2',1,0)) pfEducation2 ,DATE_FORMAT(createtime,'%Y') createtime_Y from 表
GROUP BY DATE_FORMAT(createtime,'%Y')
order by createtime desc

如果查询的学历不固定具体学历:

wangqin_2018 | 园豆:196 (初学一级) | 2019-02-26 18:14
0

select a.年度,a.本科人数,b.大专人数 from

(select createtime as '年度',count(pf_education) as '本科人数' from EmployeeInfo with(nolock) where pf_education = '本科' group by createtime,pf_education) as a

inner join

(select createtime as '年度',count(pf_education) as '大专人数' from EmployeeInfo with(nolock) where pf_education = '大专' group by createtime,pf_education) as b

on a.年度 = b.年度

order by a.年度

用这个试试

三人乐乐 | 园豆:3195 (老鸟四级) | 2019-02-27 11:01
0

select
edu_year ,sum(t1) '大专',sum(t2) '本科'
from (
SELECT
DATE_FORMAT(t.createtime, '%Y') edu_year,
CASE WHEN t.pf_education ='大专' THEN count(t.id)
ELSE 0 END as t1 ,
CASE WHEN t.pf_education ='本科' THEN count(t.id)
ELSE 0 END as t2
FROM
表 t
GROUP BY
DATE_FORMAT(t.createtime, '%Y'),
t.pf_education ) tmp
GROUP BY edu_year

seesea_bky | 园豆:202 (菜鸟二级) | 2019-02-28 13:08
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册