合并相关记录

Question

我有这个MySQL查询：

select day(ReservationDate) as ReservationDay, sum(SleighSeats) as SleighSeats, sum(CabSeats) as CabSeats, 0 as IsFullMoon, '' as OverrideText
from Reservations
where month(ReservationDate) = 2
group by ReservationDate
union
select day(CalendarDate) as ReservationDay, 0 as SleighSeats, 0 as CabSeats, IsFullMoon, OverrideText
from CalendarOverrides
where month(CalendarDate) = 2
group by CalendarDate
order by ReservationDay

预约表可以包含任何给定日历日期的任意数量的记录，也可以根本不包含任何记录。对于每个日历日期，CalendarOverrides表只能包含1条或0条记录。我正在尝试将结果合并在一起，但并不完全正确。

这是我的结果集：

​

​

预订第23天是问题所在。我希望这两条记录合并为1，这样5个值就是：

23、6、8、1、空白/空

澄清一下：

• ReservationDay位于两个表中( CalendarOverrides)
• SleighSeats中的名称为CalendarDate，CabSeats仅存在于保留table
• IsFullMoon中，而OverrideText仅存在于CalendarOverrides表

中

联合中的顶部查询已经合并了相同ReservationDay上的所有预订记录，但是对于相同的ReservationDay，如何将底部查询中的任何记录与顶部查询中的记录合并？(这样在任何一天都不会有超过1条记录。)

Answer 1

select 
    ReservationDay, 
    max(SleighSeats) as SleighSeats, 
    max(CabSeats) as CabSeats, 
    max(IsFullMoon) as IsFullMoon,
    max(OverrideText) as OverrideText
from (

    select 
        day(ReservationDate) as ReservationDay, 
        sum(SleighSeats) as SleighSeats, 
        sum(CabSeats) as CabSeats, 
        0 as IsFullMoon, 
        '' as OverrideText
    from Reservations
    where month(ReservationDate) = 2
    group by ReservationDate
    union
    select 
        day(CalendarDate) as ReservationDay, 
        0 as SleighSeats, 
        0 as CabSeats, 
        IsFullMoon, 
        OverrideText
    from CalendarOverrides
    where month(CalendarDate) = 2
    group by CalendarDate, IsFullMoon, OverrideText
) as subtbl 
group by ReservationDay
order by ReservationDay, IsFullMoon

Answer 2

select day(ReservationDate) as ReservationDay, sum(SleighSeats) as SleighSeats,
    sum(CabSeats) as CabSeats, COALESCE(IsFullMoon,0) as IsFullMoon,
    COALESCE(OverrideText,'') as OverrideText
from Reservations
left outer join CalendarOverrides 
    ON Reservations.ReservationDate = CalendarOverrides.CalendarDate
where month(ReservationDate) = 2
group by ReservationDate, IsFullMoon, OverrideText

union all

select day(CalendarDate) as ReservationDay, 0 as SleighSeats, 
    0 as CabSeats, IsFullMoon, 
    OverrideText
from CalendarOverrides
where month(CalendarDate) = 2
and NOT EXISTS
(
    SELECT 1 
    FROM Reservations 
    WHERE Reservations.ReservationDate = CalendarOverrides.CalendarDate
)

order by ReservationDay

如果您的关系型数据库管理系统支持它，那么您也应该能够在完全连接时执行此操作(由于where子句中的COALESCE？，这可能会更慢)：

select day(COALESCE(ReservationDate, CalendarDate)) as ReservationDay,
    COALESCE(sum(SleighSeats),0) as SleighSeats,
    COALESCE(sum(CabSeats),0) as CabSeats, COALESCE(IsFullMoon,0) as IsFullMoon,
    COALESCE(OverrideText,'') as OverrideText
from Reservations
full join CalendarOverrides 
    ON Reservations.ReservationDate = CalendarOverrides.CalendarDate
where month(COALESCE(ReservationDate, CalendarDate)) = 2
group by ReservationDate, CalendarDate, IsFullMoon, OverrideText
order by ReservationDay