英文:
SQL: how to select last purchased product per client?
问题
考虑以下表结构：
* `invoice`: id, client_id
* `items`: id, invoice_id, name, date
我的目标是获取以下数据集：`client_id, name, date`，但`name`和`date`是对应于最后购买的产品的列。
我尝试了以下查询，但它没有产生正确的结果：
SELECT
i.client_id, ii.name, MAX(ii.date)
FROM
invoice i
INNER JOIN
invoice_item ii ON i.id = ii.invoice_id
GROUP BY
i.client_id;
英文:
Consider the following tables structure:
invoice: id, client_iditems: id, invoice_id, name, date
My goal is to get the following dataset: client_id, name, date but the name and date are the columns that correspond to the last purchased product.
I tried the following query, but it does not produce the correct result
SELECT
i.client_id, ii.name, MAX(ii.date)
FROM
invoice i
INNER JOIN
invoice_item ii ON i.id = ii.invoice_id
GROUP BY
i.client_id;
答案1
得分: 2
你没有提到你使用的RDBMS是什么 - 如果它支持CTE（通用表达式）和窗口函数（许多最常用的RDBMS都支持），你可以使用类似以下的代码：
WITH Purchases AS
(
SELECT
i.client_id, ii.name, ii.date,
RowNum = ROW_NUMBER() OVER (PARTITION BY i.client_id ORDER BY ii.date DESC)
FROM
invoice i
INNER JOIN
invoice_item ii ON i.id = ii.invoice_id
)
SELECT
p.client_id, p.name, p.date
FROM
Purchases
WHERE
p.RowNum = 1;
Purchases 是一个CTE - 类似于“一次性的内联视图”；使用 ROW_NUMBER 函数，你可以按 client_id 对数据进行“分区”，为每个客户的所有购买进行顺序编号，按购买日期降序排序 - 因此每个客户的最新购买将始终具有 RowNum = 1 - 这正是我从该CTE中选择的内容。
英文:
You didn't mention what RDBMS you're using - if it does support CTE's (Common Table Expressions) and windowing functions (many of the most used RDBMS do support it), you could use something like this:
WITH Purchases AS
(
SELECT
i.client_id, ii.name, ii.date,
RowNum = ROW_NUMBER() OVER (PARTITION BY i.client_id ORDER BY ii.date DESC)
FROM
invoice i
INNER JOIN
invoice_item ii ON i.id = ii.invoice_id
)
SELECT
p.client_id, p.name, p.date
FROM
Purchases
WHERE
p.RowNum = 1;
The Purchases is a CTE - sort of an "one-off, inline view"; using the ROW_NUMBER function, you "partition" your data by client_id, and all purchases for each client are sequentially numbered, ordered descendingly by purchase date - so the most recent purchase for each client will always have RowNum = 1 - and that's exactly what I select from that CTE.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论