left join count(xxx), CASE WHEN, if(), group by 字段拼接

-- 注：MySQL数据库
-- 1.count(1)与count(*)得到的结果一致，包含null值。
-- 2.count(字段)不计算null值
-- 3.count(null)结果恒为0

-- 查询code字段（20010001.20010002.20010003）的第二部分（20010002）eg: 
-- 20010001 ：20010001
-- 20010001.20010002 ：20010002
-- 20010001.20010002.20010003 ：20010002
-- 20010001.20010002.20010003.20010004 ：20010002
-- SUBSTRING_INDEX(SUBSTRING_INDEX(code,".",2), '.', -1)：先左边数前两个，再右边数第一个
SELECT code, SUBSTRING_INDEX(SUBSTRING_INDEX(code,".",2), '.', -1) FROM sys_area

-- 查询最大时间的数据（多条取时间最大的那条，一条直接取）fun1
SELECT * FROM table a WHERE a.ba_time IN (SELECT MAX(b.ba_time) FROM table b WHERE b.id=a.id)
-- 查询最大时间的数据fun2
SELECT * FROM table a WHERE NOT EXISTS(SELECT 1 FROM table WHERE id=a.id AND ba_time>a.ba_time)
-- 查询每个学生最大分数的数据fun3(同一个student有相同的grades的话会查出多条需要group by fk_id)
SELECT a.* FROM t_grades a 
INNER JOIN (
	SELECT student_id, MAX(grades) grades FROM t_grades 
	GROUP BY student_id
) AS t ON t.student_id = a.student_id AND a.grades = t.grades 
group by student_id 

-- 同一条数据不同字段判断筛选，这里是时间大于30天比较(左连接条件也可以放到where/having中)
SELECT count(DISTINCT a.company_id) declareCount 
FROM check a 
INNER JOIN company b ON b.check_id = a.id AND DATE_ADD(c.ba_time, INTERVAL +30 DAY) < a.reportprovide_time AND a.type = 3
WHERE TRUE

SELECT a.*, B.id, B.business_id, COUNT(d.id) FROM c_dosemonitor a
LEFT JOIN tp_project b ON b.business_id = a.ID
LEFT JOIN c_dosemonitor_patch c ON c.dosemonitor_id = a.id AND c.is_valid = 1 -- 在连接时, 左边的一定会查出来
LEFT JOIN c_dosemonitor_data d ON d.patch_id = c.id AND d.is_valid = 1
WHERE a.is_valid = 1 AND b.is_valid = 1  -- 表示在连接完的结果中筛选, 会将查询结果中的一整条删除掉, 包括左边的tp_project
AND b.type = 1
GROUP BY a.id

-- 不同条件的统计(在select中使用if)
SELECT a.*, e.name companyName, b.entrustment_year entrustmentYear, 
CONCAT(COUNT(DISTINCT IF(c.status = 4, TRUE, NULL), '/4')) PROCESS, 
COUNT(DISTINCT d.id) abnormalCount 
FROM tp_project a
LEFT JOIN c_dosemonitor b ON b.ID = a.business_id AND b.is_valid = 1
LEFT JOIN c_dosemonitor_patch c ON c.dosemonitor_id = b.id AND c.is_valid = 1 
LEFT JOIN c_dosemonitor_data d ON d.patch_id=c.id AND d.is_valid=1 AND d.is_abnormal = 1 
LEFT JOIN tp_company e ON e.id = a.cid

-- 分组并得到每组某个字段的拼接(这里得到的是每组去重name字段的拼接,逗号分开, 和每组的数量)
SELECT group_id, GROUP_CONCAT(DISTINCT NAME SEPARATOR ',') NAMES,COUNT(id) nameCount
FROM check_record
GROUP BY group_id;

-- 根据查询结果的某个列值判断,从而取查询结果中的某列
SELECT a.*, 
CASE a.device_type
	WHEN 1 THEN d.area_id
	WHEN 4 THEN e.area_id
END areaName, -- 从d或e表的area_id中确定areaName值
CASE a.device_type
	WHEN 1 THEN CONCAT('ray', d.id)
	WHEN 4 THEN CONCAT('source', e.id)
	ELSE CONCAT('', '')
