mysql语法相关函数应用

mysql语法相关

计算两个日期之前的相差多少天

DATEDIFF( CURDATE( ), zp.`end` )

当前时间函数

CURDATE( )

字符拼接函数

CONCAT( AA.CREATED, '--', AA.TITLE, '--', AA.ITEM_TITLE ) 

相加函数

sum( kft.signMins) / 60 AS '实际出勤时常'

查询昨天数据

TO_DAYS( NOW( ) ) - TO_DAYS( ze.date ) = 1

查询当天数据

TO_DAYS( za.date ) = TO_DAYS( NOW( ) )

查询当月数据

DATE_FORMAT( ze.date, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )

case when 用法

SELECT
	SUM(
CASE
	WHEN zb.`severity` = 1 
	OR zb.`severity` = 2 
	OR zb.`severity` = 3 
	OR zb.`severity` = 4 THEN
	1 ELSE 0 
END 
	) AS 'bug总数',
	SUM( CASE WHEN zb.`severity` = 1 THEN 1 ELSE 0 END ) AS '紧急',
	SUM( CASE WHEN zb.`severity` = 2 THEN 1 ELSE 0 END ) AS '严重',
	SUM( CASE WHEN zb.`severity` = 3 THEN 1 ELSE 0 END ) AS '一般',
	SUM( CASE WHEN zb.`severity` = 4 THEN 1 ELSE 0 END ) AS '轻微',
	SUM( CASE WHEN zb.`status` = 'closed' THEN 1 ELSE 0 END ) AS '已关闭',
	SUM( CASE WHEN zb.`status` = 'resolved' THEN 1 ELSE 0 END ) AS '已解决',
	SUM( CASE WHEN zb.`status` = 'active' THEN 1 ELSE 0 END ) AS '未解决' 
FROM
	zt_bug zb 
WHERE
	zb.project IN (
SELECT
	zp.id 
FROM
	zt_project zp
	LEFT JOIN zt_user zu ON zu.account = zp.PM 
WHERE
	zp.`status` = 'doing' 
	AND zp.parent IN ( SELECT id FROM zt_project zp WHERE zp.type = 'program' AND zp.`status` = 'doing' ) 
	);

SUM( CASE WHEN zb.status = ‘active’ THEN 1 ELSE 0 END ) AS ‘未解决’
解释：如果status = ‘xxxx’ 就赋值为 1，否则为0，sum函数求总和

以上函数相关sql

除却带oa的为sqlserve语句，其余都为msql语句

-- 正在进行的项目
SELECT
	zp.`name` as '项目名称',
	zp.`begin` as '计划开始时间',
	zp.`end` as '计划结束时间',
	zp.PM as '负责人'
FROM
	zt_project zp 
WHERE
	zp.deleted = '0' 
	AND zp.`status` = 'doing';

-- 延期的项目
SELECT
	zp.`name` AS '项目名称',
	zp.`end` AS '计划结束时间',
	zu.realname AS '负责人',
	( CASE zp.`status` WHEN 'doing' THEN '进行中' END ) AS '状态',
	DATEDIFF( CURDATE( ), zp.`end` ) AS '延期天数' 
FROM
	zt_project zp
	LEFT JOIN zt_user zu ON zu.account = zp.PM 
WHERE
	CURDATE( ) > zp.`end` 
	AND zp.`status` = 'doing' 
	AND zp.parent IN ( SELECT id FROM zt_project zp WHERE zp.type = 'program' AND zp.`status` = 'doing' );

-- bug统计(以bug级别维度)	
SELECT
	SUM(
CASE
	WHEN zb.`severity` = 1 
	OR zb.`severity` = 2 
	OR zb.`severity` = 3 
	OR zb.`severity` = 4 THEN
	1 ELSE 0 
END 
	) AS 'bug总数',
	SUM( CASE WHEN zb.`severity` = 1 THEN 1 ELSE 0 END ) AS '紧急',
	SUM( CASE WHEN zb.`severity` = 2 THEN 1 ELSE 0 END ) AS '严重',
	SUM( CASE WHEN zb.`severity` = 3 THEN 1 ELSE 0 END ) AS '一般',
	SUM( CASE WHEN zb.`severity` = 4 THEN 1 ELSE 0 END ) AS '轻微',
	SUM( CASE WHEN zb.`status` = 'closed' THEN 1 ELSE 0 END ) AS '已关闭',
	SUM( CASE WHEN zb.`status` = 'resolved' THEN 1 ELSE 0 END ) AS '已解决',
	SUM( CASE WHEN zb.`status` = 'active' THEN 1 ELSE 0 END ) AS '未解决' 
FROM
	zt_bug zb 
WHERE
	zb.project IN (
SELECT
	zp.id 
FROM
	zt_project zp
	LEFT JOIN zt_user zu ON zu.account = zp.PM 
WHERE
	zp.`status` = 'doing' 
	AND zp.parent IN ( SELECT id FROM zt_project zp WHERE zp.type = 'program' AND zp.`status` = 'doing' ) 
	);

-- bug统计(以项目维度)
SELECT
	zp.`name` AS '项目名称',
	SUM( CASE WHEN zb.`severity` = 1 OR zb.`severity` = 2 OR zb.`severity` = 3 OR zb.`severity` = 4 THEN 1 ELSE 0 END ) AS 'bug总数',
	SUM( CASE WHEN zb.`severity` = 2 THEN 1 ELSE 0 END ) AS '严重',
	SUM( CASE WHEN zb.`status` = 'resolved' THEN 1 ELSE 0 END ) AS '已解决',
	SUM( CASE WHEN zb.`status` = 'active' THEN 1 ELSE 0 END ) AS '未解决'
