postgresql – 使用限制条件连接表格

huangapple go评论77阅读模式
英文:

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 &lt;= 3

huangapple
  • 本文由 发表于 2020年9月3日 18:41:07
  • 转载请务必保留本文链接:https://go.coder-hub.com/63721941.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定