MySQL加入帮助

Question

我已经为这个查询挣扎了几天了。我正在使用PHP/MySQL。

这是一个系统，在这个系统中，客户可以通过我正在为之开发的公司为托运或送货发送货物。我试图编写的查询将收集为客户执行的收集和交付，并向他们发送当天所花时间的摘要。

我有两个结果集，下面是我需要“联合起来”，以便发送报告。

(不要担心细节，问题在于连接。)

交付结果集

此结果是公司代表所列客户交付的交货总额的列表(按日分组)。

summary_day, num_cons, num_spaces, customer_code 
2011-07-20      1       3           COB001P
2011-07-20      1       3           FAI001P
2011-07-20      2       2           FRE001P
2011-07-20      2       2           MIN001P
2011-07-20      17      29          NOR001P
2011-07-20      50      79          PAL001P
2011-07-20      1       1           PAR001P
2011-07-20      1       1           POT002P
2011-07-20      6       7           RHY001P
2011-07-20      9       13          TDG001P
2011-07-20      18      23          UPN001P

集合结果集

与上述类似，但相反，这个结果是公司代表每个客户收集的总额列表。

2011-07-20  9   15  ARR001P
2011-07-20  1   1   BAC002P
2011-07-20  1   1   BLA001P
2011-07-20  4   6   CAR003P
2011-07-20  2   2   DIS001P
2011-07-20  2   2   DOV001P
2011-07-20  1   1   DRY001P
2011-07-20  1   1   ECC001P
2011-07-20  3   5   FAI001P
2011-07-20  2   2   INV001P
2011-07-20  2   2   MIN001P
2011-07-20  3   3   PAL001P
2011-07-20  1   1   QUA002P
2011-07-20  1   1   TEC002P
2011-07-20  1   1   THE006P
2011-07-20  7   7   WIL005P

问题

我很难使用联接来合并这两个结果集。

理想情况下，最终结果集应该是相当标准的，列为：

summary_day, customer_code, num_deliveries, num_collections

number字段将是来自每个结果集的num_spaces列。如果客户既有收集记录，也有送货记录，则同时显示这两个数字。如果它们有一个列而不是另一个列，那么我使用合并来期望一个列为NULL，并相应地将其设置为0。

我尝试在customer_code字段上使用一个正确的外部联接，希望这样会产生预期的结果，但我得到的唯一结果是：

2011-07-20, ARR001P, 0, 15
2011-07-20, BAC002P, 0, 1
2011-07-20, BLA001P, 0, 1
2011-07-20, CAR003P, 0, 6
2011-07-20, DIS001P, 0, 2
2011-07-20, DOV001P, 0, 2
2011-07-20, DRY001P, 0, 1
2011-07-20, FAI001P, 3, 5
2011-07-20, INV001P, 0, 2
2011-07-20, MIN001P, 2, 2
2011-07-20, PAL001P, 79, 3
2011-07-20, QUA002P, 0, 1
2011-07-20, TEC002P, 0, 1
2011-07-20, THE006P, 0, 1
2011-07-20, WIL005P, 0, 7

正如您所看到的，结果集似乎只返回有集合的客户，这是可以的，但我也需要看到那些有交付而没有集合的客户。

例如，客户NOR001P就是一个例子，它位于交付结果集中，而不是集合结果集.

在这种情况下是否需要一个完全的外部连接？如果是这样的话，考虑到MySQL不支持完全外部连接，我如何解决这个问题？

谢谢你抽出时间阅读。

全解

感谢CResults的回答，完整的解决方案如下..。正如您所看到的，传递和收集结果集实际上是由子查询组成的，所以这有点让人头疼！

set @summary_day = '2011-07-20';
select summary_day, customer_code, sum(num_deliveries) as pallet_deliveries, sum(num_collections) as pallet_collections
from
(
        select d.summary_day, d.customer_code, d.num_spaces as num_deliveries, 0 as num_collections from
            (select 
                        @summary_day as summary_day, /* change variable */
                        count(*) as num_cons,
                        sum( coalesce(micro_qty,0) + coalesce(quarter_qty,0) + coalesce(half_qty,0) + coalesce(full_qty,0) + coalesce(ceil(vlu_qty),0) ) as num_spaces,
                        pc.customer_code 
                    from pallet_routes pr
                    inner join pallet_consignments pc on pc.route_id = pr.route_id
                    where pr.date = @summary_day /* today */
                    and pc.type = 'D'
                    group by customer_code
            ) as d
        union all
        select c.summary_day, c.customer_code, 0 as num_deliveries, num_spaces as num_collections from
            (select 
                    @summary_day as summary_day, /* change variable */
                    count(*) as num_cons,
                    sum(coalesce(micro_qty,0) + coalesce(quarter_qty,0) + coalesce(half_qty,0) + coalesce(full_qty,0) + coalesce(ceil(vlu_qty),0)) as num_spaces,
                    pc.customer_code 
                from pallet_routes pr
                inner join pallet_consignments pc on pc.route_id = pr.route_id
                where pr.date = DATE_SUB(@summary_day, INTERVAL 1 DAY) /* Yesterday */
                and pc.type = 'C'
                group by customer_code
            ) as c
) as pallet_summaries
group by summary_day, customer_code

Answer 1

完全没有经过测试，但试试看

Select 
    Date, CustCode, Sum(Num_Cons) as Num_Cons, Sum(Num_Cols) as Num_Cols
from 
(    Select Date, CustCode, Num_Cons, 0 as Num_Cols From Consignments
    UNION ALL
    Select Date, CustCode, 0 as Num_Cons, Num_Cols From Collections
) parcels
group by Date, CustCode

Answer 2

SELECT D.summary_day
     , D.customer_code
     , D.num_deliveries
     , COALESCE(C.num_collections, 0) AS num_collections
FROM Deliveries AS D
  LEFT JOIN Collections AS C
    ON D.customer_code = C.customer_code
    AND D.summary_day = C.summary_day

UNION ALL

SELECT C.summary_day
     , C.customer_code
     , 0
     , C.num_collections
FROM Deliveries AS D
  RIGHT JOIN Collections AS C
    ON D.customer_code = C.customer_code
    AND D.summary_day = C.summary_day
WHERE D.summary_day IS NULL

ORDER BY summary_day
       , customer_code