当前位置：文江博客话题详情

显示最佳结果，但在SQL中的另一个表中限制了术语

发布于 2025-02-03 13:01:26 字数 1453 浏览 4 评论 0 原文

这是我所拥有的：

所有销售的表格。
带有供应商的桌子。
带有产品的桌子。

我需要实现的目标是获取一个查询，该查询将显示每个供应商出售的前三种产品以及该供应商为产品出售的金额，然后按供应商一般的销售额从上到下进行排序在显示所有供应商的同时。

因此，例如，供应商是：Mike，Lucas，Amy，Bob，Matt和Agatha，并且有10种产品。

预期的输出将是：

Mike -Cereal- $ 400
Mike -Juice- $ 100
Mike -Soap $ 50
Amy -soap $ 200
Amy -lettuce $ 150
Amy -Cheese -Cheese $ 100

，依此类推...

我尝试了此代码，但仅显示了最畅销的物品供应商：

SELECT v.vendor_name. p.product_name, sum(f.total) as total
FROM vendors v, sales f, products p
WHERE v.id_vendor = f.vendor and p.id_product = f.product
GROUP BY v.vendor_name, p.product_name
ORDER BY total DESC

表的一些样本：

就像上一张图像一样，应该是供应商出现的顺序，后面是其3个顶级销售产品的列，每个售出的金额。

因此，第一位供应商将出现3次，以及3个顶级售出的产品及其各自的产品。

最佳供应商

随后是

原文

Here's what I have:

Table with all the sales.
Table with the vendors.
Table with the products.

What I need to achieve, is to get a query that would show the top 3 products sold by each vendor along with the amount sold by that vendor for the product and then sort it from top to bottom by the sales made by the vendor in general while showing all the vendors.

So, for example, the vendors are: Mike, Lucas, Amy, Bob, Matt and Agatha and there are 10 products.

The expected output would be:

Mike -cereal - $400
Mike -juice -$100
Mike -soap $50
Amy -soap $200
Amy -lettuce $150
Amy -cheese $100

And so on...

I tried this code but only shows the top selling item for the vendor:

SELECT v.vendor_name. p.product_name, sum(f.total) as total
FROM vendors v, sales f, products p
WHERE v.id_vendor = f.vendor and p.id_product = f.product
GROUP BY v.vendor_name, p.product_name
ORDER BY total DESC

Some samples of the tables:

These are the best vendors

Like the last image displays, that should be the order in which the vendors appear followed by the column of their 3 top sold products and the amount sold for each one.

So the first vendor would appear 3 times, along with the 3 top sold products and their respective amount.

Best Vendor

Followed by Second best.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

枯寂 2025-02-10 13:01:26

并使用通过。

我们尝试重现相同的方案，
销售表

img src =“ https://i.sstatic.net/nfh6l.png” alt =“在此处输入映像”

<
“在此处输入映像”

供应商表
“在此处输入图像说明”

select ts.vendor_name,  
ts.product_name,  
ts.Total_amount from (  
select tbl_vendor.vendor_name,  
tbl_products.product_name,  
sum(tbl_products.product_amount) 'Total_amount',  
rank() over  
(partition by tbl_vendor.vendor_name order by sum(tbl_products.product_amount) desc) as 'rank'  
from tbl_sale  
inner join tbl_vendor  
on tbl_sale.vendor_id = tbl_vendor.vendor_id  
inner join tbl_products  
on tbl_sale.product_id = tbl_products.product_id  
  
group by tbl_products.product_name,tbl_vendor.vendor_name --order by tbl_vendor.vendor_name,Total_amount desc  
) ts where ts.rank <= 3

”在此处输入图像描述

We tried to reproduce the same scenario and we did it with using rank and partition by.

Example :-
Sale Table

enter image description here

Product Table
enter image description here

Vendor table
enter image description here

select ts.vendor_name,  
ts.product_name,  
ts.Total_amount from (  
select tbl_vendor.vendor_name,  
tbl_products.product_name,  
sum(tbl_products.product_amount) 'Total_amount',  
rank() over  
(partition by tbl_vendor.vendor_name order by sum(tbl_products.product_amount) desc) as 'rank'  
from tbl_sale  
inner join tbl_vendor  
on tbl_sale.vendor_id = tbl_vendor.vendor_id  
inner join tbl_products  
on tbl_sale.product_id = tbl_products.product_id  
  
group by tbl_products.product_name,tbl_vendor.vendor_name --order by tbl_vendor.vendor_name,Total_amount desc  
) ts where ts.rank <= 3

Output

enter image description here