MySQL常用查询

MySQL 根据两个字段分组，根据两个字段排序

获取年和季度分组，并设置年排序和季度排序。

SELECT year, quarter FROM report
WHERE FIND_IN_SET(`quarter`, 'Q1,Q2,Q3,Q4')
GROUP BY `year`, `quarter`
ORDER BY `year` DESC, `quarter` DESC

多 where 应用

select `create_time`,`field_id`,`field`, `name`, `form_type`, `order_id`, `order_list`, `is_show` 
from `hhir_crm_admin_field` 
where (`types` = 'crm_upload_quarter' and `valid` = 1 and `is_filter` = 0)
ORDER BY `order_id` ASC,`create_time` DESC

MySQL 根据统计 ID 出现次数和根据年度、季度分组

SELECT count(fund_id) AS count_fund_id, year, quarter
FROM report
GROUP BY fund_id, year, quarter

MySQL 分组排序过滤

未过滤的数据

SELECT year, quarter FROM report
GROUP BY `year`, `quarter`
ORDER BY `year` DESC, `quarter` DESC

过滤的数据

SELECT year, quarter FROM report
GROUP BY `year`, `quarter`
HAVING `quarter` NOT IN ('Q1', 'Q2', 'Q3', 'Q4')
AND `quarter` != ''
ORDER BY `year` DESC, `quarter` DESC

MySQL 使用 select 查询将整数 int 转换为字符串

1、使用cast进行类型转换

SELECT CAST(123 AS CHAR); 

2、使用 concat 进行转换

SELECT CONCAT(123,'');