MySQL常用查询

向右看齐 2023-10-06 22:41 10阅读 0赞

## 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

![在这里插入图片描述][162c57856d0e4e5d94926b957c9fda68.png]

多 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

![在这里插入图片描述][022bdd4f75cc455e8a13e452f8206824.png]

## MySQL 分组排序过滤 ##

未过滤的数据

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

![在这里插入图片描述][69a6d5ed13084d949eeda5eb9834f49e.png]  
过滤的数据

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

![在这里插入图片描述][ee9886d399934ad49527aae7ed2e24b7.png]

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

1、使用cast进行类型转换

SELECT CAST(123 AS CHAR);

2、使用 concat 进行转换

SELECT CONCAT(123,'');

[162c57856d0e4e5d94926b957c9fda68.png]: https://img-blog.csdnimg.cn/162c57856d0e4e5d94926b957c9fda68.png
[022bdd4f75cc455e8a13e452f8206824.png]: https://img-blog.csdnimg.cn/022bdd4f75cc455e8a13e452f8206824.png
[69a6d5ed13084d949eeda5eb9834f49e.png]: https://img-blog.csdnimg.cn/69a6d5ed13084d949eeda5eb9834f49e.png
[ee9886d399934ad49527aae7ed2e24b7.png]: https://img-blog.csdnimg.cn/ee9886d399934ad49527aae7ed2e24b7.png