主页 / dba / 问题 / 217987
Accepted
TheDemonLord
Asked: 2018-09-19 20:59:35 +0800 CST

使用 Max() 函数时遇到问题

  • 772

我有 2 个表：Customer 和 Billing，Customer 包含一般详细信息（姓名、地址，您明白了），Billing 包含已计费的项目。

项目计费的结束日期是可变的（有些人是每月一次，其他人是 3 个月、6 个月、1 年等）。

在 Billing Item 中，客户可以拥有多个包裹 - 其中一些是将他们标记为经销商的包裹。我当前的 SQL 查询如下所示：

select customer.addresses_0_firstname AS AdminFName, 
       customer.addresses_0_lastname AS AdminLName, 
       customer.addresses_0_organisation AS CompanyName, 
       customer.addresses_0_address1 AS Address1, 
       customer.addresses_0_address2 AS Address2, 
       customer.addresses_0_city AS City, 
       customer.addresses_0_postcode AS Zip, 
       customer.addresses_0_country AS CountryID, 
       customer.addresses_0_email AS Email, 
       (CASE WHEN billing.lines_0_description like '%Package0%' 
             THEN 'False' 
             ELSE 'true' END) AS Reseller, 
       max(billing.end_date) 
from customer 
INNER JOIN billing on customer._id = billing.customer_$id 
where customer.name='example' 
  and (billing.lines_0_description like '%package1%' 
       or 
       billing.lines_0_description like '%package2%' 
       OR 
       billing.lines_0_description like'%package3%') 
group by customer.addresses_0_firstname, 
         customer.addresses_0_lastname, 
         customer.addresses_0_organisation, 
         customer.addresses_0_address1, 
         customer.addresses_0_address2, 
         customer.addresses_0_city, 
         customer.addresses_0_postcode, 
         customer.addresses_0_country, 
         customer.addresses_0_email, 
         billing.lines_0_description

输出以下内容：

AdminFName  AdminLName  CompanyName Address1    Address2    City    Zip CountryID   Email           Reseller    (No column name)
Test User   Testing     123         test        Rd          test    0   Test        [email protected]   true        2012-03-23
Test User   Testing     123         test        Rd          test    0   Test        [email protected]   true        2012-01-23

这是所有用户数据（我们想要的）的列表，但加入了每个账单行，而不是选择具有最大值的 end_date。

我怀疑我必须做一个嵌套选择，但我不确定我在这里需要的逻辑。

建议？

sql-server sql-server-2014

3 个回答

  1. Best Answer

    当您在 lines_0_description 上分组时，您无法获得 end_date 的单个 MAX 值，因为每条账单记录可能会有不同的描述，从而产生不同的不同组和不同的最大 end_date 值。

    您可以使用 CTE 提取每个客户的最新账单记录，然后将其加入您的主 SELECT 以生成您想要的结果集。像下面这样的东西应该可以工作（免责声明：我猜到了一些列名，例如计费表中的 _id，因为我不知道架构）

    ;WITH BillingRecords AS (
  SELECT MAX(_id) AS billing_id,
    customer_$id
  FROM billing
  WHERE (billing.lines_0_description like '%package1%' 
       or 
       billing.lines_0_description like '%package2%' 
       OR 
       billing.lines_0_description like'%package3%')
  GROUP BY customer_$id
)


  select customer.addresses_0_firstname AS AdminFName, 
       customer.addresses_0_lastname AS AdminLName, 
       customer.addresses_0_organisation AS CompanyName, 
       customer.addresses_0_address1 AS Address1, 
       customer.addresses_0_address2 AS Address2, 
       customer.addresses_0_city AS City, 
       customer.addresses_0_postcode AS Zip, 
       customer.addresses_0_country AS CountryID, 
       customer.addresses_0_email AS Email, 
       (CASE WHEN billing.lines_0_description like '%Package0%' 
             THEN 'False' 
             ELSE 'true' END) AS Reseller, 
       billing.end_date
from customer 
INNER JOIN BillingRecords br ON br.customer_$id = customer._id
INNER JOIN billing on billing._id = br.billing_id
where customer.name='example' 
  and (billing.lines_0_description like '%package1%' 
       or 
       billing.lines_0_description like '%package2%' 
       OR 
       billing.lines_0_description like'%package3%') 
group by customer.addresses_0_firstname, 
         customer.addresses_0_lastname, 
         customer.addresses_0_organisation, 
         customer.addresses_0_address1, 
         customer.addresses_0_address2, 
         customer.addresses_0_city, 
         customer.addresses_0_postcode, 
         customer.addresses_0_country, 
         customer.addresses_0_email
    • 2

  2. 您可以为此使用窗口函数。使用公用表表达式提供日期的排序顺序，然后在最终选择语句中按顺序进行过滤。

    with Cte as (
    select ...,
           ROW_NUMBER() OVER (PARTITION BY billing.customer_$id ORDER BY billing.end_date DESC) AS Date_Rank
    From ...
)
select ...
from Cte
where Date_Rank = 1

    https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-2017

    抱歉缺少格式，这是我的第一篇文章，我有点赶时间。

    • 0

  3. 一般来说，我更喜欢JHackney 发布的 CTE 方法，但如果你想避免重写（现在），只需更改：

    group by customer.addresses_0_firstname, 
     ...,
     billing.lines_0_description

    至

    group by customer.addresses_0_firstname, 
     ...,
     (CASE WHEN billing.lines_0_description like '%Package0%' 
         THEN 'False' 
         ELSE 'true' END)

    问题是您想要将所有这些不同的值折叠成两个不同的字符串，但是如果您按原始字符串而不是折叠的输出进行分组，您将有多个行组成组，但这在输出中并不明显。lines_0_description（如果您只输出该列而不是CASE现有查询中的表达式，您就会明白我的意思。）

    • 0

相关问题