用左外连接分组，排除空值

Question

我有一个订单表和一个包含付款信息的行程表。这是一个多对多的关系——订单可以分成许多次旅行，一次旅行可以有几个订单的付款信息，有时是一个订单。Trips表中没有"零"记录，因此以该值作为键的左连接将返回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.t trip_nbr不是NULL")-但我不想在我的分组中得到2条记录-一个为t.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记录正在创建重复项。

Answer 1

应该可以:

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;

Answer 2

您可以使用像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

Answer 3

如果将空值转换为零，则对给定order_id的"trip_nbr"one_answers"sum"求和。这难道不能解决你的挑战吗?

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