END restDeviceId,
CASE a.device_type WHEN 1 THEN d.device_name ELSE e.device_name END deviceName,
CASE a.device_type WHEN 1 THEN d.unit_type ELSE e.type END unitType,
CASE a.device_type WHEN 1 THEN d.code ELSE e.code END CODE
from D_LiveCheckDetail a
LEFT JOIN D_check_patch b ON b.id = a.patch_id
LEFT JOIN D_LiveCheck c ON c.id = b.check_id
		
LEFT JOIN e_ray_device d ON d.id = a.device_id
LEFT JOIN e_source_device e ON e.id = a.device_id	
		
where c.is_valid = 1 AND b.is_valid= 1 AND a.is_valid = 1
<if test="checkId != null">
	AND c.id = #{checkId,jdbcType=INTEGER}
</if>
<if test="patchId != null">
	AND b.id = #{patchId,jdbcType=INTEGER}
</if>
<if test="type != null">
	AND a.device_type = #{type,jdbcType=INTEGER}
</if>

-- 3. 不同条件统计数量(这里统计每个省指定年份的不同月份的数量)
SELECT f.province_name, LEFT(a.inspected_area_id, 2), 
COUNT(IF(MONTH(a.create_time)=1,TRUE,NULL)) amount1,
COUNT(IF(MONTH(a.create_time)=2,TRUE,NULL)) amount2,
COUNT(IF(MONTH(a.create_time)=3,TRUE,NULL)) amount3,
COUNT(IF(MONTH(a.create_time)=4,TRUE,NULL)) amount4,
COUNT(IF(MONTH(a.create_time)=5,TRUE,NULL)) amount5,
COUNT(IF(MONTH(a.create_time)=6,TRUE,NULL)) amount6,
COUNT(IF(MONTH(a.create_time)=7,TRUE,NULL)) amount7,
COUNT(IF(MONTH(a.create_time)=8,TRUE,NULL)) amount8,
COUNT(IF(MONTH(a.create_time)=9,TRUE,NULL)) amount9,
COUNT(IF(MONTH(a.create_time)=10,TRUE,NULL)) amount10,
COUNT(IF(MONTH(a.create_time)=11,TRUE,NULL)) amount11,
COUNT(IF(MONTH(a.create_time)=12,TRUE,NULL)) amount12,
COUNT(*)
FROM check_report a 
INNER JOIN sys_area_standard f ON f.id = a.inspected_area_id
WHERE a.is_valid = 1
AND YEAR(a.create_time) = 2018 
GROUP BY LEFT(a.inspected_area_id, 2) -- 按省份分组

4.统计每个省指定年份的项目数, 检测设备数(设备间接挂在项目下)等
不同维度统计(a表有外键来自b表, 既要统计a表某些条件下的数据量, 又要统计b表某些条件下的数据量)
注: 代码较乱, 挑着看select 字段部分代码
SELECT b.province_name, LEFT(a.inspected_area_id, 2), 
		COUNT(DISTINCT IF(a.check_no NOT LIKE '%-%', location.result_no, NULL)) locationAmount, 
		COUNT(DISTINCT IF(a.check_no NOT LIKE '%-%', device.result_no, NULL)) deviceAmount, 
		COUNT(DISTINCT IF(a.check_no NOT LIKE '%-%', a.id, NULL)) reportAmount,
		COUNT(DISTINCT IF(a.check_no LIKE '%-%', location.result_no, NULL)) locationAmount2, 
		COUNT(DISTINCT IF(a.check_no LIKE '%-%', device.result_no, NULL)) deviceAmount2, 
		COUNT(DISTINCT IF(a.check_no LIKE '%-%',a.id,NULL)) reCheckReportAmount
		FROM check_report a
		LEFT JOIN (
			SELECT a.* FROM check_record a
			INNER JOIN (SELECT b.* FROM check_report b 
						WHERE b.is_valid = 1
						<if test="year!='' and year!=null">
							AND YEAR(b.recheck_completed_time)= #{year}
						</if>
						) AS check_report_temp
			ON check_report_temp.id = a.report_id
			WHERE a.is_valid = 1 AND check_report_temp.is_valid = 1
			AND template_id IN(1,2,12) 
			GROUP BY a.result_no
		)AS location ON location.report_id = a.id
		LEFT JOIN (
			SELECT a.* FROM check_record a
			INNER JOIN (SELECT b.* FROM check_report b 
						WHERE b.is_valid = 1
						<if test="year!='' and year!=null">
							AND YEAR(b.recheck_completed_time)= #{year}
						</if>
						) AS check_report_temp
			ON check_report_temp.id = a.report_id
			WHERE a.is_valid = 1 AND check_report_temp.is_valid = 1
			AND template_id IN(3,4,5,6,7,8,9,10,11) 
			GROUP BY a.result_no
		)AS device ON device.report_id = a.id
		LEFT JOIN sys_area_standard b ON b.id = a.inspected_area_id
		WHERE a.is_valid = 1
		<if test="year!='' and year!=null">
			AND YEAR(a.recheck_completed_time)= #{year}
		</if>
		GROUP BY LEFT(a.inspected_area_id, 2)

