set @code = 1000;
update watch
join (
select id, @code := @code + 1 as code from watch
) data using (id)
set watch.code = data.code;
select json_object(
'id', user_id,
'username', username,
'project_id', project_id,
'roles', json_arrayagg(role)
) roles
from user_projects
join users on users.id = user_id
group by user_id, project_id;
$result = mysqli_query($link, "SELECT * FROM mytable");
if ($result) {
echo "ok";
while ($row = mysqli_fetch_assoc($result)) {
print_r($row);
}
}
SELECT
GREATEST(0, -- return 0 in case negative balance
SUM(
IF(`status` = 1 AND `date_bonus_to` > NOW() , `points`, 0) -- bonus added and not expired
- IF(`status` = 2, `points`, 0) -- bonus used
)
) `balance`
FROM `ps_bonus_account`
WHERE
`id_customer` = 518 AND
`paid` = 1
GROUP BY `id_customer`;
touch ./resources/docker/logs/slow.log
chown 777 ./resources/docker/logs/slow.log
- "./resources/docker/logs/slow.log:/var/log/mysql/mysql-slow.log"
volumes:
- "./resources/docker/config/my.cfg:/etc/mysql/conf.d/config-file.cnf"
- "./resources/docker/logs/mysql/:/var/log/mysql/"
- "./resources/schema/schema.sql:/docker-entrypoint-initdb.d/1-schema.sql"
update routes set
seq = if(trunk_id = 118, 0, trunk_id)
where route_id = 16;
update routes set
seq = if(trunk_id = 118, 0, seq+1)
where route_id = 16;
SELECT
prices.*
FROM
`prices`
JOIN (
SELECT size, MAX(date_time) date_time FROM prices GROUP BY size
) last_price USING (size, date_time)
WHERE
-- model_id = '269' AND partner_id = '0' AND
size <= '32'
AND date_time <= '2021-10-19'
AND price > 0
ORDER BY
size DESC,
date_time DESC;
SELECT * FROM (
SELECT
prices.*,
ROW_NUMBER() OVER (PARTITION BY size ORDER BY date_time DESC) last_price
FROM
`prices`
WHERE
-- model_id = '269' AND partner_id = '0' AND
size <= '32'
AND date_time <= '2021-10-19'
) data
WHERE last_price = 1 AND price > 0
ORDER BY
size DESC;
SELECT query, COUNT(*) cont FROM (
SELECT
CASE
WHEN val LIKE '%111%' THEN '%111%'
WHEN val LIKE '%222%' THEN '%222%'
ELSE 'other'
END query
FROM tbl
WHERE val LIKE '%111%' OR val LIKE '%222%'
) tbl GROUP BY query;
+=======+======+
| query | cont |
+=======+======+
| %111% | 2 |
+-------+------+
| %222% | 1 |
+-------+------+
SELECT
users.name,
COUNT(books.id) books_count,
COUNT(DISTINCT books.author) authors_count
FROM users
JOIN user_books ON users.id = user_books.user_id
JOIN books ON books.id = user_books.book_id
WHERE users.birth_year BETWEEN 2004 AND 2014
GROUP BY users.name
HAVING books_count = 2 AND authors_count = 1;
ALTER TABLE jshopping_orders ADD COLUMN IF NOT EXISTS bonus varchar(24) NOT NULL DEFAULT '';
$con = mysqli_connect("localhost", "root", "", "dbname");
$result = mysqli_query($con, 'SELECT * FROM nomen');
$fp = fopen('file.csv', 'w');
while($row = mysqli_fetch_array($result, MYSQLI_ASSOC))
{
fputcsv($fp, $row);
}
fclose($fp);
select dates.date_time dates, count(distinct tbl.user_id), sum(tbl.price)
from (
select distinct date(tbl.date_time) date_time from tbl
) dates
join tbl on dates.date_time >= date(tbl.date_time)
group by dates.date_time
order by dates.date_time;
<?php
бла-бла-бла ...
если (пользователь с таким именем существует) {
сообщение об ошибке
переход на страницу регистрации
закончили скрипт
}
если (пароль не совпадает с подтверждением) {
сообщение об ошибке
переход на страницу регистрации
закончили скрипт
}
если (не смогли переместить файл с аватаркой) {
сообщение об ошибке
переход на страницу регистрации
закончили скрипт
}
сохраняем нового пользователя в базу данных
сообщение об успешной регистрации
переход на страницу логина
закончили скрипт
?>
DELETE FROM dle_comments
WHERE NOT EXISTS (SELECT 1 FROM dle_post WHERE dle_post.id = dle_comments.post_id);
DELETE dle_comments.*
FROM dle_comments
LEFT JOIN dle_post ON dle_post.id = dle_comments.post_id
WHERE dle_post.id IS NULL;