SELECT `d`.`generation_id`, `d`.`Name`, `d`.`volume`, `d`.`capacity`,
`d`.`type_of_connector`, COUNT(DISTINCT `c`.`color_id`) AS `color_count`
FROM `device` AS `d`
JOIN `device2color_body` AS `c` ON `c`.`device_id` = `d`.`id`
GROUP BY `d`.`generation_id`, `d`.`Name`, `d`.`volume`, `d`.`capacity`,
`d`.`type_of_connector`
SELECT `generation_id`, `Name`, COUNT(*) AS `count`
FROM (
SELECT DISTINCT `d`.`generation_id`, `d`.`Name`, `d`.`volume`, `d`.`capacity`,
`d`.`type_of_connector`, `c`.`color_id`
FROM `device` AS `d`
JOIN `device2color_body` AS `c` ON `c`.`device_id` = `d`.`id`
) AS `t`
GROUP BY `generation_id`, `Name`
WITH `cte` (`id`, `row`) AS (
SELECT `id`, ROW_NUMBER() OVER `win`
FROM `product`
WINDOW `win` AS (PARTITION BY `sku` ORDER BY `quantity` = 0, `price`)
)
UPDATE `cte`
JOIN `product` USING (`id`)
SET `product`.`status` = (`cte`.`row` = 1)
UPDATE `product` AS `p`
LEFT JOIN (
SELECT `t`.`sku`, MIN(`p`.`id`) AS `id`
FROM (
SELECT `sku`, MIN(`price`) AS `price`
FROM `product`
WHERE `quantity` != 0
GROUP BY `sku`
) AS `t`
JOIN `product` AS `p`
ON `p`.`sku` = `t`.`sku` AND `p`.`price` = `t`.`price`
WHERE `p`.`quantity` != 0
GROUP BY `t`.`sku`
) AS `i` ON `i`.`id` = `p`.`id`
SET `p`.`status` = (`i`.`id` IS NOT NULL);
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 `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 |
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...