WITH
cte1 AS (
SELECT jsonb_build_object('name', users.name,
'value', comments.value,
'rating', comments.rating) single_user,
comments.order_id,
'comment' || ROW_NUMBER() OVER (PARTITION BY comments.order_id ORDER BY comments.id) num
FROM users
JOIN comments ON users.id = comments.user_id
),
cte2 AS (
SELECT jsonb_build_object('order_id', order_id) ||
jsonb_object_agg(num, single_user) single_order
FROM cte1
GROUP BY order_id
)
SELECT jsonb_agg(single_order) final_data
FROM cte2;
SELECT "Items"."itemId", sum(price) / sum( sum(price) ) OVER ()
FROM "Items"
INNER JOIN "Purchases" USING("itemId")
GROUP BY "Items"."itemId"
начать поиск элемента в таблице в БД с конца
как получить id последнего элемента в таблице?
CREATE FUNCTION fn_test()
RETURNS TRIGGER
AS $fn_test$
BEGIN
IF OLD.val1 <> NEW.val1 THEN -- задать условие
NEW.id := NEW.id * 10; -- выполнить действие
END IF;
RETURN NEW;
END;
$fn_test$ LANGUAGE plpgsql;
CREATE TRIGGER tr_test
BEFORE UPDATE ON test
FOR EACH ROW
EXECUTE FUNCTION fn_test();
CREATE FUNCTION fn_test()
RETURNS TRIGGER
AS $fn_test$
BEGIN
NEW.id := NEW.id * 10; -- выполнить действие
RETURN NEW;
END;
$fn_test$ LANGUAGE plpgsql;
CREATE TRIGGER tr_test
BEFORE UPDATE ON test
FOR EACH ROW
WHEN (OLD.val1 <> NEW.val1) -- задать условие
EXECUTE FUNCTION fn_test();
CREATE TABLE trs (
wallet_id INT,
amount DECIMAL(12, 2),
hash BYTEA GENERATED ALWAYS AS (sha256((wallet_id + amount) :: TEXT :: BYTEA)) STORED
)
из конкатенированный строки wallet_id + hash
CREATE TABLE trs (
wallet_id INT,
amount DECIMAL(12, 2),
hash BYTEA GENERATED ALWAYS AS (sha256((wallet_id :: TEXT || amount :: TEXT) :: BYTEA)) STORED
)
select *
from products
order by type ASC,
case WHEN type = 'books' then id ELSE 0 END desc,
case WHEN type = "food" then name ELSE 0 END asc,
case WHEN type = "food" then price ELSE 0 END desc,
case WHEN type = "relax" THEN popular ELSE 0 END asc,
case WHEN type = "relax" THEN id ELSE 0 END desc
CREATE TABLE tasks (
id PRIMARY KEY,
definition,
performer_id REFERENCES performer (id)
expired_at DATETIME
);
UPDATE tasks
SET performer_id = @performer_idб
expired_at = NOW() + INTERVAL 'performing time'
WHERE ( expired_at IS NULL or expired_at < NOW() )
AND ( id = @task_id )
n_flag может быть 0..5
num_status 0..20
...
запись, где n_flag=0 и num_status>=10.
WITH cte AS (
SELECT 'Bob' AS name, 25 AS age UNION ALL
SELECT 'Mark' , 30 UNION ALL
SELECT 'Joe' , 35
)
SELECT test.id,
jsonb_build_object('items', jsonb_agg(jae.value_1 || jsonb_build_object('age', cte.age)))
FROM test
CROSS JOIN jsonb_array_elements(test.value->'items') AS jae (value_1)
LEFT JOIN cte ON cte.name = jae.value_1->>'name'
GROUP BY test.id
WITH cte AS (
SELECT PIP.purchase_id, SUM(PIP.product_count * PR.new_price) AS all_sum
FROM product_in_purchase AS PIP
RIGHT OUTER JOIN price_register AS PR ON PIP.product_id = PR.product_id
GROUP BY PIP.purchase_id
)
( SELECT * FROM cte ORDER BY all_sum ASC LIMIT 1 )
UNION
( SELECT * FROM cte ORDER BY all_sum DESC LIMIT 1 )
from
table1 , ( table2
left join table1 h on table1.id = h.id
left join table2 s on table2.id = s.id )
FROM table1
CROSS JOIN table2
LEFT JOIN table1 h ON table1.id = h.id
LEFT JOIN table2 s ON table2.id = s.id
FROM (table1 , table2)
LEFT JOIN table1 h ON table1.id = h.id
LEFT JOIN table2 s ON table2.id = s.id