WITH
`cte1` AS (
SELECT `to_user_id` AS `respondent`, `message`, `datetime`,
'outgoing' AS `direction`
FROM `messages`
WHERE `from_user_id` = :userId
UNION ALL
SELECT `from_user_id`, `message`, `datetime`,
'incoming' AS `direction`
FROM `messages`
WHERE `to_user_id` = :userId
),
`cte2` AS (
SELECT `respondent`, `message`, `datetime`, `direction`,
ROW_NUMBER() OVER `w` AS `row_num`
FROM `cte1`
WINDOW `w` AS (
PARTITION BY `respondent`
ORDER BY `datetime` DESC
)
)
SELECT `respondent`, `message`, `datetime`, `direction`
FROM `cte2`
WHERE `row_num` = 1- SELECT * FROM `users` WHERE `id` < 9 ORDER BY `id` ASC LIMIT 3
+ SELECT * FROM `users` WHERE `id` < 9 ORDER BY `id` DESC LIMIT 3- $i = 0;
- $coll = '';
- $mask = '';
- foreach ($params as $key => $value) {
- if ($i === 0) {
- $coll = $coll . "$key";
- $mask = $mask . "'" . "$value" . "'";
- } else {
- $coll = $coll . ", $key";
- $mask = $mask . ", '" . "$value" . "'";
- }
- $i++;
- }
+ $coll = implode(',', array_keys($params));
+ $mask = ':' . implode(',:', array_keys($params));- $sql = $conn->prepare("select * from post_comment where body like '%?%'");
+ $sql = $conn->prepare("select * from post_comment where body like CONCAT('%', ?, '%')");SET `datetime` = GREATEST(`datetime`, NOW()) + INTERVAL 10 DAY
WHERE ...WITH RECURSIVE `cte` AS (
SELECT `id`, `parent_id`, `comment`,
CAST(RIGHT(CONCAT('0000', `id`), 4) AS CHAR(256)) AS `path`
FROM `comments`
WHERE `parent_id` = 0
UNION
SELECT `c`.`id`, `c`.`parent_id`, `c`.`comment`,
CONCAT(`cte`.`path`, '/', RIGHT(CONCAT('0000', `c`.`id`), 4)) AS `path`
FROM `cte`
JOIN `comments` AS `c` ON `c`.`parent_id` = `cte`.`id`
)
SELECT *
FROM `cte`
ORDER BY `path`SELECT `c`.`name`, `c`.`iso2`, `t`.`total_links`
FROM (
SELECT `country`, COUNT(*) AS `total_links`
FROM `links`
GROUP BY `country`
) AS `t`
JOIN `countries` AS `c` ON `c`.`iso2` = `t`.`country`
ORDER BY `c`.`name`--UPDATE `members` SET `date_access`='11' WHERE `id`=OLD.`id`;
++SET NEW.`date_access`='11';SELECT ..., 1 AS `level`
UNION
SELECT ..., `cte`.`level` + 1SELECT GROUP_CONCAT(`json`)
FROM JSON_TABLE(
'{"1":1,"2":1,"72":3}',
'$.*' COLUMNS(`json` INT PATH '$')
) as `t`;
| GROUP_CONCAT(`json`) |
| -------------------- |
| 1,1,3 |