SELECT * FROM `category`
ORDER BY COALESCE(`orderNum`, `name`)
SELECT * FROM `category`
ORDER BY `orderNum` IS NULL, `name`;
select replace(
'format|MP3||duration|01:42:46||year|2020||tracks|0||size|238 MB||torrent-mp3|[attachment=190:miami-cyber-nights-vol-1-2020.torrent]',
'|tracks|0|',
''
) as res;
+===========================================================================================================================+
| res |
+===========================================================================================================================+
| format|MP3||duration|01:42:46||year|2020||size|238 MB||torrent-mp3|[attachment=190:miami-cyber-nights-vol-1-2020.torrent] |
+---------------------------------------------------------------------------------------------------------------------------+
DELETE PeopleFruit.*
FROM PeopleFruit
LEFT JOIN (SELECT MAX(Id) Id FROM PeopleFruit) MaxId USING(Id)
WHERE MaxId.Id IS NULL;
function cmp_function_desc($a, $b){
return ($b['status'] <=> $a['status']);
}
uasort($banks['bank'], 'cmp_function_desc');
print_r($banks);
SELECT things.id_thing, things.data, GROUP_CONCAT(note) notes
FROM things
LEFT JOIN notes USING(id_thing)
GROUP BY things.id_thing, things.data
;
SELECT * FROM orders WHERE order_date BETWEEN CURRENT_DATE() AND NOW();
SELECT * FROM orders WHERE order_date BETWEEN '2021-12-15 00:00:00' AND '2021-12-15 23:59:59' ;
CREATE TABLE orders (
id int unsigned primary key auto_increment,
amount decimal(9,2),
created_at datetime default NOW()
);
INSERT INTO orders (amount) VALUES (999.99);
UPDATE products
JOIN (
SELECT PRODUCT_ID, JSON_ARRAYAGG(FILENAME) IMAGES
FROM images
GROUP BY PRODUCT_ID
) images ON images.PRODUCT_ID = products.ID
SET NEW_IMAGE = IMAGES;
SELECT concat(
JSON_UNQUOTE(json_extract(additional,'$.surname')),
JSON_UNQUOTE(json_extract(additional,'$.firsname')),
JSON_UNQUOTE(json_extract(additional,'$.lastname'))
) fullname
FROM `user_attributes`
;
WITH RECURSIVE recursive_categories (name, parent_id) AS (
SELECT name, parent_id FROM categories WHERE name = 'PHP developer'
UNION ALL
SELECT c.name, c.parent_id FROM categories c JOIN categories ON c.id = categories.parent_id
) SELECT * FROM recursive_categories
ORDER BY parent_id ASC;
select
coalesce(name, 'total') name,
sum(`value`) as value_sum
from reserv
group by name
with rollup;
<?php
$res = DB::table('reserv as r')
->selectRaw('
coalesce(name, "total") name,
sum(`value`) as value_sum
')
->groupBy(DB::raw('name with rollup'))
->get();
print_r($res);
INSERT INTO `brand` (`name`) VALUES ('My own brand');
INSERT INTO `category` (`name`, `discount`) VALUES ('My sweet category', 8);
INSERT INTO `producttype` (`name`) VALUES ('My little productype');
INSERT INTO `product` (`brandId`, `categoryId`, `productTypeId`, `price`) VALUES (2, 2, 2, 200.55);
SELECT *
FROM `product` `p`
JOIN `producttype` `pt` ON `pt`.`id` = `p`.`productTypeId`
JOIN `category` `c` ON `c`.`id` = `p`.`categoryId`
JOIN `brand` `b` ON `b`.`id` = `p`.`brandId`
;