select
posts.*,
json_objectagg(likers.user_id, likers.lname) lastnames,
json_objectagg(likers.user_id, likers.avatar) avatars
from posts
join (
select post_id, user_id, fname, lname, avatar, row_number() over (partition by post_id) rn
from likes
join users on users.id = likes.user_id
) likers on likers.post_id = posts.id
where likers.rn < 6
group by posts.id, posts.title, posts.description
;
SELECT
orders.num, orders.createdon, orders.cost, orders.status,
GROUP_CONCAT(products.name) prod_names,
GROUP_CONCAT(products.count) prod_counts,
GROUP_CONCAT(products.cost) prod_costs
FROM `modx_ms2_orders` AS orders
JOIN `modx_ms2_order_products` AS products ON orders.id = products.order_id
WHERE `user_id` = 106
GROUP BY orders.num, orders.createdon, orders.cost, orders.status
select * from (
select
*,
date(from_unixtime(created_at)) created_date,
row_number() over (partition by created_date order by created_at desc) rn
from tbl) row_numbers where rn = 1
select tbl.* from tbl
join (
select max(created_at) max_created_at
from tbl
group by date(from_unixtime(created_at))
) last_time on last_time.max_created_at = tbl.created_at;
create table questionnaires (
id int primary key auto_increment,
title varchar(255)
);
create table questios (
id int primary key auto_increment,
questionnaire_id int,
title varchar(255),
foreign key (questionnaire_id) references questionnaires(id)
);
create table answers (
id int primary key auto_increment,
question_id int,
title varchar(255),
rigth bool,
foreign key (question_id) references questios(id)
);
pool.query("SELECT * FROM users", (err, result)=>{
if (err) throw new Error(err.message);
res.send(result);
});
if(text === 'Магазин'){
man = connection.query('SELECT * FROM `products`', function(error,results){
if(error) {throw error}
for (let i in results) {
bot.sendMessage(chatId, `Продукты: \nИмя: ${results[i]['name']} Цена: ${results[i]['price']}`);
bot.sendMessage(chatId, results[i]['name']);
}
});
}
$stmt = $mysqli->prepare("INSERT INTO coins_info_table (coin_name_id) VALUES (?)");
$stmt->bind_param("s", $id);
foreach ($coin_name_id as $id) {
$stmt->execute();
}
UPDATE Admins A
JOIN Users U ON U.UserID = A.AdminID
SET A.password = U.password
WHERE U.UserID = 123;
UPDATE Admins
SET Admins.password = U.password
FROM Admins A JOIN Users U ON U.UserID = A.AdminID
WHERE A.AdminID = 123;
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)
);