Группировать с помощью левого внешнего соединения, исключать нули

У меня есть таблица заказов и таблица поездок, которая включает в себя информацию о платеже. Это очень много отношений – заказ может разделиться во многих поездках, и одна поездка может иметь платежную информацию для нескольких заказов, а иногда и заказа. В таблице Trips нет записи «Zero», поэтому левое соединение с этим значением, так как ключ возвращает NULL-запись. Я использую SQL 2012

Order table +----+----------+--------------+ | order_id | trip_nbr | veh_id | +----+----------+--------------+ | 1 | 12 | 3 | | 2 | 22 | 6 | | 2 | 0 | 8 | | 4 | 25 | 7 | | 7 | 0 | 11 | +----+----------+--------------+ Trips table +----+------------+--------------+ | trip_nbr | payment | veh_id | +----+------------+--------------+ | 12 | 20.00 | 3 | | 22 | 123.00 | 6 | | 22 | 12.50 | 6 | | 25 | 133.33 | 7 | +----+------------+--------------+ 

Вот мой запрос:

  select o.order_id, t.trip_nbr, sum(t.payment_amt) from orders o left outer join trips t on o.trip_nbr = t.trip_nbr group by o.order_id, t.trip_nbr 

Результаты:

 +----+----------+--------------+ | order_id | trip_nbr | sum | +----+----------+--------------+ | 1 | 12 | 20.00 | | 2 | 22 | 135.50 | | 2 | NULL | NULL | | 4 | 25 | 133.33 | | 7 | NULL | NULL | +----+----------+--------------+ 

Проблема в том, что я получаю много информации из таблицы заказов и только информацию о платежах из таблицы Trips. Поэтому я не хочу исключать любые записи заказов (что произойдет, если я добавлю предложение «WHERE t.trip_nbr NOT NOT»), но я не хочу получать 2 записи в моей группе – один для t.trip_nbr это NULL и тот, где он находит совпадение.

Желаемые результаты:

 +----+----------+--------------+ | order_id | trip_nbr | sum | +----+----------+--------------+ | 1 | 12 | 20.00 | | 2 | 22 | 135.50 | | 4 | 25 | 133.33 | | 7 | NULL | NULL | +----+----------+--------------+ 

Я хочу, чтобы несогласованная запись order_id = 2 была «суммирована» – но сохраните одиночную запись для order_id = 7. Причина в том, что эта таблица позже соединяется с другой таблицей, а дополнительные NULL-записи создают дубликаты.

Это должно работать:

 WITH orders2 AS ( SELECT *, N = SUM(CASE WHEN trip_nbr <> 0 THEN 1 ELSE 0 END) OVER(PARTITION BY order_id) FROM orders ) SELECT o.order_id, t.trip_nbr, SUM(t.payment_amt) FROM orders2 o LEFT OUTER JOIN trips t ON o.trip_nbr = t.trip_nbr WHERE N = 0 OR (N > 1 AND o.trp_nbr <> 0) GROUP BY o.order_id, t.trip_nbr; 

Вы можете использовать функцию окна, такую ​​как RANK чтобы идентифицировать лишние NULL значные записи и отфильтровывать их во внешнем запросе:

 select order_id, trip_nbr, total_payment from ( select o.order_id, t.trip_nbr, sum(t.payment) as total_payment, rank() over (partition by order_id order by case when t.trip_nbr IS NULL then 2 else 1 end) as rnk from orders o left outer join trips t on o.trip_nbr = t.trip_nbr group by o.order_id, t.trip_nbr) as t where t.rnk = 1 

Если вы конвертируете свои нули в ноль, то суммируйте «trip_nbr» и «sum» для данного order_id. Разве это не решит ваш вызов?

 create table #Order (Order_Id int , Trip_nbr int , Veh_id int ) Create Table #Trips (trip_nbr int , Payment Numeric(13,2), Veh_id int ) insert into #Order (Order_id, Trip_nbr, Veh_id) values (1,12,3) insert into #Order (Order_id, Trip_nbr, Veh_id) values (2,22,6) insert into #Order (Order_id, Trip_nbr, Veh_id) values (2,0 ,8) insert into #Order (Order_id, Trip_nbr, Veh_id) values (4,25,7) insert into #Order (Order_id, Trip_nbr, Veh_id) values (7,0,11) insert into #Trips (trip_nbr, Payment, Veh_id) values (12, 20.00 , 3 ) insert into #Trips (trip_nbr, Payment, Veh_id) values (22, 123.00,6 ) insert into #Trips (trip_nbr, Payment, Veh_id) values (22, 12.50 , 6 ) insert into #Trips (trip_nbr, Payment, Veh_id) values (25, 133.33 , 7 ) select Order_id, trip_nbr = sum(trip_nbr), Payment = sum(payment) from ( select o.order_id, t.trip_nbr, Payment = sum(t.Payment) from #order o left outer join #trips t on t.trip_nbr = o.trip_nbr -- left outer join #order o on t.trip_nbr = o.trip_nbr group by o.order_id, t.trip_nbr ) x group by Order_id order by Order_id 
Interesting Posts

Как отслеживать, сколько раз столбец менял свое значение?

Как принимать идентификационные номера из таблицы SQL Server с помощью Visual C #

Типы структуры в T-SQL

Зачем кому-то использовать управление диапазонами идентификаторов с SQL Server

Слияние SQL Server с футляром?

Значения значений столбца REAL за пределами документированного диапазона

Каков в настоящее время способ получения данных с SQL Server с использованием C # 4.0?

Есть ли альтернатива объединениям для повышения производительности?

Разграниченный текст полуколона в SQL с накопленным счетом

В SQL как не выбирать строки, где значение столбца такое же, как в строке выше

Vb.net округление неубедительность

Объединение функций LTRIM и RTIM и REPLACE в MSSQL

выберите строку, основанную на определенном условии

Разделение значения по количеству дней в месяц в поле даты в таблице SQL

который более эффективен в sql min или top

Давайте будем гением компьютера.