group-by - 如何在多于一个table的select语句中使用SUM和MAX

我有 2 个 Tables

table a mempur      
    
    memberno = member number    
    purdt    = purchase date    
    amount   = purchase amount

table b meminfo

    memberno = member number
    fname    = first name
    age      = age


select a.memberno,b.fname,sum(a.amount),a.purdt,b.age from mempur a,(select max(purdt) as maxdate,memberno from mempur group by memberno) maxresult,meminfo b
where a.memberno=b.memberno
and a.purdt between '01-JAN-22' and '28-FEB-22'
and a.memberno=maxresult.memberno
and a.purdt=maxresult.maxdate
group by a.memberno,b.fname,a.purdt,b.age
order by a.memberno;

如何从 table mempur 获得总购买金额和最高购买日期的结果?我使用此查询能够显示结果,但范围之间的总量不正确。

非常感谢任何人的帮助。

我的样本数据

MEMBERNO        PURDT              AMOUNT
--------------- --------------- ---------
BBMY0004580     12-AUG-21          823.65
BBMY0004580     12-AUG-21          1709.1
BBMY0004580     26-AUG-21          1015.1
BBMY0004580     28-AUG-21          1105.1

我的结果只显示总额 1105.1

回答1

您可以在 mempur 中聚合,然后将 join 聚合到 meminfo

SELECT i.*, p.total_amount, p.maxdate
FROM meminfo i 
INNER JOIN (
  SELECT memberno, SUM(amount) total_amount, MAX(purdt) maxdate
  FROM mempur 
  WHERE purdt BETWEEN '01-JAN-22' AND '28-FEB-22'
  GROUP BY memberno
) p ON p.memberno = i.memberno;

如果结果中有您想要的没有购买的成员,您可以使用 LEFT join。

回答2

您的查询获取最大 purdt 并将此日期的金额相加。它还会检查最大 purdt 是在 2022 年 1 月还是 2022 年 2 月。如果是,则显示结果,如果不是,则不显示结果。这不是您想要的查询。

除此之外,查询看起来相当难看。您正在使用一种古老的 join 语法,该语法难以阅读且容易出错。我们在 1980 年代使用它,但在 1992 年显式 joins 将其纳入 SQL 标准。您不应再使用这种旧的逗号语法。看到它仍然被使用很奇怪。去博物馆的感觉。然后,您正在使用 table 别名。这些想法是让查询更具可读性,但您的查询甚至会降低可读性,因为您的别名 a 和 b 是任意的。请改用助记符名称,例如mp 用于 mempur,mi 用于 meminfo。然后,您将日期(我希望 purdt 是一个日期!)与字符串进行比较。不。请改用日期文字。

至于你的tables:你真的在存储年龄吗?您必须每天更新它以保持最新状态。更好地 store 出生日期并在您的查询中计算年龄。

这是一个查询,可为您获取给定日期范围的最大日期和总金额:

select memberno, m.name, p.sum_amount, p.max_purdt, m.age
from meminfo m
left outer join
(
  select memberno, sum(amount) as sum_amount, max(purdt) as max_purdt
  from mempur
  where purdt >= date '2022-01-01' and purdt < date '2022-03-01'
  group by memberno
) p using (memberno)
order by memberno;

这是一个查询,它可以让您获得最大总日期以及给定日期范围内的总金额:

select memberno, m.name, p.sum_amount, p.max_purdt, m.age
from meminfo m
left outer join
(
  select
    memberno,
    sum(case when where purdt >= date '2022-01-01' and purdt < date '2022-03-01'
          then amount
        end) as sum_amount,
    max(purdt) as max_purdt
  from mempur
  group by memberno
) p using (memberno)
order by memberno;

相似文章

随机推荐

最新文章