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;