select
office_id,
json_arrayagg(employee_id) employee_ids
from t
group by office_id;+===========+==============+
| office_id | employee_ids |
+===========+==============+
| 1 | [1, 2] |
+-----------+--------------+mysqldump -u root -p basename --single-transaction > path/to/file.sqlset @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);