SELECT 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
SELECT [DISTINCT] t.*
FROM t
JOIN t_intervals ti ON t.sequence BETWEEN ti.start AND ti.end
WHERE ti.id in (2,10,30, ..., n)WITH
cte AS ( SELECT *,
ROW_NUMBER() OVER (PARTITION BY sender, receiver ORDER BY created_at DESC) rn
FROM message )
SELECT u1.username sendername,
u2.username receivername,
cte.text_message,
cte.created_at
FROM cte
JOIN users u1 ON cte.sender = u1.id
JOIN users u2 ON cte.receiver = u2.id
WHERE cte.rn = 1[[:<:]], [[:>:]]
These markers stand for word boundaries.
The Spencer library supports word-beginning and word-end boundary markers ([[:<:]] and [[:>:]] notation). ICU does not. For ICU, you can use \b to match word boundaries; double the backslash because MySQL interprets it as the escape character within strings.
ALTER TABLE tablename
MODIFY COLUMN date_create TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
MODIFY COLUMN date_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;INSERT INTO tablename (active, user_id, value)
WITH RECURSIVE
cte AS ( SELECT 1 num
UNION ALL
SELECT num + 1 FROM cte WHERE num < 9999 )
SELECT 1, 1, num
FROM cte;SET SESSION cte_max_recursion_depth = 10000;INSERT INTO tablename (active, user_id, value)
SELECT 1, 1, 1 + t1.num + t2.num * 10 + t3.num * 100 + t4.num * 1000 AS value
FROM (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1
CROSS JOIN (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2
CROSS JOIN (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3
CROSS JOIN (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4;
HAVING value <= 9999;Вопрос: как мне правильно спроектировать таблицу для связей между этими товарами. Чтобы каждый из этих товаров ссылался друг на друга?
CREATE TABLE groups_of_goods (
group_id BIGINT UNSIGNED NOT NULL,
product_id BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (group_id, product_id),
FOREIGN KEY fk_product (product_id) REFERENCES product (product_id)
);