Как правильно составить SQL запрос?

Question

[Дмитрий]

Никак не могу составить правильный SQL-запрос. Помогите пожалуйста! Есть две таблицы: таблица счетов и таблица операций по счету. Между таблицами есть связь "один ко многим". Т.е. один счет может быть связан с многими операциями из таблицы операций. У каждого счета уникальный идентификатор count_id, а у каждой операции есть поле, указывающее на то к какому счету привязана данная операция, т.е. поле operation_count_id.
В таблице операций у каждой операции есть такое поле как "остаток" (balance), т.е. это сумма, которая оставалась на счете после проведения данной операции. Получается что баланс счета равен остатку после последней операции. Т.е., грубо говоря, чтобы знать баланс на счете, нужно получить для него этот баланс из поля balance последней сделанной операции.
Если делать так

SELECT * FROM counts c LEFT JOIN operation o ON (c.count_id=operation_count_id)

то, естественно, в результате будет много строк, т.е. количество равное количеству операций по каждому счету. Но мне необходимо присоединить к каждому счету только одну операцию, последнюю. Последнюю операцию можно определить как операцию с максимальным значением даты. Т.е. фактически нужно в списке счетов к каждому счету присоединить запись с max(operation_date) и count_id=operation_count_id из таблицы операций. Как это сделать - ума не приложу.

Answer 1

[nozzy]

Не проверял:

select
c.count_id,
t.balance
from counts c 
left join 
(
    select  o.operation_count_id,  o.balance   
    from operation o 
	  where o.date_count = (select max(date_count) 
									from operation 
									where operation_count_id = o.operation_count_id)
    
) t on t.operation_count_id = c.count_id

Comments

[Дмитрий]

Я в шоке, но ваш вариант работает! )) Сейчас буду разбираться!

[nozzy]

Дмитрий:
Рад. Можно заменить left join на inner join, если не нужны count_id без balance.

Answer 2

[Котик Антон]

stackoverflow.com/questions/612231/how-can-i-selec...

Answer 3

[Дмитрий Ковальский]

Есть еще вариантик. Сделать 2 запроса. В первый - во временную таблицу сохранить результат запроса типа

select countId, MAX(created) from operations GROUP BY countId

А вторым запросом делать ваш select но джойнить эту временную таблицу, а не всю таблицу операций.

Comments

[Дмитрий]

Для работы с SQL в данном случае я использую функцию php mysql_query и ей можно передать только один запрос. Как мне выполнить два запроса, да еще первым создав временную таблицу?

[Дмитрий Ковальский]

Дмитрий: Stored Procedure

[Дмитрий Ковальский]

Дмитрий: Я вам на всякий случай уточню. Если сегодня ваша бизнес-логика ограничивается исполнением одного скрипта, то со временем может появиться ситуация, когда нужно исполнить цепочку запросов. Вы конечно можете исполнить 3-4 mysql_query, но порой проще и быстрее 1 раз дернуть хранимку

[Дмитрий]

Дмитрий Ковальский: Я вас понял, спасибо!

Answer 4

[igruschkafox]

-- Создали таблицы
CREATE TABLE counts (count_id INT IDENTITY (1,1) , count_name nVARCHAR(20))
CREATE TABLE operation  (operation_id INT IDENTITY (1,1) ,operation_count_id INT, balance MONEY,operation_date DATETIME ,operation_name NVARCHAR(50))

go
-- Очищаем таблицы
TRUNCATE TABLE counts
TRUNCATE TABLE operation

-- Заполняем данным
INSERT INTO [dbo].[counts] ([count_name]) VALUES ('Первый счет')
INSERT INTO [dbo].[counts] ([count_name]) VALUES ('Второй счет')
INSERT INTO [dbo].[counts] ([count_name]) VALUES ('Третий счет')

INSERT INTO [dbo].[operation]([operation_count_id],[balance],[operation_date],[operation_name])VALUES(1,'10.3','01-01-2015','Первая операция по первому счету')
INSERT INTO [dbo].[operation]([operation_count_id],[balance],[operation_date],[operation_name])VALUES(1,'25.5','02-02-2015','Вторая операция по первому счету')
INSERT INTO [dbo].[operation]([operation_count_id],[balance],[operation_date],[operation_name])VALUES(1,'34.4','03-03-2015','Третья операция по первому счету')

INSERT INTO [dbo].[operation]([operation_count_id],[balance],[operation_date],[operation_name])VALUES(2,'11.7','01-01-2015','Первая операция по Второму счету')
INSERT INTO [dbo].[operation]([operation_count_id],[balance],[operation_date],[operation_name])VALUES(2,'22.2','04-04-2015','Вторая операция по Второму счету')

INSERT INTO [dbo].[operation]([operation_count_id],[balance],[operation_date],[operation_name])VALUES(3,'33.1','01-01-2015','Первая операция по Тертья счету')
INSERT INTO [dbo].[operation]([operation_count_id],[balance],[operation_date],[operation_name])VALUES(3,'33.7','02-02-2015','Вторая операция по Третья счету')


-- Запрос возвращает баланс последней операции по каждому счету
SELECT c.count_id,c.count_name
,o2.balance
,o2.operation_name
 FROM counts c
 JOIN (
SELECT operation_count_id,
 MAX(operation_date)  AS 'Max Date'
FROM dbo.operation 
GROUP BY operation_count_id

) o ON o.operation_count_id=c.count_id
JOIN dbo.operation o2 ON o2.operation_count_id=o.operation_count_id AND o2.operation_date=o.[Max Date]

Answer 5

[Руслан Федосеев]

SELECT * FROM counts c LEFT JOIN operation o ON (c.count_id=operation_count_id) order by operation_time desc limit 1
примерно так.

Answer 6

[AlikDex]

SELECT * 
FROM counts AS c 
LEFT JOIN operation AS o ON (c.count_id=(
   SELECT operation_count_id 
   FROM operation 
   ORDER by date
   LIMIT 1) o
)

В случае, если ид = всегда последняя дата (т.е. порядок никогда не нарушается) можно сделать так:

SELECT * 
FROM counts AS c 
LEFT JOIN operation AS o ON (c.count_id=(
   SELECT MAX(operation_count_id) 
   FROM operation) o
)

upd: скобочку неправильно поставил.

Comments

[Pavel]

select from select from select,а потом не поймёшь ничего...плохая практика

[AlikDex]

Павел Тананыхин: предложите свой вариант.
И вообще, вы что, против подзапросов? Комментируйте хорошо тогда, чтобы понимать в будущем.

[Pavel]

AlikDex: Меня учили что подзапросы это плохо,и надо пользоваться временными таблицами.В плане читаемости кода,временные таблицы намного нагляднее.Комментировать это да,но в данном примере даже вы пишете FROM counts AS c ,а если сджойнить ещё таблиц 10,то будет сс,сс2,или целый алфавит.Ненаглядно же.

[AlikDex]

Павел Тананыхин: если вам нужно джойнить 10 таблиц, то следует пересмотреть архитектуру базы данных.
Предложите ваш вариант с временными таблицами.