<?php
$type = 'test';
$f2 = function($s) use($type) {echo $s . "<>" . $type; };
$f2('b');SELECT *
FROM table_1 T1
LEFT JOIN table_2 T2 ON JSON_CONTAINS(
array_id,
CAST(T2.`id` AS JSON),
'$'
);SELECT
author,
count(*) books_count
FROM books
GROUP BY author
ORDER BY books_count DESC
;-- using window functions
select distinct
uid, first_value(utm) over (partition by uid order by id desc) last_utm
from t;
-- using join
select t.uid, t.utm last_utm
from t
join (
select max(id) max_id from t group by uid
) max_ids on max_id = id;
-- using sub-query
select t.uid, t.utm last_utm
from t
where id = (select max(id) max_id from t group by uid having max(id) = t.id);<?php
$random_string = strtoupper(bin2hex(random_bytes(12)));
echo $random_string;select items.id, items.name, group_concat(img) images
from items
join item_images on items.id = item_images.item_id
join images on images.id = item_images.image_id
group by items.id, items.name;$tags = array_column($val["tags"], "value");
echo implode(',', $tags);select
_date,
sum(case when _ustr = 1 then _metr else 0 end) _ustr_1,
sum(case when _ustr = 2 then _metr else 0 end) _ustr_3,
sum(case when _ustr = 3 then _metr else 0 end) _ustr_3,
sum(case when _ustr = 4 then _metr else 0 end) _ustr_4,
sum(case when _ustr = 5 then _metr else 0 end) _ustr_5
from tbl
group by _date;<?php
$search = "Диагональ 17,6";
echo preg_replace(
'/(\d),(\d)/', '$1.$2',
$search
); //Диагональ 17.6$res = array_reduce (
$data['balance'],
function($res, $el) {
if (isset($res[$el['id']])) $res[$el['id']] += $el['balance'];
else $res[$el['id']] = $el['balance'];
return $res;
},
[]
);update tbl
join (
select col1, sum(col2) col2_sum from tbl group by col1
) summed on summed.col1 = tbl.col1
set col3 = col2_sum;<?php
$query = 'SELECT
`methods`.*
FROM `methods`
LEFT JOIN `groups` ON `groups`.`id` = `methods`.`group_id`
WHERE
`methods`.`active` = ? AND
`groups`.`active` = ?
ORDER BY `methods`.`group_id`, `methods`.`position`;';
// get DB version using PDO
$stmt = $pdo->prepare($query);
$stmt->execute([1, 1]);
if ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$group_id = $row['group_id'];
printf(
'<ul data-group_id="%d">' . PHP_EOL . ' <li data-id="%d">%s</li>' . PHP_EOL,
$group_id, $row['id'], $roe['name']
);
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
if ($group_id != $row['group_id']) {
$group_id = $row['group_id'];
printf(
'</ul><ul data-group_id="%d">' . PHP_EOL,
$group_id
);
}
printf(' <li data-id="%d">%s</li>' . PHP_EOL, $row['id'], $roe['name']);
}
printf('</ul>');
}SELECT
category.id, category.title, ARRAY_AGG(subcategory.title)
FROM category
JOIN subcategory ON subcategory.category_id = category.id
GROUP BY category.id, category.title;-- Get previous week
SELECT CONCAT(datepart(YEAR, DATEADD(WEEK, -1, GETDATE())) , '-', datepart(wk, DATEADD(WEEK, -1, GETDATE())));
SELECT
SUM(some_value) AS 'Количество order_cost', SUM(just_value) AS 'Количество доставок'
FROM dbo.Sheet1$
WHERE
period = CONCAT(datepart(YEAR, DATEADD(WEEK, -1, GETDATE())) , '-', datepart(wk, DATEADD(WEEK, -1, GETDATE())))select medialib.id, category.name
from medialib
join category on category.id = medialib.catalog;WITH records AS (
SELECT
a.title,
s.date,
t.status,
ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY s.date DESC) rn
FROM adverts a
LEFT JOIN sys_advert_statuses s ON a.id = s.ad_id
LEFT JOIN sys_advert_status_types t ON s.status_id = t.id
WHERE
s.status_id IN (3, 5, 6)
AND a.id = 4
) SELECT title, date, status FROM records WHERE rn = 1;SELECT user_id, SUM(confirmed) AS "confirmed_ones"
FROM(
SELECT vr.user_id user_id, COUNT(vr.status) AS confirmed
FROM violation_requests AS vr
AND vr.status IN('confirmed'::incident_status)
AND NOT vr.is_deleted
GROUP BY vr.user_id = 1
UNION
SELECT c.user_id, COUNT(c.status)
FROM complaints AS c
AND c.status IN('confirmed'::incident_status)
AND NOT c.is_deleted
GROUP BY c.user_id
) AS res
GROUP BY user_id;