Например, пишу я такой SELECT и все нормально
SELECT Trip.plane, Trip.id
Но при таком мне выдает ошибку
SELECT Trip.plane, COUNT(Trip.plane) planes
Откуда это берется вообще? /*!40001 SQL_NO_CACHE */ и как это убрать?
/*!) - код из комментария обрабатывается любой версией.В голове идея создать varchar ячейку и в неё через запятую всё записывать.
CREATE TABLE words (
word_id INT AUTO_INCREMENT PRIMARY KEY,
word VARCHAR(255) UNIQUE NOT NULL
);
CREATE TABLE phrase (
phrase_id INT NOT NULL,
FOREIGN KEY (phrase_id) REFERENCES phrases (phrase_id),
word_id INT NOT NULL,
FOREIGN KEY (word_id) REFERENCES words (word_id),
word_position INT,
PRIMARY KEY (phrase_id, word_position)
);INSERT ... VALUES ( ... , CAST(REPLACE(@value, ',', '.') AS DOUBLE), ...CREATE PROCEDURE delete_rows()
BEGIN
SELECT @@autocommit INTO @autocommit;
SET SESSION autocommit = ON;
REPEAT
DELETE FROM bigtable WHERE state=2 LIMIT 10000;
SELECT SLEEP(1) INTO @tmp;
UNTIL NOT ROW_COUNT() END REPEAT;
SET SESSION autocommit = @autocommit;
ENDCREATE TABLE data (
data_id INT AUTO_INCREMENT PRIMARY KEY,
data_for_insertion VARCHAR(255) );
INSERT INTO data VALUES (1, 'Новое сообщение по расписанию', DEFAULT);CREATE TABLE schedule (
schedule_id INT AUTO_INCREMENT PRIMARY KEY,
data_id INT,
FOREIGN KEY (data_id) REFERENCES data (data_id) ON DELETE CASCADE ON UPDATE CASCADE,
shedule_time TIME );
INSERT INTO schedule VALUES
(NULL, 1, '09:20'),
(NULL, 1, '09:40'),
(NULL, 1, '11:20'),
(NULL, 1, '11:40');CREATE TABLE main (
main_id INT AUTO_INCREMENT PRIMARY KEY,
inserted_data VARCHAR(255),
created_as DATETIME DEFAULT CURRENT_TIMESTAMP );CREATE EVENT schedule
ON SCHEDULE EVERY 1 MINUTE
DO
INSERT INTO main (inserted_data)
SELECT data.data_for_insertion
FROM data
JOIN schedule USING (data_id)
WHERE schedule_time > CURRENT_TIME - INTERVAL 1 MINUTE
AND schedule_time <= CURRENT_TIME;SELECT UserID, t1.CountMessage-t2.CountMessage
FROM daily_exp_snapshots t1
JOIN daily_exp_snapshots t2 USING (UserID)
WHERE t1.date_added >= CURRENT_DATE
AND t1.date_added < CURRENT_DATE + INTERVAL 1 DAY
AND t2.date_added >= CURRENT_DATE - INTERVAL 1 DAY
AND t2.date_added < CURRENT_DATEWITH 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 = 6WITH
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.idWITH
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