SELECT * FROM (
SELECT
IDBrewery,
ProductCode,
ProductPrice,
PeriodDate,
ROW_NUMBER() OVER (PARTITION BY ProductCode ORDER BY PeriodDate DESC) RN
FROM Price
JOIN Period ON Period.PeriodID = Price.PeriodID
) LastPrice
WHERE RN = 1
;
SELECT
device_name,
COUNT(*) cnt
FROM users
WHERE
YEAR(created_date) = 2020 AND
year_of_birth < DATE_SUB(CURDATE(), INTERVAL 25 YEAR)
GROUP BY device_name;
SELECT *
FROM comment
WHERE (id = 1 AND reply_id IS NULL) OR reply_id = 1;
PRIMARY KEY AUTO_INCREMENT
для поля `id`, и пофиксить определение поля `text`CREATE TABLE `textlog` (
`id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`chat_id` int(111) NOT NULL,
`text` text NOT NULL,
`date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
UPDATE tbl
SET link = CONCAT(
'<p>[playerjs file:\"',
MID(LEFT(link, LOCATE('?', link, LOCATE('src="', link))-1), LOCATE('src="', link)+5),
'\"]</p>'
);
select *
from messages
join (
select
-- создаем JSON ARRAY из id и берем 2 последних элемента
IF (
JSON_LENGTH(json_arrayagg(id))>2,
JSON_EXTRACT(json_arrayagg(id), CONCAT("$[",JSON_LENGTH(json_arrayagg(id))-2,"]"), CONCAT("$[",JSON_LENGTH(json_arrayagg(id))-1,"]")),
json_arrayagg(id)
) ids,
group_id
from
messages
group by group_id
) last_messages on json_contains(ids, CAST(messages.id AS JSON))
;
select posts.id, posts.body, sum(
case direction
when 'UP' then 1
when 'DOWN' then -1
else 0
end) likes
from posts
left join likes on likes.post_id = posts.id
group by posts.id, posts.body
order by likes desc
;
select posts.id, posts.body, (
count(nullif(direction, 'DOWN')) - count(nullif(direction, 'UP'))) likes
from posts
left join likes on likes.post_id = posts.id
group by posts.id, posts.body
order by likes desc
;
select * from tbl
where from_id + to_id = 20 + 32 and abs(from_id - to_id) = abs(20 - 32);
select tbl1.*
from tbl tbl1
join tbl tbl2 on tbl1.num1 = tbl2.num2 and tbl1.num2 = tbl2.num1
where tbl2.id < tbl1.id
;
+====+======+======+
| id | num1 | num2 |
+====+======+======+
| 6 | 30 | 10 |
+----+------+------+
ADD COLUMN IF NOT EXISTSреализован только в MariaDB, но не существует в MySQL 5-8.
SELECT VERSION();
SELECT `title` AS 'Заголовок статьи', `article`.`id` AS 'Id статьи'
FROM `article`
LEFT JOIN `photo` ON `photo`.`article_id` = `article`.`id` AND `photo`.`made` = 'before' -- or 'after'
WHERE `article_id` IS NULL
GROUP BY `article`.`id`
SELECT `title` AS 'Заголовок статьи', `article`.`id` AS 'Id статьи'
FROM `article`
WHERE NOT EXISTS (
SELECT `article_id`
FROM `photo`
WHERE `photo`.`article_id` = `article`.`id` AND `photo`.`made` = 'before' -- or 'after'
);
select
users.id,
users.name,
sum(money.value) as user_value
from users
join money on users.id = money.user_id
where value > 0
group by users.id, users.name
order by user_value desc;