写了个函数,做个记录。作用是扣掉两个数组相同的部分,只保留不同的部分,也就是对称差集。CREATE FUNCTION `array_diff`(`arr1` json, `arr2` json) RETURNS json
NO SQL
DETERMINISTIC
BEGIN
RETURN (
WITH
A AS (SELECT el FROM JSON_TABLE(arr1, '$[*]' COLUMNS(el INT PATH '$')) T),
B AS (SELECT el FROM JSON_TABLE(arr2, '$[*]' COLUMNS(el INT PATH '$')) T)
SELECT JSON_ARRAYAGG(el) FROM (
SELECT el FROM A WHERE A.el NOT IN (SELECT el FROM B)
UNION
SELECT el FROM B WHERE B.el NOT IN (SELECT el FROM A)
) AS T
);
END调用select array_diff('[1,2,3]','[2,3,4]')结果[1, 4]
...
继续阅读
(49)