SELECT `p`.`url_product`, `p`.`rus_product`, `p`.`home`, `p`.`phone`,
`p`.`cx`, `p`.`cy`, `s`.`rus_street`, `s`.`url_street`,
`c`.`categories`, `r`.`rating`
FROM `products` AS `p`
LEFT JOIN `streets` ON `s`.`id` = `p`.`id_street`
LEFT JOIN (
SELECT `p`.`id` AS `product_id`, GROUP_CONCAT(CONCAT('<a href=\"/$url_punkt/', `c`.`url_category`, '/\">', `rus_category`, '</a>')) AS `categories`
FROM `products` AS `p`
JOIN `categories` AS `c` ON `c`.`product_id` = `p`.`id`
WHERE `p`.`id_punkt` = :id_punkt
AND `p`.`id` IN (
SELECT `product_id`
FROM `category_product`
WHERE `category_id` = :id_category
)
GROUP BY `p`.`id`
) AS `c` ON `c`.`product_id` = `p`.`id`
LEFT JOIN (
SELECT `p`.`url_product` AS `url_product`, AVG(`pr`.`rating`) AS `rating`
FROM `products` AS `p`
JOIN `products_reviews` AS `pr`
ON `pr`.`type` = `p`.`url_product`
WHERE `p`.`id_punkt` = :id_punkt
AND `p`.`id` IN (
SELECT `product_id`
FROM `category_product`
WHERE `category_id` = :id_category
)
GROUP BY `p`.`url_product`
) AS `r` ON `r`.`url_product` = `p`.`url_product`
WHERE `p`.`id_punkt` = :id_punkt
AND `p`.`id` IN (
SELECT `product_id`
FROM `category_product`
WHERE `category_id` = :id_category
)
SELECT `personalNumber`, SUM(`count`) AS `count`
FROM (
SELECT `personalNumber`, COUNT(`personalNumber`) AS `count`
FROM `emm_departure`
GROUP BY `personalNumber`
UNION ALL SELECT `personalNumber`, COUNT(`personalNumber`)
FROM `emm_arrival`
GROUP BY `personalNumber`
) AS `t`
GROUP BY `personalNumber`
ORDER BY `count`
SELECT `p`.`product_id`, `pi`.`images`, ...
FROM (
SELECT `product_id`,
GROUP_CONCAT(`image` ORDER BY `sort_order`) AS `images`
FROM `oc_product_image`
GROUP BY `product_id`
) AS `pi`
JOIN `oc_product` AS `p`
ON `p`.`product_id` = `pi`.`product_id`
AND `p`.`status` = 1
<?php
$alphabet62 = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
function toAlphabet(int $decimal, string $alphabet) : string
{
$base = mb_strlen($alphabet);
$result = '';
do {
$pos = $decimal % $base;
$result = mb_substr($alphabet, $pos, 1) . $result;
$decimal = intdiv($decimal, $base);
} while ($decimal > 0);
return $result;
}
var_dump(toAlphabet(12345678, $alphabet62));
// string(4) "PNFQ"
var_dump(toAlphabet(12345678, '0aA'));
// string(15) "AaA0A00A000Aa00"
tbl_publications_media.media_type = 3
, условие (tbl_publications_media.media_type IS NULL OR tbl_publications_media.media_type = 3)
будет истинным всегда, от него можно избавиться.SELECT `p`.`id`, `p`.`name`, `p`.`content`, `p`.`create_time`,
`p`.`type`, `p`.`section_id`, `m`.`media_url`
FROM (
SELECT `id`, `name`, `content`, `create_time`, `type`, `section_id`
FROM `tbl_publication`
WHERE `article` = 0 AND `status` = 1 AND `type` = 0
ORDER BY `p`.`id` DESC
LIMIT 12
) AS `p`
LEFT JOIN `tbl_publications_media` AS `m`
ON `m`.`publication_id` = `p`.`id`
AND `m`.`media_type` = 3
SELECT DATE(`t`.`state_at`) AS `date`, `t`.`value`
FROM (
SELECT MAX(`state_at`) AS `max`
FROM `table`
GROUP BY DATE(`state_at`)
) AS `m`
JOIN `table` AS `t`
ON `t`.`state_at` = `m`.`max`
SELECT DISTINCT
DATE(FIRST_VALUE(`state_at`) OVER `win`) AS `date`,
FIRST_VALUE(`value`) OVER `win` AS `value`
FROM `table`
WINDOW `win` AS (
PARTITION BY DATE(`state_at`)
ORDER BY `state_at` DESC
)
DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.https://dev.mysql.com/doc/refman/8.0/en/declare.html