-- 5. 首字母查询g/G开头的
SELECT a.* FROM (
SELECT name_ch,
CHAR(INTERVAL(CONV(HEX(LEFT(CONVERT(name_ch USING gbk),1)),16,10),
      0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,
     0xBBF7,0xBBF7,0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,
     0xC6DA,0xC8BB,0xC8F6,0xCBFA,0xCDDA,0xCDDA,0xCDDA,0xCEF4,
     0xD1B9,0xD4D1)+64) AS dxzm,
CHAR(INTERVAL(CONV(HEX(LEFT(CONVERT(name_ch USING gbk),1)),16,10),
      0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,
     0xBBF7,0xBBF7,0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,
     0xC6DA,0xC8BB,0xC8F6,0xCBFA,0xCDDA,0xCDDA,0xCDDA,0xCEF4,
     0xD1B9,0xD4D1)+96) AS xxzm
FROM student
) AS a 
WHERE a.dxzm = 'G' OR a.xxzm = 'g';

-- 6.模糊匹配多个字段
<!-- 模糊匹配关键字 -->
    <if test="keyword!=null and keyword!=''">
	    AND CONCAT(
			IFNULL(apply_name, ''), ',',  
			IFNULL(apply_mobile, ''), ',',
			IFNULL(cname, '')
		) like CONCAT('%',#{keyword},'%')
	</if>

-- 7.查询出来insert
-- 补齐项目-设备关系数据
INSERT INTO check_report_device (report_id, hospital_id, device_id, check_time)
SELECT b.id report_id, a.hospital_id, c.hospital_device_id device_id, c.check_time check_time
FROM hospital_device a
INNER JOIN check_report b ON b.hospital_id = a.hospital_id AND b.is_valid = 1
INNER JOIN check_record c ON c.report_id = b.id AND c.is_valid = 1
WHERE b.status >= 8
AND NOT EXISTS (SELECT * FROM check_report_device d WHERE d.report_id = b.id AND d.device_id = c.hospital_device_id AND d.is_valid = 1)
GROUP BY b.id, c.hospital_device_id
ORDER BY b.id DESC


-- 其他
-- 查询整个数据库大小/MB
SELECT CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024), 2), 'MB') AS DATA_SIZE,
CONCAT(ROUND(SUM(INDEX_LENGTH/1024/1024), 2), 'MB') AS INDEX_SIZE,
CONCAT(ROUND(SUM(DATA_LENGTH + INDEX_LENGTH)/1024/1024, 2), 'MB') AS TOTAL_SIZE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'chemical_query';

-- 查询数据库中部分表的大小/MB
SELECT
CONCAT(ROUND((SUM(DATA_LENGTH) + SUM(INDEX_LENGTH))/1024/1024, 2), 'MB') AS TOTAL_SIZE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'chemical_query' 
AND TABLE_NAME LIKE'chemical%';