代码之家 › 专栏 › 技术社区 › ihatemash

确定可以合并哪些行的SQL查询

sql-server-2008 sql-server sql

ihatemash · 技术社区 · 7 年前

我们有正在注册活动的客户。注册有两种类型:“基本包”和“完整包”。基本包仅包括注册人员。每增加一位客人都要额外付费。整套服务包括2名成年人(其中一名为注册者)和2名儿童。那么每增加一位客人都要额外付费。每个客户都可以添加任意数量的基本注册和完整注册。

我的任务是编写一个查询,该查询将评估每个客户的所有注册,并返回客户的ID,如果他们有任何注册可以组合以降低其总成本。我们有两个表来维护这些数据-一个表用于每个注册,另一个表用于每个注册的其他来宾。请参阅 DB-Fiddle 数据结构示例。

在示例数据中,有3个客户“00001”、“00002”和“00003”。对于示例数据,我的查询应该返回“00001”和“00002”的客户Id,而不是“00003”。

客户“00001”有3个注册(一个基本注册和两个完整注册)。吉姆·史密斯(JimSmith)的第一次正式注册已经包含2名成年人和2名儿童。然而,第二次正式注册(Pam Jones)只有1名成年人和2名儿童。因此,John Doe的基本注册是不必要的,因为John Doe可以添加为Pam Jone注册的第二个成年人。

客户“00002”拥有Walter Mann和Paula Wilson的两个完整注册。Walter的注册记录中包含包裹随附的两名成人和两名儿童。但也包括额外的成人和儿童——每一项都是额外的费用。然而,Paula Wilson的注册仅包括一名成年人和一名儿童。所以,沃尔特额外的成人和孩子可以转移到葆拉的登记处*注意-我的数据没有反映这一点。但如果Walter的注册包含的成人和/或儿童数量超过Paula的注册所能容纳的数量,则仍将标记,因为通过填补尽可能多的可用空缺,仍将降低客户00002的总成本。

客户“00003”有两个注册-一个基本注册和一个完整注册。阿尔伯特·帕尔默的基本注册费用包括额外的成人和儿童。然而,道尔顿·埃文斯的第二次注册已经包含两名成年人和两名儿童,因此,无法降低客户的总体成本。

因此,如前所述,我需要一个查询来评估所有客户的所有注册,并返回客户Id(如果他们的注册可以优化)。我无法理解这种SQL级别。因此,对此提供的任何帮助都将不胜感激。

2 回复 | 直到 7 年前

shawnt00 7 年前

您至少需要为每对(或部分)成人(儿童)进行注册。如果您的注册数量超过了这两个数字的要求,那么它们可以合并。

select r.customerid
from Registrations r left outer join RegistrationAdditionalGuestDetails g
    on g.registrationid = r.registrationid
group by r.customerid
having
    count(distinct r.registrationid) >
      ceiling(
        (
          count(distinct r.registrationid) /* non-guest adults */ +
          count(case when guesttype like '%Adult%' then 1 end)
        ) / 2.0
      )
    or
      ceiling(count(case when guesttype like '%Child%' then 1 end) / 2.0)
        between 1 and /* children are optional */
          count(distinct case when r.registrationtype = 'Full' then r.registrationid end);

如果包定义发生更改,则可以根据需要轻松调整计算。此外,我假设只需两个成年人的完全注册就比两个基本注册好。如果这是错误的,我仍然想了解更多所需合并规则的参数。

Joe Farrell 7 年前

有趣的问题。为了让这更容易理解,我将使用一个临时表来说明可能的解决方案。如果它对您有效,并且您不希望表出现在那里,那么您应该能够稍微折叠查询。以下是我想知道的关于每个预订的信息:

declare @RegistrationDetail table
(
    RegistrationId int,
    CustomerId varchar(5),
    AdultsIncluded int,
    ChildrenIncluded int,
    AdultsRemaining int,
    ChildrenRemaining int
);

前两个字段将直接从 dbo.Registrations 桌子 AdultsIncluded 和 ChildrenIncluded 将分别给出每个保留地上已经出现的成年人和儿童的总数。 AdultsRemaining 和 ChildrenRemaining 将分别给出保留地中仍可能增加的成人和儿童人数,而无需支付额外费用。因此,对于完整注册,剩余计数将为2减去已使用的预订数量,而对于基本预订,剩余计数将始终为零。

