select * from clc
where not exists (select user_id from stat where stat.user_id = clc.user_id);
select clc.*
from clc
left join stat on stat.user_id = clc.user_id
where stat.user_id is null;
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;
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;
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
;
select count(id) users_with_first_day_order
from users
where exists (
select 1 from orders where orders.user_id = users.id and orders.order_date = users.registration_date
);
select
*
from
orders
where
exists (
select
1
from
order_product
where
order_id = orders.id
and importance = 0
);
SELECT Запись_на_прием.ID_Записи, Водитель.ФИО Водитель, Врач.ФИО Врач
FROM Запись_на_прием
JOIN Пользователь AS Водитель ON Водитель.ID_Пользователя = Запись_на_прием.ID_Водитель
JOIN Пользователь AS Врач ON Врач.ID_Пользователя = Запись_на_прием.ID_Врач
;
WITH p AS (
SELECT
payments.*,
extract(EPOCH from (now() - updated_at::timestamp) / 60) AS minutes_passed
FROM public.payments
) SELECT
p.*,
CASE
WHEN 60 - minutes_passed > 0
AND 60 - minutes_passed <= 15 THEN 'expires'
WHEN 60 - minutes_passed > 15 THEN 'success'
ELSE 'expired'
END
AS exp_status
FROM p;
<?php
$product = 'sweater';
$query = "select distinct color from products where product = ?;";
// get DB version using PDO
$stmt = $pdo->prepare($query);
$stmt->execute([$product]);
$colors = $stmt->fetchAll(PDO::FETCH_ASSOC);
print_r($colors);
SELECT id, SUM(price) sum_price
FROM tbl
GROUP BY id
ORDER BY sum_price DESC;
delete from news where description like 'Мы печем лучший % хлеб в стране.';
<?php
$random_string = strtoupper(bin2hex(random_bytes(12)));
echo $random_string;
select items.id, items.name, group_concat(img) images
from items
join item_images on items.id = item_images.item_id
join images on images.id = item_images.image_id
group by items.id, items.name;
update tbl
join (
select col1, sum(col2) col2_sum from tbl group by col1
) summed on summed.col1 = tbl.col1
set col3 = col2_sum;