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хотелось бы посмотреть на версию mysql 5.7
SELECT department.name as department_name,
employee.name
FROM employee
JOIN department ON employee.department_id = department.id
WHERE 2 > ( SELECT COUNT(*)
FROM employee emp
WHERE emp.department_id = employee.department_id
AND emp.id < employee.id )SELECT department.name as department_name,
subquery.name
FROM department
JOIN ( SELECT employee.*,
@row_number := CASE WHEN @department = department_id
THEN @row_number + 1
ELSE 1
END rownumber,
@department := department_id
FROM employee
CROSS JOIN ( SELECT @department:=0, @row_number:=0 ) variables
ORDER BY department_id, id ) subquery ON subquery.department_id = department.id
WHERE subquery.rownumber <= 2я просто хочу чтобы добавился столбце 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 )Подскажите пожалуйста, какую функцию необходимо написать, чтобы в column_3 подсчитывалась сумма из column_2 учитывая группировку с column_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.idFROM (table1 , table2)
LEFT JOIN table1 h ON table1.id = h.id
LEFT JOIN table2 s ON table2.id = s.idSELECT *
FROM `users`
WHERE FIND_IN_SET(id, ?);CREATE OR REPLACE FUNCTION _Foo2(st TIMESTAMP, fin TIMESTAMP)
RETURNS TABLE (out_key INTEGER, out_tst timestamptz, out_val FLOAT) AS $$
DECLARE
BEGIN
RETURN QUERY EXECUTE FORMAT('
SELECT %I, %I, %I
FROM %I
WHERE %I BETWEEN ''%s'' AND ''%s'';
',
'in_key', 'in_tst', 'in_val', 'in_table', 'in_tst', st, fin);
END;
$$ LANGUAGE plpgsql;Надо подсчитать количество строк в ответе запроса вида
SELECT COUNT(*) AS cnt
FROM (
SELECT 1
FROM "order" AS o
LEFT JOIN product p ON (p.order_id = o.id)
GROUP BY o.id
HAVING sum(p.price) >= 10
) xSELECT o.id, COUNT(o.id) OVER () AS cnt
FROM "order" AS o
LEFT JOIN product p ON (p.order_id = o.id)
GROUP BY o.id
HAVING sum(p.price) >= 10Я не знаю их архитектуру и название таблиц заранее
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY quantity DESC) rn
FROM tablename
)
SELECT *
FROM cte
WHERE rn = 1;select jsonb_value,
jsonb_set(jsonb_value, '{requisites,kpp}', '"777"', false)
FROM test;