对于每个ID_Number,都会发生一个bill_date,然后发生两种类型的账单。我想返回每个 ID 号的最新日期(最大日期),然后将两种类型的账单金额相加。因此,根据下表,它应该返回:
| 1 | 201604 | 10.00 | |
| 2 | 201701 | 28.00 | |
tbl_charges
+-----------+-----------+-----------+--------+
| ID_Number | Bill_Date | Bill_Type | Amount |
+-----------+-----------+-----------+--------+
| 1 | 201601 | A | 5.00 |
| 1 | 201601 | B | 7.00 |
| 1 | 201604 | A | 4.00 |
| 1 | 201604 | B | 6.00 |
| 2 | 201701 | A | 15.00 |
| 2 | 201701 | B | 13.00 |
+-----------+-----------+-----------+--------+
然后,如果可能的话,我希望能够在另一个查询的联接中执行此操作,使用 ID_Number 作为联接的列。这会改变这里的查询吗?
注意:我最初只想对大约 1000 万个不同ID_Numbers中的大约 200 个运行查询。我将为这些 ID 添加一个"IN"子句。当我为最终产品进行联接时,我需要知道如何从所有其他联接可能性中获取这些最新日期。(即,如何让 ID_Number 1 加入 201604 而不是201601?
我会使用NOT EXISTS
和GROUP BY
select, t1.id_number, max(t1.bill_date), sum(t1.amount)
from tbl_charges t1
where not exists (
select 1
from tbl_charges t2
where t1.id_number = t2.id_number and
t1.bill_date < t2.bill_date
)
group by t1.id_number
NOT EXISTS
过滤掉不相关的行,GROUP BY
做总和。
我倾向于在where
中过滤:
select id_number, sum(c.amount)
from tbl_charges c
where c.date = (select max(c2.date)
from tbl_charges c2
where c2.id_number = c.id_number and c2.bill_type = c.bill_type
)
group by id_number;
或者,另一种有趣的方法是将in
与元组一起使用:
select id_number, sum(c.amount)
from tbl_charges c
where (c.id_number, c.bill_type, c.date) in
(select c2.id_number, c2.bill_type, max(c2.date)
from tbl_charges c2
group by c2.id_number, c2.bill_type
)
group by id_number;