數據一?

 <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

?