SELECT
`authors`.`id`, `authors`.`last_name`, `authors`.`first_name`, COUNT(*) `author_count`
FROM `bookissue`
JOIN `books` ON `books`.`id` = `bookissue`.`idBook`
JOIN `authors` ON `authors`.`id` = `books`.`idAuthor`
WHERE YEAR(`bookissue`.`data`) = ?
GROUP BY `authors`.`id`, `authors`.`last_name`, `authors`.`first_name`
ORDER BY `author_count` DESC
LIMIT 1Обратите внимание, что если у вас есть составной индекс для (c1, c2, c3), у вас будут возможности индексированного поиска для одной из следующих комбинаций столбцов:
SELECT *
FROM table_name
WHERE c1 = v1;
SELECT *
FROM table_name
WHERE c1 = v1 AND c2 = v2;
SELECT *
FROM table_name
WHERE c1 = v1 AND c2 = v2 AND c3 = v3;<?php
$operator = "Borat";
// Check if operator exists
$stmt = $pdo->prepare(
"SELECT id_operator FROM operators WHERE name_operator =:name_operator;"
);
$stmt->execute(["name_operator" => $operator]);
$result = $stmt->fetch(PDO::FETCH_ASSOC);
// If not exists store new record
if (!$result) {
$sth = $pdo->prepare(
"INSERT INTO operators (name_operator) VALUES (:name_operator);"
);
$sth->execute(["name_operator" => $operator]);
$id_operator = $pdo->lastInsertId();
} else {
$id_operator = $result["id_operator"];
}
echo "id_operator: $id_operator";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 tbl
WHERE registration >= DATE_SUB(NOW(), INTERVAL 30 MINUTE)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
;