sql语句查找总量

I am trying to write an SQL statement that can find a company's top client. A top client is defined as one that has purchased the most (total purchase amount) among all the company's clients, not just a the max amount in 1 purchase. How would I go at solving this one? I don't have much experience with SQL but what I do have so far is:

我正在尝试编写一个可以找到公司*客户端的SQL语句。*客户被定义为在所有公司的客户中购买了最多(总购买金额)的客户,而不仅仅是购买中的最大金额。我该如何解决这个问题呢?我对SQL没有太多经验,但到目前为止我所做的是:

SELECT CLIENT.CLIENTNO, CLIENT.CLIENT NAME, PURCHASE.AMOUNT
FROM PURCHASE, CLIENT
WHERE PURCHASE.CLIENTNO = CLIENT.CLIENTNO
GROUP BY CLIENT.CLIENTNO, CLIENT.CNAME, PURCHASE.AMOUNT;

This only displays the results of all candidates. How would I start since i'm not looking for the max value of an amount but the total sum of the highest paying client?

这仅显示所有候选人的结果。我怎么开始,因为我不是在寻找金额的最大值,而是寻找付费最高的客户的总和?

Just going to put this as an additional question if someone could answer.. How would I change a constraint on one of my tables to be limited to a set of strings? Right now it is varchar2(25) but I want it to be only valid for like "string" "string2" help is appreciated.

如果有人可以回答的话,就把它作为一个额外的问题。我如何将我的一个表上的约束更改为限制为一组字符串?现在它是varchar2(25),但我希望它只对“string”“string2”有用,我们非常感谢。

2 个解决方案

#1

Try:

select CLIENTNO, CNAME
from (SELECT CLIENT.CLIENTNO, CLIENT.CNAME, rank() over (order by sum(PURCHASE.AMOUNT) desc) rnk
FROM PURCHASE join CLIENT on PURCHASE.CLIENTNO = CLIENT.CLIENTNO
GROUP BY CLIENT.CLIENTNO, CLIENT.CNAME)
where rnk = 1;

UPDATE If you want the sum of purchase amount

更新如果您想要购买金额的总和

select CLIENTNO, CNAME, spa PURCHASE_AMOUNT
from (SELECT CLIENT.CLIENTNO, CLIENT.CNAME, sum(PURCHASE.AMOUNT) spa,  rank() over (order by sum(PURCHASE.AMOUNT) desc) rnk
FROM PURCHASE join CLIENT on PURCHASE.CLIENTNO = CLIENT.CLIENTNO
GROUP BY CLIENT.CLIENTNO, CLIENT.CNAME)
where rnk = 1;

#2

Try this

 Select c.ClientNo, c.CName Name, Sum(p.Amount)
 From Client c Join Purchase p 
    On p.clientNo = c.clientNo
 Group By c.CLientNo, c.ClientName
 Having Sum(p.Amount) =
       (Select Max(SumAmt)
        From (Select Sum(Amount) SumAmt
              From Purchase 
              Group By clientNo) z)

This sql says

这个sql说

"Select the clientNo, Name and sum of purchase amounts for the specific client whose sum of purchase amounts is the greatest."

“选择购买金额最大的特定客户的客户编号,名称和购买金额总和。”

 Select c.ClientNo, c.CName Name, Sum(p.Amount) -- Select the clientNo, Name and sum 
 From Client c Join Purchase p 
    On p.clientNo = c.clientNo
 Group By c.CLientNo, c.ClientName              -- for the specific client whose 
 Having Sum(p.Amount) =                         -- sum of purchase amounts is 
       (Select Max(SumAmt)                      -- Largest 
        From (Select Sum(Amount) SumAmt         -- Sum of purchase amounts
              From Purchase 
              Group By clientNo) z)             -- for each client

#1