SQL知识点合集---第二弹

数据一 

 <select id="listPositionAuditCheckSample" resultType="net.nxe.cloud.content.server.entity.PositionAuditCheckSample"><trim prefixOverrides="union all"><if test="userSampleCount != null and userSampleCount > 0">select pat.position_id position_id, pat.id audit_idfrom (select t.creator,cast(concat('[',substring_index(group_concat(concat('"', t.audit_id, '"') order by rand()),',',#{userSampleCount}),']') as json) audit_idsfrom (select pat.position_id, max(pat.id) audit_id, pat.creatorfrom position_audit patwhere pat.creator > 1and pat.entry in ('AUDITING', 'RECHECKING')and pat.create_time between #{startDate} and date_add(#{endDate}, interval 86399 second)and pat.creator in (<foreach collection="auditors" item="auditor" separator=",">#{auditor}</foreach>)and pat.finished = 1 and pat.id = pat.`group`group by pat.position_id, pat.creator, date_format(pat.create_time, '%Y-%m-%d')) tinner join position pn on pn.id = t.position_id and pn.updater = t.creator and pn.source = 'USER'group by t.creator) tinner join position_audit pat on pat.creator = t.creatorand json_contains(t.audit_ids, concat('"', pat.id, '"'))</if><if test="captureExactSampleCount != null and captureExactSampleCount > 0">union allselect pat.position_id position_id, pat.id audit_idfrom (select t.creator,cast(concat('[',substring_index(group_concat(concat('"', t.audit_id, '"') order by rand()),',',#{captureExactSampleCount}),']') as json) audit_idsfrom (select pat.position_id, max(pat.id) audit_id, pat.creatorfrom position_audit patwhere pat.creator > 1and pat.entry in ('AUDITING', 'RECHECKING')and pat.create_time between #{startDate} and date_add(#{endDate}, interval 86399 second)and pat.creator in (<foreach collection="auditors" item="auditor" separator=",">#{auditor}</foreach>)and pat.finished = 1 and pat.id = pat.`group`group by pat.position_id, pat.creator, date_format(pat.create_time, '%Y-%m-%d')) tinner join position pnon pn.id = t.position_id and pn.updater = t.creator and pn.source = 'CAPTURED' and `vague` = 0group by t.creator) tinner join position_audit pat on pat.creator = t.creatorand json_contains(t.audit_ids, concat('"', pat.id, '"'))</if><if test="captureVagueSampleCount != null and captureVagueSampleCount > 0">union allselect pat.position_id position_id, pat.id audit_idfrom (select t.creator,cast(concat('[',substring_index(group_concat(concat('"', t.audit_id, '"') order by rand()),',',#{captureVagueSampleCount}),']') as json) audit_idsfrom (select pat.position_id, max(pat.id) audit_id, pat.creatorfrom position_audit patwhere pat.creator > 1and pat.entry in ('AUDITING', 'RECHECKING')and pat.create_time between #{startDate} and date_add(#{endDate}, interval 86399 second)and pat.creator in (<foreach collection="auditors" item="auditor" separator=",">#{auditor}</foreach>)and pat.finished = 1 and pat.id = pat.`group`group by pat.position_id, pat.creator, date_format(pat.create_time, '%Y-%m-%d')) tinner join position pnon pn.id = t.position_id and pn.updater = t.creator and pn.source = 'CAPTURED' and `vague` = 1group by t.creator) tinner join position_audit pat on pat.creator = t.creatorand json_contains(t.audit_ids, concat('"', pat.id, '"'))</if></trim></select>

知识点 

<trim>标签的使用

• 作用：去除 SQL 片段开头多余的 UNION ALL。

• 示例：

<trim prefixOverrides="union all"><if>...</if><if>...</if>
</trim>

• 说明：如果第一个 <if> 条件不满足，生成的 SQL 不会以 UNION ALL 开头，避免语法错误。

GROUP_CONCAT + ORDER BY RAND()

• 作用：将多个值拼接成字符串，并按随机顺序排序。

• 示例：

group_concat(concat('"', t.audit_id, '"') order by rand())

SUBSTRING_INDEX

• 作用：截取字符串的前 N 个元素。

• 示例：

substring_index(group_concat(...), ',', #{userSampleCount})

CAST(... AS JSON)

• 作用：将字符串转换为 MySQL 的 JSON 类型。

• 示例：

cast(concat('[', substring_index(...), ']') as json)

JSON_CONTAIN

• 作用：检查 JSON 数组中是否包含某个值。

• 示例：

json_contains(t.audit_ids, concat('"', pat.id, '"'))

JSON数据类型处理

函数	作用	示例场景
JSON_ARRAY()	创建 JSON 数组	初始化订单状态历史
JSON_ARRAY_APPEND()	向数组追加元素	添加新状态
JSON_LENGTH()	获取数组/对象长度	限制状态历史长度
JSON_MERGE_PATCH()	合并并覆盖重复键（对象）	更新物流信息
JSON_EXTRACT()	提取 JSON 中的值	查询最新状态
JSON_CONTAINS_PATH()	检查 JSON 中是否存在指定路径	验证物流信息是否存在
JSON_MERGE()	合并 JSON 文档（兼容旧版本）	合并用户备注（注意数组行为）

CREATE TABLE orders (order_id INT PRIMARY KEY AUTO_INCREMENT,user_id INT NOT NULL,status_history JSON COMMENT '状态变更历史（JSON数组）',extra_info JSON COMMENT '额外信息（JSON对象）'
);-- JSON_ARRAY()、JSON_OBJECT
INSERT INTO orders (user_id, status_history, extra_info)
VALUES (1001,JSON_ARRAY('created'),  JSON_OBJECT('note', '请尽快发货') 
);-- JSON_ARRAY_APPEND：追加数据
UPDATE orders
SET status_history = JSON_ARRAY_APPEND(status_history, '$', 'ios')
WHERE order_id = 1;-- JSON_MERGE_PATCH：追加数据
UPDATE orders
SET extra_info = JSON_MERGE_PATCH(extra_info,'{"logistics": {"company": "顺丰", "tracking_no": "SF123456"}}'
)
WHERE order_id = 1;-- JSON_EXTRACT() 提取最后一个状态
SELECTJSON_EXTRACT(status_history, '$[last]') AS last_status
FROM orders
WHERE order_id = 1;-- JSON_CONTAINS_PATH() 验证extra_info中是否有物流信息
SELECTJSON_CONTAINS_PATH(extra_info, 'one', '$.logistics') AS has_logistics
FROM orders
WHERE order_id = 1;-- JSON_MERGE：会合并为数组
UPDATE orders
SET extra_info = JSON_MERGE(extra_info,'{"note": "已加急处理"}'
)
WHERE order_id = 1;-- JSON_LENGTH()长度
SELECT * from orders where JSON_LENGTH(status_history)>2