我如何加入两张桌子，然后根据sales_amount找到查找前5个CUSTMER_NAMES，并按Custmer名称进行分组

发布于 2025-02-11 00:51:58 字数 534 浏览 2 评论 0原文

基本上，我希望由Custmer_name

with results as (
SELECT custmer_name, sales_amount from sales.customers inner join sales.transactions
on sales.customers.customer_code = sales.transactions.customer_code 
)

select *, dense_rank() over(partition by custmer_name order by sales_amount desc) as ranking  from results

a href =“ https://i.sstatic.net/in7tl.png” noreferrer”>交易表

custmer_name分组的最大sales_amount排名前5名。 https://i.sstatic.net/ilnzw.png“ rel =“ nofollow noreferrer“>加入后的结果

原文

Basically I want the top 5 custmer_name with maximum sales_amount grouped by custmer_name

with results as (
SELECT custmer_name, sales_amount from sales.customers inner join sales.transactions
on sales.customers.customer_code = sales.transactions.customer_code 
)

select *, dense_rank() over(partition by custmer_name order by sales_amount desc) as ranking  from results

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

发布评论

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

等你爱我 2025-02-18 00:51:58

您应该计算内部密集级别 cte或subquery，以及外部的过滤器：

WITH results AS (
    SELECT c.custmer_name, t.sales_amount,
           DENSE_RAN() OVER (PARTITION BY c.custmer_name
                             ORDER BY t.sales_amount DESC) AS ranking
    FROM customers c
    INNER JOIN transactions t
        ON c.customer_code = t.customer_code 
)

SELECT *
FROM results
WHERE ranking <= 5;

You should compute the dense rank inside the CTE or subquery, and the filter on it outside:

WITH results AS (
    SELECT c.custmer_name, t.sales_amount,
           DENSE_RAN() OVER (PARTITION BY c.custmer_name
                             ORDER BY t.sales_amount DESC) AS ranking
    FROM customers c
    INNER JOIN transactions t
        ON c.customer_code = t.customer_code 
)

SELECT *
FROM results
WHERE ranking <= 5;

回复收藏 0 原文

~没有更多了~