можно порт сделать так чтобы,порт был и tagged (тегированным -определенным vlan-ом) и аксессом ?
WITH RECURSIVE
cte AS ( SELECT *, 1 level
FROM category
WHERE id = $category_id
UNION ALL
SELECT cat.*, cte.level + 1
FROM category cat
JOIN cte ON cat.id = cte.parent_id )
SELECT *
FROM cte
ORDER BY level;SELECT CONCAT_WS('=>', c1.id, c2.id, c3.id, c4.id, c5.id) path
FROM category c1
LEFT JOIN category c2 ON c1.parent_id = c2.id
LEFT JOIN category c3 ON c2.parent_id = c3.id
LEFT JOIN category c4 ON c3.parent_id = c4.id
LEFT JOIN category c5 ON c4.parent_id = c5.id
WHERE c1.id = $category_idSELECT id, CAST(array_test AS CHAR) array_test
FROM test
WHERE JSON_OVERLAPS(array_test, CAST('[2]' AS JSON));в каком виде лучше хранить этот массив в бд..
запрос для выборки всех записей у которых в поле 'condition' есть "action": "6"
SELECT DISTINCT action.*
FROM action
CROSS JOIN JSON_TABLE(action.`condition`,
'$[*].action' COLUMNS (action INT PATH '$')) jsontable
WHERE jsontable.action = 6
WITH
cte1 AS ( SELECT t1.ts, t1.val,
t2.ts ts_before, t2.val val_before,
t3.ts ts_after, t3.val val_after
FROM test t1
JOIN test t2 ON t1.ts >= TIMESTAMP(t2.ts, @delta)
JOIN test t3 ON t1.ts <= TIMESTAMP(t3.ts, @delta) ),
cte2 AS ( SELECT *,
ROW_NUMBER() OVER (PARTITION BY ts ORDER BY ts_before DESC) rn_before,
ROW_NUMBER() OVER (PARTITION BY ts ORDER BY ts_after ASC) rn_after
FROM cte1 )
SELECT ts,
val,
ts_before,
val_before,
ts_after,
val_after,
CASE WHEN val_after = val_before
THEN val_before
ELSE val_before + (val_after - val_before) / TIMESTAMPDIFF(SECOND, ts_after, ts_before) * TIMESTAMPDIFF(SECOND, ts, TIMESTAMP(ts_before, @delta))
END val_approximated
FROM cte2
WHERE ts > '2021-01-02'
AND rn_before = 1
AND rn_after = 1Не имел дела раньше с оконными функциями в MySQL.
WITH cte AS (SELECT 1 id, CAST('[12, 13, {"12": 123} ]' AS JSON) sort)
SELECT cte.id, JSON_ARRAYAGG(jsontable.element) sort
FROM cte
CROSS JOIN JSON_TABLE(cte.sort,
'$[*]' COLUMNS (element JSON PATH '$')) jsontable
WHERE jsontable.element != CAST('{"12": 123}' AS JSON)
GROUP BY cte.id
