DELIMITER $$
CREATE PROCEDURE ...
...
END$$
DELIMITER ;SELECT `brand`, COUNT(*) AS `count`
FROM `products`
GROUP BY `brand`WHERE `work_start_date` <= :searchEndDate
&& `work_end_date` >= :searchStartDateSELECT *
FROM `products`
WHERE `id` NOT IN (
SELECT `product_id`
FROM `product_options`
WHERE `key` = 'manufacturer'
)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` = :postIdWITH 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` DESCSELECT COUNT(*) + 1 AS `position`
FROM `table`
WHERE `RATING` > (
SELECT `RATING`
FROM `table`
WHERE `NAME` = :name
LIMIT 1
)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`