WITH RECURSIVE `cte` (`from`, `to`) AS (
SELECT CAST('00:00' AS TIME), CAST('01:00' AS TIME)
UNION SELECT `to`, `to` + INTERVAL 1 HOUR
FROM `cte`
WHERE `to` < '24:00'
)
SELECT `cte`.`from` AS `interval`, COUNT(*) AS `count`
FROM `cte`
LEFT JOIN `visitor_stat` AS `v`
ON `v`.`time` >= `cte`.`from` AND `v`.`time` < `cte`.`to`
WHERE `date` >= :sTime AND `date <= :eTime
GROUP BY `cte`.`from`
ORDER BY `cte`.`from`INSERT INTO `table` (`WebsiteId`)
WITH RECURSIVE `cte` (`num`) AS (
SELECT 1
UNION SELECT `num` + 1
FROM `cte`
WHERE `num` < 9999
)
SELECT `num`
FROM `cte`‘SET collation_connection = utf8_unicode_ci’ в неправильных кавычках.
SELECT `userID`, `productID`, `date`
FROM (
SELECT `userID`, `productID`, `date`,
ROW_NUMBER() OVER `win` AS `row`
FROM `table`
WINDOW `win` AS (PARTITION BY `productID` ORDER BY `date` DESC)
) AS `t`
WHERE `row` = 1SELECT *
FROM `posts`
WHERE `id` IN (
SELECT `id_post`
FROM `tags_rel`
WHERE `id_tag` IN (1,2,3)
) AND `status` = 1
LIMIT 0, 50WITH `cte` AS (
SELECT `ID`, `POLL_ID`, `VOTES`, `NAME`,
ROW_NUMBER() OVER (PARTITION BY `POLL_ID` ORDER BY `VOTES` DESC) AS `row`
FROM `table`
)
SELECT `ID`, `POLL_ID`, `VOTES`, `NAME`
FROM `cte`
WHERE `row` = 1INSERT INTO `mail` (`user`, `to`, `text`, `time`)
SELECT 2, `id`, :text, :time
FROM `users`
WHERE `id` != 2;
INSERT INTO `contacts` (`user`, `ho`, `delete`, `time`)
SELECT *
FROM (
SELECT `id`, 2, 0, :time
FROM `users`
WHERE `id` != 2
UNION ALL SELECT 2, `id`, 0, :time
FROM `users`
WHERE `id` != 2
) AS `t`
ON DUPLICATE KEY UPDATE `delete` = 0, `time` = VALUES(`time`);# Schema (MySQL v8.0)
CREATE TABLE `test` (
`id` INT,
`json` JSON
);
INSERT INTO `test` VALUES
(1, '{"2022-01-01": 4, "2022-05-05": 10, "2022-09-09": 9}'),
(2, '{"2022-05-01": 4, "2022-01-05": 10, "2022-03-09": 9}');
# Query #1
WITH `cte` AS (
SELECT `t`.`id`, MAX(`j`.`json_key`) AS `key`
FROM `test` AS`t`
JOIN JSON_TABLE(
JSON_KEYS(`json`), '$[*]' COLUMNS(`json_key` JSON PATH '$')
) AS `j`
GROUP BY `t`.`id`
)
SELECT `t`.`id`, `c`.`key`,
JSON_EXTRACT(`t`.`json`, CONCAT('$.', `c`.`key`)) AS `val`
FROM `cte` AS `c`
JOIN `test` AS `t` ON `t`.`id` = `c`.`id`
# Result
| id | key | val |
| --- | ------------ | --- |
| 1 | "2022-09-09" | 9 |
| 2 | "2022-05-01" | 4 |WITH RECURSIVE `cte` (`level`, `id`, `login`, `referrer`) AS (
SELECT 0, `id`, `login`, `referrer`
FROM `users`
WHERE `id` = :userId
UNION
SELECT `u`.`level` + 1, `r`.`id`, `r`.`login`, `r`.`referrer`
FROM `cte` AS `u`
JOIN `users` AS `r` ON `r`.`id` = `u`.`referrer`
)
SELECT *
FROM `cte`The error (2002) Can't connect to ... normally means that there is no MySQL server running on the system or that you are using an incorrect Unix socket file name or TCP/IP port number when trying to connect to the server. You should also check that the TCP/IP port you are using has not been blocked by a firewall or port blocking service.https://dev.mysql.com/doc/refman/8.0/en/can-not-co...
не понятно, какой влияние оказывает третья таблица на вторуюНапрямую никакого. У вас INNER JOIN `wp_company_rates` и `wp_term_relationships`, поэтому естественно, что в финальную выборку попадут только те строки, которые совмещаются по условию JOIN'a. Если после RIGHT JOIN вы получили в объединённой строке `wp_company_rates`.`post_id` равное NULL, то, естественно, в INNER JOIN эта строка не войдёт.
SELECT `t2`.*
FROM (
SELECT `IpProxy`
FROM `sc_proxy_request_log`
WHERE `IdSite` = '11'
GROUP BY `IpProxy`
HAVING COUNT(*) <= 3
) AS `t1`
JOIN `sc_proxy` AS `t2` ON `t2`.`Ip` = `t1`.`IpProxy`products_countries (product_id, country_id)
SELECT `u`.*
FROM `users` AS `u`
LEFT JOIN `user_statuses` AS `us`
ON `us`.`user_id` = `u`.`id` AND `us`.`status_id` = 1
WHERE `us`.`status_id` IS NULLSELECT *
FROM `users`
WHERE `id` NOT IN (
SELECT `user_id`
FROM `user_statuses`
WHERE `status_id` = 1
)