select *
from services
order by
order_num is not null desc, -- сначала не нулевые номера
order_num, -- затем сортировка номеров
name -- в конце сортировка по имениSELECT type, GROUP_CONCAT(model) AS models
FROM furniture
GROUP BY type;SELECT type, JSON_ARRAYAGG(model) AS models
FROM furniture
GROUP BY type;SELECT
Query AS "Ключевая фраза",
SUM(CASE WHEN (Updated = '2021-05-20' AND RegionCode = 54) THEN Yandex ELSE 0 END) AS "Yandex ekb",
SUM(CASE WHEN (Updated = '2021-05-20' AND RegionCode = 193) THEN Yandex ELSE 0 END) AS "Yandex vor"
FROM Queries c
LEFT JOIN SitePositions o ON c.Id = o.QueryId
WHERE SiteId = 33
GROUP BY QueryId, Query;select
id,
name,
quantiy
from (
select
test.*,
avg(quantiy) over() as average
from test
) average_test
where quantiy > average;select *
from news
where not exists (
select 1 from user_news
where user_news.news_id = news.id and user_news.user_id = news.user_id
);select
id, title, avg_value
from params
join (
select
p_id,
`value`,
avg(`value`) over (partition by p_id order by value desc) avg_value,
row_number() over (partition by p_id order by value desc) rn
from data
) avg_data on avg_data.p_id = params.id
where rn = 50
;select
id, title, avg(value) avg_value
from params
join (
select
p_id,
`value`,
row_number() over (partition by p_id order by value desc) rn
from data
) avg_data on avg_data.p_id = params.id
where rn <= 50
group by id, title
;<?php
class FOO {
public function bar() {
echo "Method bar called" . PHP_EOL;
return $this;
}
public function baz() {
echo "Method baz called" . PHP_EOL;
return $this;
}
}
$foo = new FOO;
$foo->bar()->baz();select *
from users
order by ((flag1=1)*1 + (flag2=0)*1 + (flag3=1)*1 +(flag4=0)*1) desc
;foreach ($items["entries"] as $item) {
if ($item["name_item"] === $postData[0]) {
$searchRes = (
is_array($item["size_item"]) and
in_array($postData[1], $item["size_item"])
) or
$item["size_item"] === $postData[1];
}
}$filtered = array_filter(
$items["entries"],
function($item) use ($postData) {
return
$item["name_item"] === $postData[0] and
(
(
is_array($item["size_item"]) and
in_array($postData[1], $item["size_item"])
) or
$item["size_item"] === $postData[1]
);
}
);<?php
$file_content = file_get_contents($file_path);
preg_match_all($pattern, $file_content);fetch('https://sheetdb.io/api/v1/j0s93lmrv0uwi')
.then(response => response.json())
.then(data => {
console.log(data);
return data;
})
.then((response) => {
let resp = response;
console.log(resp);
console.log(resp[8].KEYWORDS); //так не выходит
})
.catch((err)=>console.log(err))create table persons (
id int auto_increment primary key,
name varchar(255),
wealth_sorce varchar(255)
);
insert into persons (name, wealth_sorce) values ('Bill Gates', 'Microsoft'), ('Jeff Bezos', 'Amazon');
create table persons_wealth (
person_id int,
wealth_sum bigint,
updated_at date
);
insert into persons_wealth values
(1, 50000000000, '2020-01-01'),(2, 70000000000, '2020-01-01'),
(1, 56000000000, '2021-01-01'),(2, 90000000000, '2021-01-01');
select distinct
person_id,
name,
first_value(wealth_sum) over (partition by person_id order by updated_at) as start_wealth,
first_value(wealth_sum) over (partition by person_id order by updated_at desc) as end_wealth
from persons_wealth w
join persons p on p.id = w.person_id;
with wealth_data as (
select distinct
person_id,
name,
first_value(wealth_sum) over (partition by person_id order by updated_at) as start_wealth,
first_value(wealth_sum) over (partition by person_id order by updated_at desc) as current_wealth
from persons_wealth w
join persons p on p.id = w.person_id
where updated_at >= '2020-01-01'
) select
person_id,
name,
current_wealth,
(current_wealth - start_wealth) / start_wealth as wealth_change_since_2020_01_01
from wealth_data;$hour = date('H:i');
if ($hour < '08:00' || $hour > '23:00') die('Closed');select
max_col1,
t1.col1,
max_col2,
t2.col2
from (
select
max(if(col1 is null, 0, id)) max_col1,
max(if(col2 is null, 0, id)) max_col2
from test) last_values
join test t1 on t1.id = max_col1
join test t2 on t2.id = max_col2;+==========+======+==========+========+
| max_col1 | col1 | max_col2 | col2 |
+==========+======+==========+========+
| 19 | logo | 23 | ulitsa |
+----------+------+----------+--------+select
sum(amount)
from
records
where
user_id = 1
and date_trunc('month', date) = date_trunc('month', to_date('2021-05-03', 'YYYY-MM-DD'));select
ads.id, ads.title,
json_arrayagg(photo) as photos
from ads
left join photos on ads.id = photos.id_ad
group by ads.id, ads.title
;