英文:
postgresql - join tables with limit
问题
我想展示用户每家商店的购买摘要,每家商店最多显示前3次购买的情况。为简单起见,购买次数不可配置。因此,不是前N次购买,而是前3次购买。
假设我有以下带有这些列的表:
Store
id
name
Product
id
name
storeId
price
Purchase
id
userId
productId
count
purchaseTime
这是我想要显示的内容:
-----------------------------------------------
| 商店 | 商品 | 数量 | 价格 |
-----------------------------------------------
| 迪士尼 | 毛绒玩偶 | 1 | 30 |
| | 唐老鸭帽子 | 3 | 15 |
| | 门票 | 2 | 100 |
-----------------------------------------------
| 环球影城 | 钢铁侠模型 | 1 | 100 |
| | 蝙蝠侠模型 | 1 | 90 |
-----------------------------------------------
最好的情况是,如果用户在两次独立购买中购买了唐老鸭帽子(假设价格永远不会改变),购买次数将被合并。
例如:如果用户购买了2个唐老鸭帽子,然后再购买1个帽子,结果将是3个唐老鸭帽子 - 而不是2个唐老鸭帽子和1个唐老鸭帽子。
我正在使用Hibernate和PostgreSQL。
我甚至不知道从哪里开始,特别是如何限制每个商店购买次数为3次。
任何提示、伪代码解决方案或实际解决方案都将不胜感激。
谢谢
注意:
不幸的是,我在这方面存在一个重大疏忽。
我想我希望每个摘要都在1行内呈现。
例如
id | 商店 | 商品1 | 数量1 | 价格1 | 商品2 | 数量2 | 价格2 | 商品3 | 数量3 | 价格3
----------------------------------------------------------------------------------------------------------------------------------
2 | 迪士尼 | 毛绒玩偶 | 1 | 30 | 唐老鸭帽子 | 3 | 15 | 门票 | 2 | 100
5 | 环球影城 | 钢铁侠模型 | 1 | 100 | 蝙蝠侠模型 | 1 | 90 | null | null | null
原因是:我需要对结果进行分页 - 每页显示10个购买摘要。如果每个摘要返回1-3行,很难弄清楚下一页的结果。
英文:
I want to show a user of his purchase summary from each store, up to first 3 purchases from each store.
For simplicity sake, the number of purchases is not configurable. So, it is not the first N, but the first 3
Let say I have these tables with these columns
Store
id
name
Product
id
name
storeId
price
Purchase
id
userId
productId
count
purchaseTime
This is what I want to display
-----------------------------------------------
| store | product | count | price |
-----------------------------------------------
| disney | stitch doll | 1 | 30 |
| | donald cap | 3 | 15 |
| | ticket | 2 | 100 |
-----------------------------------------------
| universal | iron man figure | 1 | 100 |
| | batman figure | 1 | 90 |
-----------------------------------------------
Preferably, if the user purchases donald caps on 2 separate purchases (assumed that price will never change), the number of purchases will be combined
for example: if the user purchases 2 donald caps and then purchase 1 cap, the result will be 3 donald caps - instead of 2 donald caps & 1 donald cap
I am using hibernate and postgresql
I don't even know where to start, in particular how to limit each store purchases to 3 for each store
Any hint, psudo solution or solutions will be appreciated
thanks
Note:
Unfortunately, there is a major oversight on my part.
I think I want to get each summary in 1 line
For example
id | Store | Product1 | count1 | price1 | Product2 | count2 | price2 | Product3 | count3 | price3
----------------------------------------------------------------------------------------------------------------------------------
2 | Disney | stitch doll | 1 | 30 | donald cap | 3 | 15 | ticket | 2 | 100
5 | Universal | iron man figure | 1 | 100 | batman figure | 1 | 90 | null | null | null
The reason is: I need to paginate the result - displaying 10 purchase summaries per page
If each summary returns 1-3 rows, it is hard to figure out the result of the next page
答案1
得分: 1
你可以使用侧连接(lateral join)来检索每个商店的前3笔购买记录:
select s.name, p.*
from store s
cross join lateral (
select pr.name as product, pu.count, pr.price
from purchase pu
inner join product pr on pr.id = pu.productid
where pr.storeid = s.id and pu.userid = ?
order by pu.purchasetime
limit 3
) p
或者,你也可以使用 row_number()
函数:
select *
from (
select pr.name as product, pu.count, pr.price,
row_number() over(partition by s.id order by pu.purchasetime) rn
from purchase pu
inner join product pr on pr.id = pu.productid
inner join store s on s.id = pr.storeid
where pu.userid = ?
) t
where rn <= 3
英文:
You could use a lateral join to retrieve the first 3 purchases of each store:
select s.name, p.*
from store s
cross join lateral (
select pr.name as product, pu.count, pr.price
from purchase pu
inner join product pr on pr.id = pu.productid
where pr.storeid = s.id and pu.userid = ?
order by pu.purchasetime
limit 3
) p
Alternatively, you can use row_number()
:
select *
from (
select pr.name as product, pu.count, pr.price,
row_number() over(partition by s.id order by pu.purchasetime) rn
from purchase pu
inner join product pr on pr.id = pu.productid
inner join store s on s.id = pr.storeid
where pu.userid = ?
) t
where rn <= 3
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论