Cоставить SQL запрос?

Question

[SkyRZN]

Добрый вечер! Помогите, пожалуйста, составить SQL запрос (SQL Server 2008)

На данный моммент есть следующее:

SELECT<br/>
 Data,<br/>
 Postav.Nazv,<br/>
 Schet.Shet,<br/>
 Schet.id,<br/>
 Schet.OtKogo,<br/>
 sum(Naimen.Kolvo*Naimen.Cena) AS SUMM,<br/>
 sum(Opl) AS SUMM2, <br/>
 (sum(Naimen.Kolvo*Naimen.Cena)-sum(Opl)) AS RAZN<br/>
FROM<br/>
 Schet,<br/>
 Postav,<br/>
 Naimen,<br/>
 Oplata<br/>
WHERE <br/>
 (Postav.id=Schet.OtKogo) AND <br/>
 (Naimen.Shet = Schet.id) AND<br/>
 (Oplata.Shet = Schet.id)<br/>
GROUP BY<br/>
 Schet.Data,<br/>
 Postav.Nazv,<br/>
 Schet.Shet,<br/>
 Schet.id,<br/>
 Schet.OtKogo<br/>

Проблема заключается в том что не удается нормально вывести 3 подсчитываемых поля.

В данном варианте при добавлении в таблицу Oplata каких либо значений поле SUMM в запросе начинает удваиваться.

Схема данных БД выглядит так:


Значения таблицы Naimen:


Значения таблицы Oplata:


Результат запроса в текущем состояния:


Хотя должны быть в идеале следующие значчения:

id SUMM SUMM2 RAZN

3 1250 0 1250

4 200 11111 10911

5 880 100 780

19 12321 100 12221

22 4653,68 0 4653,68

Как-то вот так вообщем. Заранее спасибо!!!

Answer 1

[wwi]

так и должно происходить
Вы выбираете строку для каждого товара выбираете строку из ОПЛАТА
и соответственно она суммируется столько раз, сколько у вас есть товарных строк.

Рекомендую для более удобного чтения писать запросы через JOIN

Если по счету может быть несколько оплат вам нужно использовать подзапросы
1. Определить счет и подзапросом итог поступлений закрывающих счет
2. определить его цену = сумму (итого по Naimen)
3. вывести результат

в реальных условиях, правильнее отслеживать счета закрытые оплатами
(как минимум по признаку «счет оплачен полностью»)

В вашей схеме — вы всегда проводите расчет по всей БД без ограничений…

ниже запрос который выполнить вашу задачу (не оптимизированный):

Select QR1.*,
sum(Naimen.Kolvo*Naimen.Cena) AS SUMM,
sum(Naimen.Kolvo*Naimen.Cena) — QR.Payment AS RAZN
FROM (
SELECT
SCHET.Data,
Postav.Nazv,
Schet.Shet,
Schet.id,
Schet.OtKogo,
( Select sum(OPLATA.Opl) from Naimen Where Naimen.Shet = Schet.id ) As Payment
FROM Schet
JOIN Postav ON Postav.id=Schet.OtKogo
JOIN Oplata ON Oplata.Shet = Schet.id
GROUP BY
Schet.Data,
Postav.Nazv,
Schet.Shet,
Schet.id,
Schet.OtKogo
) QR1
JOIN Naimen ON Naimen.Shet = Schet.id
GROUP BY QR1.Data,
QR1.Nazv,
QR1.Shet,
QR1.id,
QR1.OtKogo,
QR1.Payment

Answer 2

[Дмитрий Гукетлев]

Искренне хотел вам помочь на за 5 минут не понял чего вы всетаки хотите.
Думаю проблема в том, что вам надо исподьзовать join'ы и джойнить все к счету, это должно решить проблему дублей.

А вообще названия полей вырвиглазные, читать неприятно. За одно Schet.shet вас тот кто будет поддерживать этот код возненавидит, уж используйте одно название.

И вообще начните с чтения книг, это скучно но полезно.

Answer 3

[Антон Пронин]

Где то так )

SELECT
  sc.data, 
  SUM(n.Kolvo * n.Cena) itogo,
  SUM(n.Kolvo * n.Cena)-IFNULL(o.opl,0) ostatok,
  o.opl,
  p.Nazv,
  sc.id
FROM
  habr.naimen n
  LEFT JOIN
  habr.schet sc
  ON sc.id = n.Schet
  LEFT JOIN habr.postav p
  ON p.id = sc.OtKogo
  LEFT JOIN habr.oplata o
  on o.Schet = sc.id
GROUP BY
  n.Schet

Comments

[SkyRZN]

IFNULL не знает.
И не совсем то, поле o.opl может содержать тоже несколько значений относящихся к одному счету, тоесть оплата по частям.

Answer 4

[wwi]

PS: последний QR1.Payment — лишний… он бессмысленный

Comments

[SkyRZN]

Все хорошо, только ругается на 3-ю строку: «Не удалось привязать составной идентификатор „QR.Payment“» и на последний JOIN «Не удалось привязать составной идентификатор „Schet.id“» В чем может быть проблема?

[wwi]

это моя опечатка необходимо поправить = QR1.Payment

= QR1.

[wwi]

последний JOIN «Не удалось привязать составной идентификатор „Schet.id“» В чем может быть
--> QR1.Id

[SkyRZN]

Так и сделал, теперь сообщение следующее: «Вложенный запрос вернул больше одного значения. Это запрещено, когда вложенный запрос следует после =, !=, <, <=, >, >= или используется в качестве выражения.»

Answer 5

[SkyRZN]

Хочется чтобы к счету который в таблиц Shet выводилась его сумма, сколько уже оплачено по нему и сколько оставалось оплатить, согласно формул:
Сумма всех наименований (кол-во * цену)
Сумма всех платежей
Разность общей суммы товаров и сколько уже оплачено.

Тоесть счет состоит из нескольких наименований, у которых прописана цена и кол-во, перемножаем цену и кол-во и складываем все наименования относящиеся только к этому счету.
Тоже самое и с оплатой, в итоге все это выводим.

Answer 6

[SkyRZN]

В частности вот кусок который прекрасно работает, но он подсчитывает только одно поле «Сумму», а надо чтобы еще появилось поле оплата, значения которое в другой таблички но всё по аналогии с этой.
SELECT
Data,
Postav.Nazv,
Schet.Shet,
Schet.id,
Schet.OtKogo,
sum(Naimen.Kolvo*Naimen.Cena) AS SUMM
FROM
Schet
LEFT OUTER JOIN Postav ON Postav.id=Schet.OtKogo
LEFT OUTER JOIN Naimen ON Naimen.Shet = Schet.id
LEFT OUTER JOIN Oplata ON Oplata.Shet = Schet.id
GROUP BY
Schet.Data,
Postav.Nazv,
Schet.Shet,
Schet.id,
Schet.OtKogo

Comments

[wwi]

этот запрос принципиально некорректен.
Еще раз прочитайте мое сообщение: вы для каждого товара в таблице Naimen добавляете общую сумму по счет… более того.., если у вас будет оплата счета несколькими денежными поступлениями, то все еще раз умножится на количество оплат.

оператор select из нескольких таблиц это по сути операция умножения строк соотвествующих условиям выборки.

[SkyRZN]

Сообщение просто написал раньше чем успел прочесть Ваше(
Спасибо что помогаете!