WITH
`cte` AS (
SELECT `from`, SUM(`inc`) AS `inc`
FROM (
SELECT '2022-01-01' AS `from`, 0 AS `inc`
UNION ALL
SELECT '2022-12-31', 0
UNION ALL
SELECT `DateBegin`, +1
FROM `Vacation`
UNION ALL
SELECT `DateEnd` + INTERVAL 1 DAY, -1
FROM `Vacation`
) AS `t`
GROUP BY `from`
)
SELECT LAG(`from`, 1) OVER `win` AS `begin`,
`from` - INTERVAL 1 DAY AS `end`,
(SUM(`inc`) OVER `win`) - `inc` AS `onVacancy`
FROM `cte`
WINDOW `win` AS (ORDER BY `from` ROWS UNBOUNDED PRECEDING)| begin | end | onVacancy |
| ---------- | ---------- | --------- |
| null | 2021-12-31 | 0 |
| 2022-01-01 | 2022-01-10 | 0 |
| 2022-01-11 | 2022-01-25 | 2 |
| 2022-01-26 | 2022-02-04 | 0 |
| 2022-02-05 | 2022-02-09 | 1 |
| 2022-02-10 | 2022-02-19 | 2 |
| 2022-02-20 | 2022-02-24 | 1 |
| 2022-02-25 | 2022-03-13 | 0 |
| 2022-03-14 | 2022-03-28 | 1 |
| 2022-03-29 | 2022-04-08 | 0 |
| 2022-04-09 | 2022-04-23 | 1 |
| 2022-04-24 | 2022-05-28 | 0 |
| 2022-05-29 | 2022-06-12 | 1 |
| 2022-06-13 | 2022-12-31 | 0 |and t1.номер_детали != t2.номер_детали AND t2.номер_детали != t1.номер_деталиand t1.номер_детали < t2.номер_детали
SELECT *
FROM users AS u
LEFT JOIN user_role AS ur ON ur.user_id = u.id
LEFT JOIN roles AS r ON r.id = ur.roles_id
WHERE u.login = :loginSELECT `p`.*,
COUNT(`op`.`id`) AS `ordersCount`,
IFNULL(SUM(`op`.`count`), 0) AS `countOrdersCount`
IFNULL(SUM(`op`.`price` * `op`.`count`), 0) AS `ordersSum`
FROM `products` AS `p`
LEFT JOIN `orders_products` AS `op` ON `op`.`item_id` = `p`.`id`
LEFT JOIN `orders` AS `o` ON `o`.`id` = `op`.`order_id`
AND `o`.`date` > '2020-01-01'
AND `o`.`status` != 2
WHERE `p`.`active` = '1'
GROUP BY `p`.`id`
ORDER BY `p`.`id` DESC
LIMIT 0, 25CREATE TABLE "table" (
field1 VARCHAR(16),
field2 INT GENERATED ALWAYS AS (
ARRAY_POSITION(ARRAY['value1', 'value2', 'value3', 'value4'], field1::TEXT)
) STORED
);CREATE TABLE "table" (
field1 VARCHAR(16),
field2 INT GENERATED ALWAYS AS (
(ARRAY[0, 11, 22, 44])[ARRAY_POSITION(ARRAY['value1', 'value2', 'value3', 'value4'], field1::TEXT)]
) STORED
);CREATE TABLE "table" (
field1 VARCHAR(16),
field2 INT GENERATED ALWAYS AS (
CASE field1
WHEN 'value1' THEN 0
WHEN 'value2' THEN 11
WHEN 'value3' THEN 22
ELSE 44
END
) STORED
);
user_id соответсвует количеству зарегистрированных клиентов и написал следующий кодНеудачное решение. Нет никаких гарантий, что id будут идти строго последовательно.
WITH RECURSIVE `dates` (`date`) AS (
SELECT '2021-06-01'
UNION
SELECT `date` + INTERVAL 1 DAY
FROM `dates`
WHERE `date` < '2021-06-30'
)
SELECT `d`.`date`, MAX(`c`.`count`) OVER `win` AS `registered`
FROM `dates` AS `d`
LEFT JOIN (
SELECT DISTINCT `date`, COUNT(*) OVER `win` AS `count`
FROM `registers`
WINDOW `win` AS (ORDER BY `date`)
) AS `c` ON `c`.`date` = `d`.`date`
WINDOW `win` AS (ORDER BY `d`.`date`)SUBSTRING_INDEX(SUBSTRING_INDEX(JSON_EXTRACT(`json`, '$.offers.url'), '/', -1), '"', 1)BETWEEN '2022-04-06 18:30' AND '2022-04-06 19:00'BETWEEN CONCAT(CURDATE(), ' 18:30') AND CONCAT(CURDATE(), ' 19:00')