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'
)
select name, avg(rate) rate
from users u
left join replies r on r.user_id = u.id
group by u.name
order by rate desc
SELECT
`tokens`.*
FROM `tokens`
ORDER BY REPLACE(SUBSTRING_INDEX(s_short,';',3), SUBSTRING_INDEX(s_short,';',2), '') DESC;
SELECT
`tokens`.*
FROM `tokens`
ORDER BY SUBSTRING_INDEX(SUBSTRING_INDEX(s_short,';',3),';',-1) DESC;
concat('category_id=', t1.id) = t2.category_id;
SELECT posts.id_post, posts.text_post, posts.photo, posts.created_at,
users.id_user, users.login, users.name, users.surname, users.avatar
COALESCE(COUNT(`likes`.`id_post`), 0) AS `likes`
FROM `posts`
INNER JOIN `users_connections` ON posts.id_user = users_connections.id_followed
INNER JOIN `users` ON users.id_user = posts.id_user
LEFT JOIN `likes` ON `likes`.`id_post` = `posts`.`id_post`
WHERE users_connections.id_follower = :id_follower
AND `created_at` BETWEEN '$prewweek' AND '$nowdate' ORDER BY `created_at`
GROUP BY posts.id_post, posts.text_post, posts.photo, posts.created_at,
users.id_user, users.login, users.name, users.surname, users.avatar
CREATE TABLE book (
id INTEGER PRIMARY KEY,
title VARCHAR(128),
year INTEGER,
author_id INTEGER,
FOREIGN KEY(author_id) REFERENCES author(id)
);
SELECT
SUM(`summa`) as full_summa,
SUM(
IF(
DATE_FORMAT(`start_date`, '%Y-%m') = DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -1 MONTH), '%Y-%m'),
`summa`,
0
)
) `month_summa`,
AVG(
IF(
DATE_FORMAT(`start_date`, '%Y-%m') = DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -1 MONTH), '%Y-%m'),
`summa`,
null
)
) `month_average`
FROM S;
echo json_encode($result, JSON_NUMERIC_CHECK);
SELECT
`messages`.`id` AS `messages_id`, `money_box`.`id` AS `money_box_id`
FROM `messages`,`money_box`;
CREATE TABLE sportsmans(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
full_name VARCHAR(255),
email VARCHAR(255),
phone_number VARCHAR(11),
birthday date,
passport_number int(15),
average_rate int(11),
biography text,
video VARCHAR(255),
created_at datetime
);
select
row.user_id, count(*) matches_row_count
from users user_row
join users row on
(user_row.a_id = row.a_id) and
(user_row.b_id = row.b_id) and
(user_row.id != row.user_id)
where user_row.user_id = 1
group by row.user_id;
<?php
function get_categoies($pdo)
{
$stmt = $pdo->prepare("SELECT * FROM `categories`");
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
function get_prices_by_category($pdo, $category_id)
{
$stmt = $pdo->prepare("SELECT * FROM `prices` WHERE category_id = ?");
$stmt->execute([$category_id]);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
$categories = get_categoies($pdo);
foreach ($categories as $category) {
echo '<h3>'.$category['title'].'</h3>
<div class="cblock">';
$prices = get_prices_by_category($pdo, $category['id']);
foreach ($prices as $price) {
echo '<div class="pricelist-row">
<div class="service">' . $price["title"] . '</div>
<div class="price">от ' . $price["price"] . ' руб/' . $price["type"] . '</div>
</div>';
}
echo '</div>';
}
SELECT user_id, count(distinct created)
FROM orders
WHERE created > DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) -- get last 30 day orders
GROUP BY user_id
HAVING count(distinct created) = 30 -- check user have orders in 30 different days
;