我有两个DB表,它们都共享一个Order Number列。
其中一个表是"订单",订单号是唯一的键。
第二个表是我的"事务"表,它有一行,每个订单号的每个事务。根据我们按月付款的事实,"交易"表显然有多行,其中有一个唯一的日期,但每个订单号都有很多重复。
如何运行一个在一列中有唯一OrderNumber列表,在第二列中有最新的"TransDate"(事务日期)的查询。
我尝试了以下操作,但它收回了每个订单号存在的第一个TransDate,而不是最新的。我想我需要某种子查询:
select orders.ordernumber, transdate from orders
join transactions on transactions.ordernumber = orders.ordernumber
where status = 'booking'
group by ordernumber
order by orders.ordernumber, TransDate DESC
您应该只使用MAX()
函数以及对订单号进行分组。似乎也没有任何理由在这里加入。
SELECT
ordernumber,
MAX(transdate) AS maxtransdate
FROM transactions
WHERE status = 'booking'
GROUP BY ordernumber
ORDER BY ordernumber ASC
使用聚合函数,特别是max()
:
select o.ordernumber, max(transdate) as last_transdate
from orders as o
inner join transactions as t on o.ordernumber = t.ordernumber
-- where conditions go here
group by ordernumber
如果你需要为每个订单提取最后一笔交易的详细信息,你可以使用上面的查询作为另一个查询的数据源,并将其与交易表连接:
select a.*, t.*
from (
select o.ordernumber, max(transdate) as last_transdate
from orders as o
inner join transactions as t on o.ordernumber = t.ordernumber
-- where conditions go here
group by ordernumber
) as a
inner join transactions as t on a.ordernumber = t.ordernumber and a.last_transdate = t.transdate
将order by
行更改为
order by Transdate DESC, orders.ordernumber
以下是更改的完整查询
select orders.ordernumber, transdate from orders
join transactions on transactions.ordernumber = orders.ordernumber
where status = 'booking'
group by ordernumber
order by Transdate DESC, orders.ordernumber