select
req_id, req_text,
recipient.name as recipient_name,
sender.name as sender_name
from request
join users as recipient on request.recipient_id = recipient.id
join users as sender on request.sender_id = sender.id;CREATE TABLE User (
id INTEGER NOT NULL AUTO_INCREMENT,
login VARCHAR(255) NOT NULL,
password VARCHAR(255) NOT NULL,
status INTEGER NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE Message (
id INTEGER NOT NULL AUTO_INCREMENT,
userid INTEGER NOT NULL,
message VARCHAR(255) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (userid) REFERENCES User(id)
);SELECT order_number, order_status, order_date
FROM (
SELECT order_number, order_status, order_date,
RANK() OVER(PARTITION BY order_number ORDER BY order_date DESC) AS r
FROM orders
) t
WHERE r = 1select d, STR_TO_DATE(d,'%d/%m/%Y в %H:%i')
from t
order by STR_TO_DATE(d,'%d/%m/%Y в %H:%i');SELECT count(*) from `runs` where `date` >= unix_timestamp(curdate());SELECT count(distinct client) from `runs` where `date` >= unix_timestamp(curdate());SELECT COUNT(*) FROM (
SELECT
`client`, MIN(`date`) `first_run`
FROM `runs`
GROUP BY `client`
HAVING `first_run` >= unix_timestamp(curdate())
) `todays_first_runs`;select *, row_number() over(partition by platform_id order by id) n
from t
order by n, platform_id, idselect ch1.chat_id
from chat_user ch1
join chat_user ch2 using (chat_id)
where ch1.user_id = 1 and ch2.user_id = 2;select *
from a,
lateral (select * from b where b.a_id = a.id order by d desc limit 1) m;select user_id
from t
where value_id = 22
group by user_id
having count(*) = 1
intersect
select user_id
from t
where value_id = 23
group by user_id
having count(*) = 2with count_values as (
select user_id, value_id, count(*) cnt
from t
group by user_id, value_id
)
select user_id from count_values where value_id = 22 and cnt > 0
intersect
select user_id from count_values where value_id = 23 and cnt > 1select distinct manufacturer
from spent s
join products p on p.id = s.product_id;select
products.*,
case when bad_manufacturers.manufacturer is null then 'Хороший производитель' else 'Бракодел' end manufacturer_rate
from products
left join (
select distinct manufacturer
from spent s
join products p on p.id = s.product_id
) bad_manufacturers using (manufacturer)//Выбираем все категории с базы данных
$stmt = $pdo->prepare("SELECT * FROM category WHERE id=:id OR name=:name");
$stmt->execute(['id'=> $id, 'name'=> $name]);
$res = $stmt->fetchAll(PDO::FETCH_ASSOC);
//Выводим категории по колонке имени в базе
foreach ($res as $row) {
echo '<a href="/"><b>' . $row['id'] . '</b></a><br>' . PHP_EOL;
}SELECT * FROM `table`
WHERE 1 IN (`с1`,`с2`,`с3`);select
date(`datetime`),
sum(case when `action_type` = 'value_add' then `value` else -`value` end) `total`
from t
group by date(`datetime`)select distinct year(d) from t;
connection.query('INSERT INTO your_table VALUES (?)', [text_to_insert], function(err, rows, fields) {
if (err)
console.log('Error Occurred');
});