SET @@SESSION.old_passwords = 0;
CREATE USER 'newuser'@'%' IDENTIFIED BY 'newpass';
GRANT SELECT, INSERT, UPDATE, DELETE ON `database`.* TO 'newuser'@'%';
FLUSH PRIVILEGES;
SELECT `p`.`title`, `p`.`supplement`, `p`.`link`, `p`.`file`,
`u`.`avatar`, `p`.`pid`, `u`.`lastname`, `u`.`uid`, `c`.`comments`,
CONCAT(IFNULL(`u`.`firstname`, ''), ' ', IFNULL(`u`.`surname`, '')) AS `name`
FROM (
SELECT `c`.`pid`,
JSON_ARRAYAGG(JSON_OBJECT(
'name', CONCAT(IFNULL(`u`.`firstname`, ''), " ", IFNULL(`u`.`surname`, '')),
'avatar', `u`.`avatar`,
'lastname', `u`.`lastname`,
'value', `c`.`value`,
'cid', `c`.`cid`,
'answers', `a`.`answers`
)) AS `comments`
FROM (
SELECT `a`.`cid`,
JSON_ARRAYARG(JSON_OBJECT(
'name', CONCAT(IFNULL(`u`.`firstname`, ''), " ", IFNULL(`u`.`surname`, '')),
'avatar', `u`.`avatar`,
'lastname', `u`.`lastname`,
'value', `a`.`value`,
'cid', `a`.`cid`,
'answer', `a`.`answer`
)) AS `answers`
FROM `comments` AS `c`
JOIN `comments` AS `a` ON `a`.`cid` = `c`.`cid`
LEFT JOIN `users` AS `u` ON `u`.`uid` = `a`.`uid`
WHERE `c`.`pid` = :postId
GROUP BY `a`.`cid`
) AS `a`
RIGHT JOIN `comments` AS `c` ON `c`.`cid` = `a`.`cid`
LEFT JOIN `users` AS `u` ON `c`.`uid` = `u`.`uid`
WHERE `c`.`pid` = :postId
GROUP BY `c`.`pid`
) AS `c`
RIGHT JOIN `posts` AS `p` ON `p`.`pid` = `c`.`pid`
LEFT JOIN `users` AS `u` ON `u`.`uid` = `p`.`uid`
WHERE `p`.`pid` = :postId
WITH RECURSIVE `cte` (`id`, `parent_id`, `title`, `n`) AS (
SELECT `id`, `parent_id`, `title`, 0
FROM `table`
WHERE `id` = :categoryId
UNION
SELECT `t`.`id`, `t`.`parent_id`, `t`.`title`, `n`+1
FROM `cte`
JOIN `table` AS `t` ON `t`.`id` = `cte`.`parent_id`
)
SELECT `id`, `title`
FROM `cte`
ORDER BY `n` DESC
Остаётся только выбрать все строки из ответа и вывести их в цикле. SELECT `recipe_id`, `ingredient_id`
FROM `recipe_ingredients`
WHERE `ingredient_id` IN (501, 900)
SELECT `recipe_id`, COUNT(*) AS `count`,
SUM(`ingredient_id` IN (501, 900)) AS `avail`
FROM `recipe_ingredients`
GROUP BY `recipe_id`
HAVING `count` = `avail`
SELECT `o`.`num`, `o`.`createdon`, `o`.`cost`, `o`.`status`, `p`.`products`
FROM `modx_ms2_orders` AS `o`
LEFT JOIN (
SELECT `order_id`,
JSON_ARRAYAGG(
JSON_OBJECT('name', `name`, 'price', `price`, 'count', `count`)
) AS `products`
FROM `modx_ms2_order_products`
GROUP BY `order_id`
) AS `p` ON `p`.`order_id` = `o`.`id`
WHERE `o`.`user_id` = 106
async function get_balance(user_id) {
return new Promise((resolve, reject) => {
conn.query(
`SELECT * FROM users WHERE user_id = ${user_id}`,
(err, result) => {
if (err) {
reject(err);
}
console.log(result[0].balance) // 0 (как и надо)
resolve(result[0].balance);
},
);
});
}
const result = await get_balance(ctx.message.from.id);
SET `cte_max_recursion_depth` = 10000;
INSERT INTO `test` (`DATE_CREATE`, `DATE_UPDATE`, `ACTIVE`, `USER_ID`, `VALUE`)
WITH RECURSIVE `cte` (`DATE_CREATE`, `DATE_UPDATE`, `ACTIVE`, `USER_ID`, `VALUE`) AS (
SELECT NOW(), NOW(), 1, 1, 1 AS `VALUE`
UNION
SELECT NOW(), NOW(), 1, 1, `VALUE`+1 FROM `cte` WHERE `VALUE` < 9999
)
SELECT *
FROM `cte`
SELECT `day`, `priority`, `count`
FROM (
(SELECT 'today' AS `day`, `priority`, COUNT(*) AS `count`
FROM `table`
WHERE `time` >= CURDATE() AND `time` < CURDATE() + INTERVAL 1 DAY
GROUP BY `priority`)
UNION ALL (SELECT 'yesterday', `priority`, COUNT(*)
FROM `table`
WHERE `time` >= CURDATE() + INTERVAL 1 DAY AND `time` < CURDATE() + INTERVAL 2 DAY
GROUP BY `priority`)
) AS `t`
The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0.