MySQL部分查询写法

原创已于 2025-11-12 17:16:08 修改 · 207 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

于 2024-07-18 14:50:03 首次发布

1.根据a列进行分组，然后取b列最新的一条数据，b列是时间类型
使用子查询和GROUP BY

SELECT yt.*  
FROM your_table yt  
INNER JOIN (  
    SELECT a, MAX(b) AS latest_b  
    FROM your_table  
    GROUP BY a  
) AS latest_times ON yt.a = latest_times.a AND yt.b = latest_times.latest_b;

2.查看group_concat返回值的最大长度，默认1024

SHOW VARIABLES LIKE "group_concat_max_len";

2.1修改group_concat返回值的最大长度，永久生效，需要重启
在mysqld下加入

group_concat_max_len = 2048

2.2修改group_concat返回值的最大长度，临时修改，重启失效

SET GLOBAL group_concat_max_len=2048; 
SET SESSION group_concat_max_len=2048;

3.MySQL + mybatis plus批量插入配置

spring:
  datasource:
    url: jdbc:mysql://ip:3306/demo?useUnicode=true&characterEncoding=utf-8&rewriteBatchedStatements=true&allowMultiQueries=true
    username: root
    password: 'xxx'

不加insert-strategy: ignored会导致批量插入失败

mybatis-plus:
 global-config:
   db-config:
     insert-strategy: ignored

4.有一张表，有四个字段，医院，就诊，处方，是否合理，一个医院有多个就诊，每个就诊有多个处方，每个处方可能合理可能不合理，现在要按医院维度进行统计有多少个就诊合理，如果一个就诊既有合理，又有不合理，那就算不合理，这种三级关联统计的查询需要使用子表来实现，举例

SELECT
    hospital,
    SUM(is_reasonable_visit) AS reasonable_visits_count
FROM (
    SELECT
        hop.hsp_id AS hospital,
        hop.visit,
        CASE
            WHEN SUM(CASE WHEN hopd.is_reasonable = 0 THEN 1 ELSE 0 END) > 0 THEN 0
            ELSE 1
        END AS is_reasonable_visit
    FROM
        his_outpatient_prescription hop
    INNER JOIN his_outpatient_prescription_detail hopd ON hop.prescription_number = hopd.prescription_number
        AND hop.hsp_id = hopd.hsp_id
    GROUP BY
        hop.hsp_id,
        hop.visit
) AS visit_reasonability
GROUP BY
    hospital;

5.查询表名、备注、索引情况

select bbb.table_name,bbb.table_comment,aaa.index_name,aaa.index_clumn from information_schema.`TABLES` bbb
left join (SELECT table_name,index_name,group_concat(column_name) as index_clumn FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'ppt_zj'  and index_name != 'PRIMARY'
group by table_name,index_name)  aaa on aaa.table_name = bbb.TABLE_NAME
where bbb.table_schema = 'test'