select json_arrayagg(
json_object(
'id', id,
'headlineEn', headlineEn,
'posttextEn', posttextEn
)
)
from posts;-- Query 1: All records
SELECT
wp_posts.post_title,
CASE
WHEN v1.meta_key = 'id_sotrudnika' AND v2.meta_key = 'summa_poluchennoj_zarplaty'
THEN -v2.meta_value
ELSE v2.meta_value
END AS netto
FROM wp_posts
JOIN wp_postmeta v1 ON (wp_posts.ID = v1.post_id)
JOIN wp_postmeta v2 ON (wp_posts.ID = v2.post_id)
WHERE
v1.meta_key IN ('id_rab_1', 'id_sotrudnika') AND v1.meta_value = '981' AND
v2.meta_key IN ('summa_rab_1', 'summa_rab_2', 'summa_poluchennoj_zarplaty');
-- Query 2: Summarized records
SELECT
wp_posts.post_title,
SUM(CASE
WHEN v1.meta_key = 'id_sotrudnika' AND v2.meta_key = 'summa_poluchennoj_zarplaty'
THEN -v2.meta_value
ELSE v2.meta_value
END) AS netto
FROM wp_posts
JOIN wp_postmeta v1 ON (wp_posts.ID = v1.post_id)
JOIN wp_postmeta v2 ON (wp_posts.ID = v2.post_id)
WHERE
v1.meta_key IN ('id_rab_1', 'id_sotrudnika') AND v1.meta_value = '981' AND
v2.meta_key IN ('summa_rab_1', 'summa_rab_2', 'summa_poluchennoj_zarplaty');select d.DEPARTMENT_ID, d.DEPARTMENT_NAME, COUNT(e.EMPLOYEE_ID)
from departments d
left join employees e on d.DEPARTMENT_ID = e.DEPARTMENT_ID
group by d.DEPARTMENT_ID, d.DEPARTMENT_NAME;create unique index col1_uniq on dbname (col1);
SELECT p.*, category_titles, filter_titles
FROM products as p
LEFT JOIN (
SELECT product_id, json_arrayagg(c.title) category_titles from product_category as pc
JOIN categories as c ON pc.category_id=c.id
GROUP BY product_id
) pc ON p.id=pc.product_id
JOIN (
SELECT product_id, json_arrayagg(f.title) filter_titles
FROM product_filter as pf
JOIN filters as f ON pf.filter_id=f.id
GROUP BY product_id
) pf ON p.id=pf.product_id
WHERE p.id='2'ALTER TABLE table_name DROP COLUMN createdAt; -- не правильно
ALTER TABLE table_name DROP COLUMN "createdAt"; -- правильно$q = mysqli_prepare($connection, "INSERT INTO `user_value` (`user_name`, `user_pass`) VALUES (?, ?)");
$q->bind_param("ss", $user_names, $user_pass);
$q->execute();create table info (
ID int generated always as identity,
FirstName text,
LastName text,
Year int2,
Mounth int2,
primary key (FirstName, LastName)
);
INSERT INTO info (FirstName, LastName, Year, Mounth) VALUES ('Дима', 'Скрипов', 1999, 12)
ON CONFLICT (FirstName, LastName) DO UPDATE SET
Year = excluded.Year,
Mounth = excluded.Mounth;