SELECT
name,
ROUND(AVG(establishment_avg_rate), 2) avg_rate
FROM (
SELECT
chains.name,
ROUND(AVG(rate), 2) establishment_avg_rate
FROM Establishment
JOIN Review ON Review.establishment_id = Establishment.id
JOIN Rate ON Rate.review_id = Review.id
JOIN Establishment chains ON chains.id = COALESCE(Establishment.chain_id, Establishment.id)
GROUP BY chains.name, Establishment.name
) establishment_avg
GROUP BY name
ORDER BY avg_rate DESC;class Stopwatch
{
private $mysqli;
private $stopwatch_id;
private $db;
function __construct($mysqli, $stopwatch_id)
{
$this->db = $_ENV['BD_NAME'];
$this->mysqli = $mysqli;
$this->stopwatch_id = $stopwatch_id;
}
public function start()
{
$timestamp = time();
$query = "
INSERT INTO `$this->db`.`stopwatch` (`chat_id`, `timestamp`)
VALUES (?, ?)
ON DUPLICATE KEY UPDATE timestamp = ?
";
$stmt = $this->mysqli->prepare($query);
/* bind parameters for markers */
$stmt->bind_param("iii", $this->stopwatch_id, $timestamp, $timestamp);
/* execute query */
return $stmt->execute();
}
}SELECT *
FROM users
WHERE name = 'Мария' AND city = 'Саратов';SELECT *
FROM users
WHERE name IN ('Саратов', 'Мария') AND city IN ('Саратов', 'Мария');$query = "SELECT * FROM `mytable` ";
$result = $mysqli->query($query);
$rows = $result->fetch_all(MYSQLI_ASSOC);$MyListArray = [];
while ($row = $queryResult->fetch_assoc()) {
$MyListArray[] = $row;
};select replace(
'format|MP3||duration|01:42:46||year|2020||tracks|0||size|238 MB||torrent-mp3|[attachment=190:miami-cyber-nights-vol-1-2020.torrent]',
'|tracks|0|',
''
) as res;+===========================================================================================================================+
| res |
+===========================================================================================================================+
| format|MP3||duration|01:42:46||year|2020||size|238 MB||torrent-mp3|[attachment=190:miami-cyber-nights-vol-1-2020.torrent] |
+---------------------------------------------------------------------------------------------------------------------------+DELETE PeopleFruit.*
FROM PeopleFruit
LEFT JOIN (SELECT MAX(Id) Id FROM PeopleFruit) MaxId USING(Id)
WHERE MaxId.Id IS NULL;SELECT things.id_thing, things.data, GROUP_CONCAT(note) notes
FROM things
LEFT JOIN notes USING(id_thing)
GROUP BY things.id_thing, things.data
;SELECT * FROM orders WHERE order_date BETWEEN CURRENT_DATE() AND NOW();SELECT * FROM orders WHERE order_date BETWEEN '2021-12-15 00:00:00' AND '2021-12-15 23:59:59' ;CREATE TABLE orders (
id int unsigned primary key auto_increment,
amount decimal(9,2),
created_at datetime default NOW()
);INSERT INTO orders (amount) VALUES (999.99);
UPDATE products
JOIN (
SELECT PRODUCT_ID, JSON_ARRAYAGG(FILENAME) IMAGES
FROM images
GROUP BY PRODUCT_ID
) images ON images.PRODUCT_ID = products.ID
SET NEW_IMAGE = IMAGES;SELECT tasks.id, tasks.title, tasks.parent_id, GROUP_CONCAT(subtasks.id) subtasks
FROM tasks
LEFT JOIN tasks AS subtasks ON subtasks.parent_id = tasks.id
GROUP BY tasks.id, tasks.title, tasks.parent_idWITH RECURSIVE recursive_categories (name, parent_id) AS (
SELECT name, parent_id FROM categories WHERE name = 'PHP developer'
UNION ALL
SELECT c.name, c.parent_id FROM categories c JOIN categories ON c.id = categories.parent_id
) SELECT * FROM recursive_categories
ORDER BY parent_id ASC;select
coalesce(name, 'total') name,
sum(`value`) as value_sum
from reserv
group by name
with rollup;<?php
$res = DB::table('reserv as r')
->selectRaw('
coalesce(name, "total") name,
sum(`value`) as value_sum
')
->groupBy(DB::raw('name with rollup'))
->get();
print_r($res);INSERT INTO `brand` (`name`) VALUES ('My own brand');
INSERT INTO `category` (`name`, `discount`) VALUES ('My sweet category', 8);
INSERT INTO `producttype` (`name`) VALUES ('My little productype');
INSERT INTO `product` (`brandId`, `categoryId`, `productTypeId`, `price`) VALUES (2, 2, 2, 200.55);
SELECT *
FROM `product` `p`
JOIN `producttype` `pt` ON `pt`.`id` = `p`.`productTypeId`
JOIN `category` `c` ON `c`.`id` = `p`.`categoryId`
JOIN `brand` `b` ON `b`.`id` = `p`.`brandId`
;$query = "
SELECT
items.*,
GROUP_CONCAT(DISTINCT values_color.value) colors,
GROUP_CONCAT(DISTINCT values_base_color.value) base_colors
FROM items
LEFT JOIN values_color ON values_color.item_id = items.id
LEFT JOIN values_base_color ON values_base_color.item_id = items.id
GROUP BY items.id, items.name
;";
// get DB version using PDO
$stmt = $pdo->prepare($query);
$stmt->execute();
$items = $stmt->fetchAll(PDO::FETCH_ASSOC);SELECT cat.id, cat.name, JSON_ARRAYAGG(
JSON_OBJECT('pro_name',pro.name,'pro_pice',pro.price)
) prod
FROM products as pro
JOIN categories as cat on pro.cat_id = cat.id
GROUP BY cat.id, cat.name
ORDER BY cat.id;with data as (select
id,
a, b, c,
row_number() over (partition by a,b order by id) rn
from t
) select id,a, b, c from data where rn = 1;<?php
$query = "SELECT DATE(`time`) `Day`, SUM(`Profit`) `DaylyProfit`
FROM `history`
WHERE `type` = 1 AND `time` > DATE_SUB(CURRENT_DATE, INTERVAL 6 DAY)
GROUP BY `Day`
ORDER BY `Day` DESC;";
$result = $mysqli->query($query);
$all = $result -> fetch_all(MYSQLI_ASSOC);
echo json_encode($all);update t set
time_from = LOWER(time_from),
nullable = IF(nullable is null or nullable = '', 1, nullable)
;