with order_files as (
select
*,
row_number() over (partition by filename order by version desc, created_at desc) rn
from files
where user_id = 1
) select id, filename, user_id, version, created_at
from order_files where rn = 1
;
<?php
$mysqli->query("SET NAMES 'utf8'");
$mysqli->query("CREATE TABLE `geolocal` (
`id` INT AUTO_INCREMENT,
`names` VARCHAR(50),
`pass` VARCHAR(20),
`email` VARCHAR (50),
PRIMARY KEY(`id`)
)"
);
$mysqli->query(
"INSERT INTO `geolocal` (`names`, `pass`, `email`) VALUES ('spiderman', 'pautinka', 's.pider@list.ru')"
);
$mysqli->close();
create table customers (
id int primary key auto_increment,
name varchar(128)
);
create table products (
id int primary key auto_increment,
name varchar(128),
price decimal(9, 2)
);
create table baskets (
id int primary key auto_increment,
user_id int references customers(id),
product_id int references products(id),
price decimal(9, 2),
amount decimal(6, 3)
);
-- get records with wrong parents
select *
from test
where
parent_id is not null
and not exists (
select id from test as parents where parents.id = test.parent_id
);
-- delete records with wrong parents
delete test.*
from test
left join test as parents on parents.id = test.parent_id
where test.parent_id is not null and parents.id is null;
select * from test;
-- add foreign key to prevent missing parents
alter table test add foreign key (parent_id) references test(id);
WITH d AS (
SELECT
*,
row_number() over (partition by key order by created desc) rn
FROM move_history
) SELECT *
FROM d
WHERE status = 'COMPLETED'
AND rn = 1
ORDER BY id;
$stmt = $mysqli->prepare("UPDATE req
SET balance = balance - ?
WHERE card =? AND code = ? ;");
$stmt->bind_param("sss", $cost, $card, $code);
// set parameters and execute
$cost = $_GET['cost'];
$card = $_POST['card'];
$code = $_POST['code'];
$stmt->execute();
SELECT `p`.`date`, `p`.`sum`, `p`.`type`, `p`.`visit`
FROM `payments` AS `p`
JOIN `visits` AS `v` ON `p`.`visit` = `v`.`id`
WHERE `p`.`date` >= '2022-05-01 00:00:00'
AND `p`.`date` < '2022-05-31 23:59:00'
AND `p`.`type` IN('cash', 'cashless', 'advance')
AND `v`.`office` = 131
SELECT `p`.`date`, `p`.`sum`, `p`.`type`, `p`.`visit`
FROM `payments` AS `p`
WHERE `p`.`date` >= '2022-05-01 00:00:00' AND `p`.`date` < '2022-05-31 23:59:00'
AND `p`.`type` IN('cash', 'cashless', 'advance')
AND EXISTS (SELECT 1 FROM `visits` `v` WHERE `p`.`visit` = `v`.`id` AND `v`.`office` = 131)
SELECT `p`.`date`, `p`.`sum`, `p`.`type`, `p`.`visit`
FROM `payments` AS `p`
JOIN `visits` AS `v` ON `p`.`visit` = `v`.`id`
WHERE `p`.`date` >= '2022-05-01 00:00:00' AND `p`.`date` < '2022-05-31 23:59:00'
AND `p`.`type` = 'cash'
AND `v`.`office` = 131
UNION
SELECT `p`.`date`, `p`.`sum`, `p`.`type`, `p`.`visit`
FROM `payments` AS `p`
JOIN `visits` AS `v` ON `p`.`visit` = `v`.`id`
WHERE `p`.`date` >= '2022-05-01 00:00:00' AND `p`.`date` < '2022-05-31 23:59:00'
AND `p`.`type` = 'cashless'
AND `v`.`office` = 131
UNION
SELECT `p`.`date`, `p`.`sum`, `p`.`type`, `p`.`visit`
FROM `payments` AS `p`
JOIN `visits` AS `v` ON `p`.`visit` = `v`.`id`
WHERE `p`.`date` >= '2022-05-01 00:00:00' AND `p`.`date` < '2022-05-31 23:59:00'
AND `p`.`type` = 'advance'
AND `v`.`office` = 131
select u.name, sum(amount)
from account a
join user u on u.id = a.user_id
join operation o on o.account_id = a.id
where a.currency = 'EUR'
group by u.id, u.name
having sum(amount) > (
select avg(amount)
from account a
join operation o on o.account_id = a.id
where a.currency = 'EUR'
)