with GuestCountCTE as
(
    select
        Reg.RegistrationId,
        AdultsIncluded = 1 + sum(case when Guest.GuestType in ('Adult Included', 'Additional Adult') then 1 else 0 end),
        ChildrenIncluded = sum(case when Guest.GuestType in ('Child Included', 'Additional Child') then 1 else 0 end)
    from
        dbo.Registrations Reg
        left join dbo.RegistrationAdditionalGuestDetails Guest on Reg.RegistrationId = Guest.RegistrationId
    group by
        Reg.RegistrationId
)
insert @RegistrationDetail
select
    Reg.RegistrationId,
    Reg.customerId,
    GCount.AdultsIncluded,
    GCount.ChildrenIncluded,
    AdultsRemaining = case when Reg.RegistrationType = 'Full' and GCount.AdultsIncluded < 2 then 2 - GCount.AdultsIncluded else 0 end,
    ChildrenRemaining = case when Reg.RegistrationType = 'Full' and GCount.ChildrenIncluded < 2 then 2 - GCount.ChildrenIncluded else 0 end
from
    dbo.Registrations Reg
    inner join GuestCountCTE GCount on Reg.RegistrationId = GCount.RegistrationId;

CTE建立已使用的预订数量,以及 INSERT 下面的语句直接从CTE获取数据,然后推断出剩余的保留数量。这里需要注意以下几点:

你当然可以在没有CTE的情况下做到这一点。我使用了一个,只是为了进一步细分该方法,避免编写表达式 成人包括在内 和 包括儿童 每次两次。
我在这里假设,如果没有使用“包含成人”的完整注册,或者没有使用“包含儿童”的完整注册,就不会有“包含其他成人”的完整注册。如果不是这样,那么尽管整体方法仍然有效,您需要更明智地确定每个预订中剩余的可用空间。
请注意 1 添加到 成人包括在内 表示这代表了首先注册的成年人。

这是我现在的数据 @RegistrationDetail :

RegistrationId   CustomerId   AdultsIncluded   ChildrenIncluded   AdultsRemaining   ChildrenRemaining
1                00001        1                0                  0                 0
2                00001        2                2                  0                 0
3                00001        1                2                  1                 0
4                00002        3                3                  0                 0
5                00002        1                1                  1                 1
6                00003        2                1                  0                 0
7                00003        2                2                  0                 0

因此,现在我需要的是至少有一个预订的任何客户的标识符,其中出现在该预订上的成人和儿童数量小于或等于该客户所有另外保留。这其实并不难:

with RemaindersByCustomerCTE as
(
    select
        Detail.CustomerId,
        AdultsRemaining = sum(Detail.AdultsRemaining),
        ChildrenRemaining = sum(Detail.ChildrenRemaining)
    from
        @RegistrationDetail Detail
    group by
        Detail.CustomerId
)
select
    Rem.CustomerId
from
    RemaindersByCustomerCTE Rem
where
    exists
    (
        select 1
        from
            @RegistrationDetail Detail
        where
            Detail.AdultsIncluded <= (Rem.AdultsRemaining - Detail.AdultsRemaining) and
            Detail.ChildrenIncluded <= (Rem.ChildrenRemaining - Detail.ChildrenRemaining)
    );

在这里,我首先使用CTE生成一个结果集,其中每个客户都有一条记录,以及每个客户上未使用的成人和儿童空间的总数 全部的 他们的保留。CTE产生:

CustomerId   AdultsRemaining   ChildrenRemaining
00001        1                 0
00002        1                 1
00003        0                 0

最后,CTE后的位使用半连接(即 EXISTS )仅返回 CustomerId 具有符合上述标准的注册的值。特别注意 WHERE 这个查询中的子句:我不能简单地写 Detail.AdultsIncluded <= Rem.AdultsRemaining 因为 Rem.AdultsRemaining 是否有成人空间 全部的 客户的预订,包括 Detail 这样,客户就无法通过将客人从一个登记处转移到同一登记处的空白处来节省资金。最终结果集为:

CustomerId
00001
00002

这看起来对你有用吗?