SQL - 如何在不必传递选择中的所有列的情况下进行分组？

Question

我有以下选择，其目标是选择自第 X 天以来没有销售的所有客户，并提供上次销售的日期和销售数量:

select s.customerId, s.saleId, max (s.date) from sales s
group by s.customerId, s.saleId
having max(s.date) <= '05-16-2013'

但是这样它给我带来了以下内容:

19 | 300 | 26/09/2005
19 | 356 | 29/09/2005
27 | 842 | 10/05/2012

换句话说，前两行来自同一个客户(id 19)，我希望他为每个客户只得到一条记录，这将是最大日期的记录，在这种情况下，第二条记录列表。
按照这个逻辑，我应该从“group by”子句中去掉 s.saleId，但是如果我这样做了，当然，我会得到错误:
选择列表中的无效表达式(未包含在聚合函数或 GROUP BY 子句中)。

我正在使用火鸟 1.5

我怎样才能做到这一点？

Answer 1

GROUP BY 通过聚合一组行来汇总数据，每组返回一行。您正在使用聚合函数 max() ，这将返回一组行的一列的最大值。

我们来看一些数据。我重命名了您称为“日期”的列。

create table sales (
  customerId integer not null,
  saleId integer not null,
  saledate date not null
  );


insert into sales values
(1, 10, '2013-05-13'),
(1, 11, '2013-05-14'),
(1, 12, '2013-05-14'),
(1, 13, '2013-05-17'),
(2, 20, '2013-05-11'),
(2, 21, '2013-05-16'),
(2, 31, '2013-05-17'),
(2, 32, '2013-03-01'),
(3, 33, '2013-05-14'),
(3, 35, '2013-05-14');

你说

In another words, the first 2 lines are from the same customer(id 19), i wish he'd get only one record for each client, which would be the record with the max date, in the case, the second record from this list.

select s.customerId, max (s.saledate) 
from sales s
where s.saledate <= '2013-05-16'
group by s.customerId
order by customerId;

customerId  max
--
1           2013-05-14 
2           2013-05-16
3           2013-05-14

那张 table 是什么意思？表示客户“1”购买商品的最晚日期为 5 月 16 日或之前的日期为 5 月 14 日；客户“2”在 5 月 16 日或之前购买东西的最晚日期是 5 月 16 日。如果您在连接中使用此派生表，它将返回具有一致含义的可预测结果。

现在让我们看一个稍微不同的查询。 MySQL 允许这种语法，并返回下面的结果集。

select s.customerId, s.saleId, max(s.saledate) max_sale
from sales s
where s.saledate <= '2013-05-16'
group by s.customerId
order by customerId;

customerId  saleId  max_sale
--
1           10      2013-05-14
2           20      2013-05-16
3           33      2013-05-14

ID为“10”的销售没有发生在5月14日；它发生在 5 月 13 日。此查询产生了虚假信息。将此派生表与销售交易表连接将加剧错误。

这就是 Firebird 正确引发错误的原因。解决方案是从 SELECT 子句中删除 saleId。

现在，说了这么多，你可以找到自 5 月 16 日以来没有销售的客户是这样的。

select distinct customerId from sales
where customerID not in
  (select customerId
  from sales
  where saledate >= '2013-05-16')

您可以像这样获得正确的 customerId 和“正确的” saleId。 (我说“正确”的 saleId，因为当天可能有多个。我只是选择了最大值。)

select sales.customerId, sales.saledate, max(saleId)
from sales
inner join (select customerId, max(saledate) max_date
            from sales
            where saledate < '2013-05-16'
            group by customerId) max_dates
        on sales.customerId = max_dates.customerId
        and sales.saledate = max_dates.max_date
inner join (select distinct customerId 
            from sales
            where customerID not in
              (select customerId
               from sales
               where saledate >= '2013-05-16')) no_sales
        on sales.customerId = no_sales.customerId
group by sales.customerId, sales.saledate

就我个人而言，我发现通用表表达式让我更容易阅读这样的 SQL 语句，而不会迷失在 SELECT 中。

with no_sales as (
  select distinct customerId 
  from sales
  where customerID not in
    (select customerId
     from sales
     where saledate >= '2013-05-16')
),
max_dates as (
  select customerId, max(saledate) max_date
  from sales
  where saledate < '2013-05-16'
  group by customerId
)
select sales.customerId, sales.saledate, max(saleId)
from sales
inner join max_dates
        on sales.customerId = max_dates.customerId
        and sales.saledate = max_dates.max_date
inner join no_sales
        on sales.customerId = no_sales.customerId
group by sales.customerId, sales.saledate