mysql 统计查询

mysql中一条sql语句中使用多个count关联查询多张表

SELECT  sum(fkNum) fkNum,sum(tsNum) tsNum,sum(fjNum) fjNum
FROM (
SELECT COUNT(*) fkNum,0 tsNum,0 fjNum FROM yw_consult_feedback WHERE con_id = 1
UNION ALL
SELECT 0 fkNum,COUNT(*) tsNum,0 fjNum FROM yw_consult_user_fb WHERE con_id =1
UNION ALL
SELECT  0 fkNum,0 tsNum,COUNT(*) fjNum FROM yw_consult_attachment WHERE con_id = 1
) t

结果如图

mybatis插入或者更新

insert into yw_consult_user_fb (con_id, ex_id) values
		<foreach collection="exIdList" item="exId" separator=",">
			(#{conId,jdbcType=INTEGER},#{exId,jdbcType=INTEGER})
		</foreach>
		ON DUPLICATE KEY UPDATE
        con_id = VALUES(con_id),ex_id = VALUES(ex_id)
        

case when 运用

1.分组

SELECT
	user_type userType,
CASE
		user_type 
		WHEN 0 THEN
		'管理员数量' 
		WHEN 1 THEN
		'专家数量' ELSE '未知' 
	END AS NAME,
	COUNT( user_type ) num 
FROM
	sys_user 
GROUP BY
	user_type

2.搜索

SELECT 
max(CASE feedback WHEN 0 THEN 
(SELECT COUNT(*) FROM yw_consult_user_fb WHERE feedback=0 AND ex_id =1)
ELSE 0 END)  wfk ,
max(CASE feedback WHEN 1 THEN 
(SELECT COUNT(*) FROM yw_consult_user_fb WHERE feedback=1 AND ex_id =1)
ELSE 0 END)  asfk,
max(CASE feedback WHEN 2 THEN 
(SELECT COUNT(*) FROM yw_consult_user_fb WHERE feedback=2 AND ex_id =1)
ELSE 0 END) ysfk
FROM
yw_consult_user_fb

查询时间相关数据

今年 YEAR(NOW())

SELECT YEAR(sendtime),YEAR(NOW()) from yw_consult_user_fb

上一年 YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR))

SELECT YEAR(sendtime),YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR)) from yw_consult_user_fb