select
coalesce(type::text, 'total by type') type,
coalesce(cost::text, 'total') cost,
sum(cost) as balance
from expenses e
where plan_date = '2023-12-04'
group by rollup (type,cost);create extension pgcrypto;
with a as (
select
house_id,
doc_id,
json_agg(json_build_object('doc_name', doc_name, 'file_id', file_id, 'file_path', file_path)) j
from tbl
group by house_id, doc_id
) select house_id, json_agg(j)
from a
group by house_id;select
o.id, o.name, o.price,
group_concat(case when attrib_name = 'цвет' then attrib_value end) color,
group_concat(case when attrib_name = 'размер' then attrib_value end) size,
group_concat(case when attrib_name = 'дата' then attrib_value end) date
from orders o
left join order_attributes oa on o.id = oa.order_id
group by o.id, o.name, o.price;create table timeslots (
staff_id int,
date date,
time_from time,
time_to time,
free boolean default true
);CREATE EXTENSION btree_gist;
create table timeslots (
staff_id int,
slot_time tsrange,
free boolean default true,
EXCLUDE USING GIST (staff_id WITH =, slot_time WITH &&)
);create table users (
id integer primary key autoincrement,
name text,
email text
);
create table journal (
user_id integer references users(id),
event_date timestamp
/*
остальные поля журнала
*/
);SELECT *
FROM tbl
ORDER BY ...
LIMIT 40, 10SELECT *
FROM tbl
ORDER BY ...
LIMIT 10 OFFSET 40select *
from blocks
join (
select min(b.id) id
from blocks b
join links l on b.link_id = l.id
where l.id = 1
) fb on fb.id = blocks.id;select products.id, products.name, jsonb_agg(url)
from products
left join products_photos on products.id = product_id
group by products.id, products.name|----|---------|----------------------------------|
| id | name | jsonb_agg |
|----|---------|----------------------------------|
| 1 | Товар 1 | ["example1.png", "example2.png"] |if (in_array($person, $arr['Ученик'])) {
echo "$person is Ученик";
} elseif (in_array($person, $arr['Учитель'])) {
echo "$person is Учитель";
}foreach ($arr as $position => $names) {
if (in_array($person, $names)) {
echo "$person is $position";
}
}create table users (
id serial primary key,
balance numeric(9, 2)
);
create table user_cards (
id serial,
user_id int references users(id),
filename text
);