Mysql统计数据时相关函数的使用

来源：互联网发布：数据被锁英文怎么说编辑：程序博客网时间：2024/05/16 00:56

一，在mysql统计业务数据的时候经常用到DATE_FORMAT，COUNT，IF，CASE WHEN 等函数，以下总结在实际中的使用
SELECT
CASE type
WHEN 1
THEN '教学类'
WHEN 2 THEN '科研类'
WHEN 3 THEN '服务类'
END AS '分类',
count(IF(level = 1 ,TRUE,NULL)) AS '校级' ,
count(IF(level = 2 ,TRUE,NULL)) AS '院系级' ,
count(1) AS '数量'

FROM
fa_college
WHERE
isDeleted=0
GROUP BY

type

二、

SELECT
DATE_FORMAT(createdOn,"%Y") AS str,
COUNT(*) AS count1,
COUNT(*) AS count2,
COUNT(IF(DATEDIFF(SYSDATE(), ContractStart)>=365,TRUE,NULL)) AS count3,
COUNT(IF(currenttitle IN (1,2),TRUE,NULL)) AS count4,
COUNT(IF(educationid IN (10),TRUE,NULL)) AS count5,
COUNT(IF(DATEDIFF(SYSDATE(), ContractStart)< 365,TRUE,NULL)) AS count6,
COUNT(IF(DATEDIFF(SYSDATE(), ContractStart)>=90,TRUE,NULL)) AS count7,
COUNT(IF(isChangJiangScholars=1,TRUE,NULL)) AS count8,
COUNT(IF(isThousandsScholars=1,TRUE,NULL)) AS count9,
COUNT(IF(DATEDIFF(SYSDATE(), ContractStart) < 90 AND isChangJiangScholars= 2 AND isThousandsScholars= 2,TRUE,NULL)) AS count10
FROM
fa_foreignexpert
WHERE
isDeleted = 0

0 0