Помогите с запросом

[^D^ima]

SQL server 2014 + SQL management studio.

Запрос:

SELECT     Payment.Number, Payment.Date, PaymentDetail.ID AS PaymentDetailID, PaymentDetail.Value, Invoice.FileAs, Invoice.Number AS Expr1, Project.FileAs AS Expr2
FROM         PaymentDetail INNER JOIN
                      Payment ON PaymentDetail.PaymentID = Payment.ID INNER JOIN
                      Invoice ON PaymentDetail.InvoiceID = Invoice.ID INNER JOIN
                      Project ON Invoice.ProjectID = Project.ID INNER JOIN
                      DocumentCategory ON Project.ID = DocumentCategory.DocumentID INNER JOIN
                      Category ON DocumentCategory.CategoryID = Category.ID
ORDER BY Payment.Date

Проблема:
В таблице DocumentCategory присутствуют не все значения из таблицы Project, следовательно итоговая таблица усекается до не пустых вхождений(с 1000 до 700)

Вопрос:
Как запрос построить таким образом, что если DocumentCategory.DocumentID не найден, то вместо него(ну и дальше в Category) просто писалось null и все(т.е. число записей осталось 1000).

Сообщение отредактировано: Akina - 16.07.18, 12:06

[Akina]

 

LEFT JOIN DocumentCategory

[^D^ima]

Бинго!!!

Akina
Не напомнишь, как называется функция объединения(суммирования) строк?

1	СИНИЙ
2	КРАСНЫЙ
3	СИНИЙ

суммировались в

4	СИНИЙ
2	КРАСНЫЙ

[Akina]

Цитата ^D^ima @ 06.09.17, 17:28

как называется функция объединения(суммирования) строк?

Это шутка такая? у тебя суммируются числа, а не строки. SUM() ... А по строке выполняется группировка. GROUP BY ...

Сообщение отредактировано: Akina - 06.09.17, 17:37

[^D^ima]

Ошибка:

SELECT     /*Payment.Date, Payment.FileAs AS Payment,*/ Payment.TotalWithVATBC, /*Project.FileAs AS Project,*/ Category.FileAs AS Category
FROM         PaymentDetail INNER JOIN
                      Payment ON PaymentDetail.PaymentID = Payment.ID INNER JOIN
                      Invoice ON PaymentDetail.InvoiceID = Invoice.ID INNER JOIN
                      Project ON Invoice.ProjectID = Project.ID LEFT OUTER JOIN
                      DocumentCategory ON Project.ID = DocumentCategory.DocumentID LEFT OUTER JOIN
                      Category ON DocumentCategory.CategoryID = Category.ID
WHERE     (Payment.FileAs NOT LIKE N'%-1%') AND (Payment.FileAs NOT LIKE N'%-3%') and (Payment.Date between '01.01.2017' and '31.12.2017' )
/*ORDER BY Payment.Date*/
GROUP BY Payment.TotalWithVATBC

Сообщение 8120, уровень 16, состояние 1, строка 1
Столбец "Category.FileAs" недопустим в списке выбора, поскольку он не содержится ни в агрегатной функции, ни в предложении GROUP BY.

Добавлено 06.09.17, 18:46
Мне что-то типа такого нужно:

GROUP BY Category.FileAs,sum(Payment.TotalWithVATBC)

Чтобы для одинаковых Category.FileAs суммировались их Payment.TotalWithVATBC

[^D^ima]

Так получилось, всем спасибо.

SELECT     /*Payment.Date, Payment.FileAs AS Payment,*/ sum(Payment.TotalWithVATBC), /*Project.FileAs AS Project,*/ Category.FileAs AS Category
FROM         PaymentDetail INNER JOIN
                      Payment ON PaymentDetail.PaymentID = Payment.ID INNER JOIN
                      Invoice ON PaymentDetail.InvoiceID = Invoice.ID INNER JOIN
                      Project ON Invoice.ProjectID = Project.ID LEFT OUTER JOIN
                      DocumentCategory ON Project.ID = DocumentCategory.DocumentID LEFT OUTER JOIN
                      Category ON DocumentCategory.CategoryID = Category.ID
WHERE     (Payment.FileAs NOT LIKE N'%-1%') AND (Payment.FileAs NOT LIKE N'%-3%') and (Payment.Date between '01.01.2017' and '31.12.2017' )
 
/*ORDER BY Payment.Date*/
GROUP BY Category.FileAs

[Akina]

Цитата ^D^ima @ 06.09.17, 20:28

Так получилось

Мне неизвестна физическая сущность хранящихся в таблицах сведений, но создаётся впечатление, что запрос избыточен. Как я понимаю, не может быть такого, чтобы в DocumentCategory присутствовала запись о CategoryID, к которой нет соответствующей записи в таблице Category. Но если так, то нет смысла связывать таблицу Category левым связыванием, достаточно внутреннего. А если поле Category.ID уникально (а на показанной схеме это вообще первичный ключ) - то таблицу Category вообще можно удалить из списка таблиц-источников запроса.

[^D^ima]

Akina
Спасибо за совет.

А можно так сделать, чтобы Category.FileAs AS Category объединяло все найденные варианты в 1 строку.

Я имею в виду что если для Project.ID найдется несколько DocumentCategory.DocumentID и Category.ID чтобы эти строки в 1 объединились?

Документ	Категория
1	Категория 1
1	Категория 2

переделалось в

Документ	Категория
1	Категория 1, Категория 2

[Akina]

Цитата ^D^ima @ 07.09.17, 10:02

А можно так сделать, чтобы Category.FileAs AS Category объединяло все найденные варианты в 1 строку.

У Вас MS SQL. Если версия позволяет, то можете использовать STRING_AGG. Иначе разве что через FOR XML.

Сообщение отредактировано: Akina - 07.09.17, 11:04

[^D^ima]

помогите отформатировать число.

Я хочу чтобы сотые отделялись запятой(12345,67)
А то получается что SQL management studio выдает корректно, с запятой, а при обработке того-же запроса через sqlcmd получается точка
Я написал так:

Format( sum(PaymentDetail.Value) , '#,00') AS Сумма

но почему-то дробную часть вообще отсекает

Добавлено 12.09.17, 15:38
или придется переводить в строку и делать replace?

[^D^ima]

Вместо

sum(PaymentDetail.Value) AS Сумма

Стало:

SELECT   replace( CAST(  sum(PaymentDetail.Value) as varchar(20) ) ,'.',',')AS Сумма

Так заработало, но как-то череопно выглядит. PaymentDetail.Value имеет тип данных money

[Akina]

Цитата ^D^ima @ 12.09.17, 14:42

SQL management studio выдает корректно, с запятой

Потому что SSMS - это средств отображения, которое форматирует результат, используя установленный системный или настраиваемый разделитель/формат.

Цитата ^D^ima @ 12.09.17, 14:42

при обработке того-же запроса через sqlcmd получается точка

А sqlcmd отдаёт результат as is, используя стандартный разделитель.

Цитата ^D^ima @ 12.09.17, 18:12

Так заработало, но как-то череопно выглядит.

Попробуй

Format( sum(PaymentDetail.Value) , N'#.00', N'ru-ru') AS Сумма

[^D^ima]

[^D^ima]

Цитата Akina @ 12.09.17, 18:29

N

Что означает N в формате?

[Akina]

Цитата ^D^ima @ 13.09.17, 10:41

Что означает N в формате?

Если посмотреть описание функции Format, то увидишь. что второй и третий параметры имеют тип Nvarchar, а не просто varchar.