CREATE TABLE orders (
price DOUBLE,
amount DOUBLE,
total DOUBLE AS (price * amount)
);
select
sum(if(`type` = 0, `sum`, 0)) `income`,
sum(if(`type` = 1, `sum`, 0)) `outcome`,
sum(if(`type` = 0, `sum`, -`sum`)) `balance`
from tablename;
$check_alaliable_amount = 'select * from users where id = ?';
$stmt = $mysqli->prepare($check_alaliable_amount);
$stmt->bind_param("i", $client);
$stmt->execute();
$result = $stmt->get_result();
$user = $result->fetch_assoc();
print_r($user);
if ($user['amount'] >= $summa) {
/* Start transaction */
$mysqli->begin_transaction();
try {
$stmt = $mysqli->prepare("update users set amount=(amount-?) where id=?;");
$stmt->bind_param('ii', $summa, $client);
$stmt->execute();
$stmt = $mysqli->prepare("update users set amount=(amount+?) where id=?;");
$stmt->bind_param('ii', $summa, $shop);
$stmt->execute();
$mysqli->commit();
} catch (mysqli_sql_exception $exception) {
$mysqli->rollback();
throw $exception;
}
}
select *
from cars
where not exists (
select 1
from rental
where
rental.car = cars.regnum
and CURRENT_DATE() between start_date and end_date
);
select *
from cars
where exists (
select 1
from rental
where
rental.car = cars.regnum
and CURRENT_DATE() between start_date and end_date
);
select *
from cars
where exists (
select 1
from rental
where
rental.car = cars.regnum
and start_date > CURRENT_DATE()
);
create table t (
a int
);
insert into t values (2000000000),(2000000000),(2000000000),(2000000000);
select sum(a) * 99 from t;
+==============+
| sum(a) * 99 |
+==============+
| 792000000000 |
+--------------+
select * from test where created_at like '%-01 00:00:00';
update master_products
join (
select master_product_id, MIN(product_prices.price) min_price
from product_prices
join products on product_prices.product_id = products.id
products.published=1 and products.available = 1
group by master_product_id
) min_prices
set price_min = min_prices.min_price
where published = 1 and available = 1;
SELECT type, GROUP_CONCAT(model) AS models
FROM furniture
GROUP BY type;
SELECT type, JSON_ARRAYAGG(model) AS models
FROM furniture
GROUP BY type;
select
id, title, avg_value
from params
join (
select
p_id,
`value`,
avg(`value`) over (partition by p_id order by value desc) avg_value,
row_number() over (partition by p_id order by value desc) rn
from data
) avg_data on avg_data.p_id = params.id
where rn = 50
;
select
id, title, avg(value) avg_value
from params
join (
select
p_id,
`value`,
row_number() over (partition by p_id order by value desc) rn
from data
) avg_data on avg_data.p_id = params.id
where rn <= 50
group by id, title
;
select *
from users
order by ((flag1=1)*1 + (flag2=0)*1 + (flag3=1)*1 +(flag4=0)*1) desc
;
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
;