WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY list_name ORDER BY date DESC) rn
FROM table
)
SELECT *
FROM cte
WHERE rn = 1
SELECT t1.*
FROM table t1
NATURAL JOIN (
SELECT list_name, MAX(date) date
FROM table
GROUP BY 1
)
SELECT *
FROM table
ORDER BY user_id = @user_id DESC, id DESC LIMIT 1
SELECT *
FROM table
ORDER BY user_id IS NULL, id DESC LIMIT 1
SELECT *
FROM products
CROSS JOIN categories
LEFT JOIN products_category USING (product_id, category_id)
DELETE t1
FROM review_detail t1
JOIN review_detail t2 USING (title, nickname)
WHERE t1.detail_id < t2.detail_id;
INSERT INTO people (last_name, first_name, sex, birthday)
VALUES ('Иванов', 'Иван', 'm', TO_DATE('1999-04-04', 'YYYY-MM-DD'));
SELECT DISTINCT
client_id,
FIRST_VALUE(massage)
OVER (PARTITION BY client_id
ORDER BY massage IS NULL, dttm) massage
FROM test
WITH cte AS ( SELECT *,
LAG(`datetime`) OVER (PARTITION BY login ORDER BY `datetime`) lag_datetime,
LAG(event) OVER (PARTITION BY login ORDER BY `datetime`) lag_event
FROM history )
SELECT login, SUM(TIMESTAMPDIFF(MINUTE, lag_datetime, `datetime`)) duration
FROM cte
WHERE (event, lag_event) = (2,1)
GROUP BY login;
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.id
FROM (table1 , table2)
LEFT JOIN table1 h ON table1.id = h.id
LEFT JOIN table2 s ON table2.id = s.id
SELECT *
FROM `users`
WHERE FIND_IN_SET(id, ?);