在日常工作中，经常需要统计各种数据。大部分情况下，可以利用EXCEL完成，当我们的需求越来越复杂的时候，可以利用数据库软件实现工作需要，这是我在工作中用过的查询语句，也许对你的工作有所帮助。
——我
#在EXCEL中根据身份证号计算性别、出生日期、年龄
这里以身份证号单元格地址R6为例。
一、性别
=IF(MOD(MID(R6,17,1),2)=1,"男","女")
二、出生日期
=DATE(MID(R6,7,4),MID(R6,11,2),MID(R6,13,2))
三、年龄
=DATEDIF(DATE(MID(R6,7,4),MID(R6,11,2),MID(R6,13,2)),TODAY(),"y")
日期转文本
=TEXT(C1,"yyyymmdd")&"-"&TEXT(D1,"yyyymmdd")
=REPLACE(A3,7,4,"****")
#修改字段的值
UPDATE px2019 SET bqmc="2019年5期育婴" WHERE bqid=1778901;
#查询全部数据
SELECT * from px2019;
#分类查询xxdw,bqid，条件是bqmc is null
SELECT xxdw,bqid from px2019 where bqmc is null group by xxdw,bqid;
#根据其他表格temp1更新相关字段
UPDATE px2019 SET hgzbh=(SELECT hgzbh from temp1 where px2019.sfzh=temp1.sfzh),lxdh=(SELECT lxdh from temp1 where px2019.sfzh=temp1.sfzh),jycj=(SELECT jycj from temp1 where px2019.sfzh=temp1.sfzh)
#分类查询各机构、专业、人数
SELECT xxdw,pxgz,count(*) from px2019 GROUP BY xxdw,pxgz;
#插入数据，来自另一表
INSERT INTO px2019(bqid,xm,xb,sfzh) SELECT bqid,xm,xb,sfzh FROM temp2;
insert into px2019(qx,xxdw,bqid,bqmc,xm,sfzh,xb,rylb,pxgz,pxdd,pxks,pxjs,lxdh) SELECT qx,xxdw,bqid,bqmc,xm,sfzh,xb,rylb,gz,pxdd,pxks,psjs,lxdh from temp;
update px2019 a inner join(select * from tmp3) b on a.sfzh = b.sfzh set a.lxdh=b.lxdh;
#查找重复数据
SELECT * from px2019 where sfzh in(SELECT sfzh from px2019 GROUP BY sfzh HAVING count(sfzh)>1)
#删除身份证号重复的数据，删除xh小的记录
delete from jianding where xh
in (select xh from (
select xh from jianding
where sfzh
in (select sfzh from jianding group by sfzh having count(sfzh) > 1)
and xh not in (select max(xh) from jianding group by sfzh having count(sfzh)>1)
)as temp )
#统计分析培训数据，按照班期统计
SELECT bqid 班期ID,pxks,pxjs,xxdw 培训机构,pxgz 培训工种,
sum(case when xb='女' then 1 end) as 女性人数,
sum(case when xb='男' then 1 end) as 男性人数,
sum(case when rylb='失业职工（享受失业保险金）' then 1 end) as 领取失业金,
count(sfzh) 培训人数 ,
sum(case when rylb ='农村转移劳动力' then 1 end) as 农村转移劳动力,
sum(case when hgzbh <>'' then 1 end) as 合格人数,
sum(case when jdzbh<>'' then 1 end) as 鉴定通过
from px2019 GROUP BY bqid
ORDER BY pxks
#修改表结构，添加字段，自动更新日期
ALTER TABLE px2019 ADD gmt_create datetime not null default current_timestamp;
ALTER TABLE px2019 ADD gmt_modified datetime ON UPDATE CURRENT_TIMESTAMP;