-- 	(CASE WHEN zp.PM is NULL THEN zu.realname  WHEN zp.PO is NULL THEN zu.realname END ) AS '负责人' 
FROM
	zt_bug zb
	LEFT JOIN zt_project zp ON zb.project = zp.project
-- 	LEFT JOIN zt_user zu ON zu.account = zp.PM 
WHERE
	zb.project IN (
SELECT
	zp.id 
FROM
	zt_project zp
	LEFT JOIN zt_user zu ON zu.account = zp.PM 
WHERE
	zp.`status` = 'doing' 
	AND zp.parent IN ( SELECT id FROM zt_project zp WHERE zp.type = 'program' AND zp.`status` = 'doing' ) 
	) 
GROUP BY
	zp.project

-- 	延期任务
SELECT
	zp.`name` AS '项目名称',
	zu.realname AS '任务负责人',
	zt.deadline AS '截止时间',
	zt.`name` AS '任务名称',
	( CASE WHEN zt.`status` = 'doing' THEN '进行中' END ) AS '任务状态' 
FROM
	zt_task zt
	LEFT JOIN zt_project zp ON zt.project = zp.project
	LEFT JOIN zt_user zu ON zu.account = zt.assignedTo 
WHERE
	zt.`status` = 'doing' 
	AND CURDATE( ) > zt.`deadline`;

-- 	延期待办
SELECT
	zt.assignedTo AS '任务负责人',
	zd.`name` AS '所属部门',
	zt.`name` AS '待办事项',
	zt.assignedDate AS '截至日期(暂定)',
	zt.`status` AS '任务状态' 
FROM
	zt_todo zt
	LEFT JOIN zt_user zu ON zu.account = zt.assignedTo
	LEFT JOIN zt_dept zd ON zd.id = zu.dept;

-- 禅道系统实时动态(当天)
SELECT
	DATE_FORMAT(za.date,'%T') as '时间',
	zu.realname as '姓名',
	za.action as '实时动态'
FROM
	zt_action za
	LEFT JOIN zt_user zu ON zu.account = za.actor 
WHERE
	TO_DAYS( za.date ) = TO_DAYS( NOW( ) );

-- 	禅道积分排行榜	
SELECT
	zu.`realname` AS '姓名',-- 	zd.`name` AS '所属部门',
	SUM( CASE WHEN zs.`module` != 'user' THEN 1 ELSE 0 END ) AS '操作次数',
	SUM( CASE WHEN zs.`module` = 'user' THEN 1 ELSE 0 END ) AS '登录次数',
	sum( zs.score ) AS '积分' 
FROM
	zt_score zs
	LEFT JOIN zt_user zu ON zu.account = zs.account
	LEFT JOIN zt_dept zd ON zd.id = zu.dept 
WHERE
	zu.deleted = '0' 
	and (zd.id = '13' or zd.id = '14')
GROUP BY
	zs.account 
ORDER BY
	sum( zs.score ) DESC;

-- confluence实时动态
SELECT
	CONCAT( AA.CREATED, '--', AA.TITLE, '--', AA.ITEM_TITLE ) 
FROM
	AO_9412A1_AONOTIFICATION AA;

-- oa考勤实时动态
SELECT
	att.lastname AS '姓名',
	att.signintime AS '上班打卡',
	att.signouttime AS '下班打卡',
	att.cqdesc AS '当日实时动态' 
FROM
	[dbo].[attendanceDynamicView] AS att 
WHERE
	datediff( DAY, att.kqdate, getdate( ) ) =0

-- oa出勤排行榜
SELECT
	h.lastname AS '姓名',
	sum( kft.signMins) / 60 AS '实际出勤时常',
	sum( kft.	belatemins ) AS '迟到',
	sum( kft.absenteeism ) AS '矿工' 
FROM
	kq_format_total kft
	LEFT JOIN hrmresource h ON h.id = kft.resourceid 
	LEFT JOIN HrmDepartment hd ON h.departmentid = hd.id
WHERE
	( datediff( MONTH, kft.kqdate, getdate ( ) ) = 0 ) 
	AND ( h.STATUS = 0 OR h.STATUS = 1 OR h.STATUS = 2 OR h.STATUS = 3 ) 
	and (hd.id ='39' or hd.id ='40' or hd.id ='41')
GROUP BY
	h.id,
	h.lastname 
ORDER BY
	sum( kft.signMins ) DESC

-- 	延期任务数量
SELECT
	count( * ) 
FROM
	zt_task zt
	LEFT JOIN zt_project zp ON zt.project = zp.project
	LEFT JOIN zt_user zu ON zu.account = zt.assignedTo 
WHERE
	zt.`status` = 'doing' 
	AND CURDATE( ) > zt.`deadline`;

-- 延期项目数量
SELECT
    count(*)
FROM
	zt_project zp
	LEFT JOIN zt_user zu ON zu.account = zp.PM 
WHERE
	CURDATE( ) > zp.`end` 
	AND zp.`status` = 'doing' 
	AND zp.parent IN ( SELECT id FROM zt_project zp WHERE zp.type = 'program' AND zp.`status` = 'doing' );