create table persons (
id int auto_increment primary key,
name varchar(255),
wealth_sorce varchar(255)
);
insert into persons (name, wealth_sorce) values ('Bill Gates', 'Microsoft'), ('Jeff Bezos', 'Amazon');
create table persons_wealth (
person_id int,
wealth_sum bigint,
updated_at date
);
insert into persons_wealth values
(1, 50000000000, '2020-01-01'),(2, 70000000000, '2020-01-01'),
(1, 56000000000, '2021-01-01'),(2, 90000000000, '2021-01-01');
select distinct
person_id,
name,
first_value(wealth_sum) over (partition by person_id order by updated_at) as start_wealth,
first_value(wealth_sum) over (partition by person_id order by updated_at desc) as end_wealth
from persons_wealth w
join persons p on p.id = w.person_id;
with wealth_data as (
select distinct
person_id,
name,
first_value(wealth_sum) over (partition by person_id order by updated_at) as start_wealth,
first_value(wealth_sum) over (partition by person_id order by updated_at desc) as current_wealth
from persons_wealth w
join persons p on p.id = w.person_id
where updated_at >= '2020-01-01'
) select
person_id,
name,
current_wealth,
(current_wealth - start_wealth) / start_wealth as wealth_change_since_2020_01_01
from wealth_data;select
max_col1,
t1.col1,
max_col2,
t2.col2
from (
select
max(if(col1 is null, 0, id)) max_col1,
max(if(col2 is null, 0, id)) max_col2
from test) last_values
join test t1 on t1.id = max_col1
join test t2 on t2.id = max_col2;+==========+======+==========+========+
| max_col1 | col1 | max_col2 | col2 |
+==========+======+==========+========+
| 19 | logo | 23 | ulitsa |
+----------+------+----------+--------+select
ads.id, ads.title,
json_arrayagg(photo) as photos
from ads
left join photos on ads.id = photos.id_ad
group by ads.id, ads.title
;SELECT *
FROM `tab1`
JOIN `tab2`
ON LOWER(`tab1`.`name`) LIKE CONCAT('%', LOWER(`tab2`.`name`), '%');select
class,
price
from (
select
class,
price,
row_number() over (partition by class order by price) rn
from complex
) prices
where
(class=1 and rn < 3) -- 2 econom
or (class=2 and rn < 4) -- 3 comfort
or (class=3 and rn < 2) -- 1 premium
;SELECT * FROM `advert` WHERE `active` = '1' AND `type` = '1' ORDER BY `price`;shell> mysqldump db_name > backup-file.sql
shell> mysql db_name < backup-file.sql$sql = 'INSERT INTO users(nickname, email) VALUES(:nickname, :email)';
$query = $pdo->prepare($sql);
$query->execute([':nickname'=> $nickname, ':email' => $email]);update test
set html = replace(
html,
substr(
html,
locate('<nav>', html, 1) + 5,
locate('</nav>', html, locate('<nav>', html, 1))-locate('<nav>', html, 1) - 5
), '');select
locate('<h4 class="notify__title notify__title--small">', html, 1) as o,
locate('</h4>', html, locate('<h4 class="notify__title notify__title--small">', html, 1)) as c,
concat('<span>',
substr(
html,
locate('<h4 class="notify__title notify__title--small">', html, 1) + 47,
locate('</h4>', html, locate('<h4 class="notify__title notify__title--small">', html, 1))-locate('<h4 class="notify__title notify__title--small">', html, 1) - 47
), '</span>') as new_html
from test;$stmt = $mysqli->prepare('INSERT INTO viber_messages (`datetimemsg`, `id_viber`, `message`) VALUES (?, ?, ?);');
$stmt->bind_param('sss', $datetimemsg, $sender_id, $message);
$stmt->execute();SELECT
book.NAME,
SUM(IF(store.CITY = 'Moscow', QUANTITY, 0)) AS CITY1_QUANTITY,
SUM(IF(store.CITY = 'London', QUANTITY, 0)) AS CITY2_QUANTITY,
SUM(IF(store.CITY = 'Moscow', QUANTITY, 0)- IF(store.CITY = 'London', QUANTITY, 0)) AS DIFF_QUANTITY
FROM book
LEFT JOIN book_store ON book_store.BOOK_ID = book.ID
JOIN store ON store.ID = book_store.STORE_ID
WHERE store.CITY IN ('Moscow', 'London')
GROUP BY book.NAME;UPDATE first_table
JOIN second_table ON first_table.id = second_table.id
SET first_table.count = first_table.count + second_table.total_count, second_table.total_count = 0
WHERE second_table.id = 1;SELECT
`users`.`name`,
COUNT(`orders`.`id`) as counts,
COALESCE(SUM(`orders`.`amount`), 0) as sums
FROM
`users`
LEFT JOIN `orders` ON
`users`.`id` = `orders`.`user_id` AND
`orders`.`date` BETWEEN '2021-03-29' AND '2021-03-30'
GROUP BY
`users`.`id`, `users`.`name`;CREATE TABLE Cities (
id int primary key auto_increment,
city varchar(64)
);
CREATE TABLE CityDistnce (
city1 int,
city2 int,
distance int,
foreign key (city1) references Cities(id),
foreign key (city2) references Cities(id),
index (city1, city2)
);