如何在SQL中查找哪个客户具有最大行数 [英] How to find which customer has maximum rows in SQL

问题描述

我想查找哪个客户的发票表中有最大发票，



所以CustomerRef是客户的ID，所以我想找出哪个客户最大SQL Server 2008中的发票。



请帮我写这个查询



什么我试过了：



我刚试过

从发票中选择不同的CustomerRef，其中CustomerRef在
（从发票中选择CustomerRef） CustomerRef分组有COUNT（*）> 1）

此查询仅返回哪些客户有多张发票的记录。 />


但我想要一条记录，哪个客户在我的发票表中有最大行。

解决方案

尝试：

  SELECT   TOP   1  CustomerRef，COUNT（*） AS  InvoiceCount  FROM 发​​票
  GROUP   BY  CustomerRef 
  ORDER   BY  InvoiceCount  DESC  

替代OriginalGriff的解决方案，我建议阅读有关排名功能的信息，这可以帮助您找到发票数量的最大值。

< a href =https://www.mssqltips.com/sqlservertip/1944/sql-server-2005-and-2008-ranking-functions-rownumber-and-rank/> SQL Server 2005和2008排名函数Row_Number和Rank [ ^ ]

  SELECT  A.CustomerRef，ROW_NUMBER（） OVER （ ORDER   BY  A.CountOfInvoices  DESC ）作为位置
  FROM （
  SELECT  CustomerRef，COUNT（*） AS  CountOfInvoices 
  FROM 发​​票
） AS  A 
  WHERE  A.Position = 1 

试试！

Hi, i want to find which customer has maximum invoices in my invoice table,

so CustomerRef is Id of customer so I want to find which customer has maximum invoices in SQL server 2008.

please help me to write query for this

What I have tried:

I just tried

select distinct CustomerRef from Invoices where CustomerRef in
(select CustomerRef from Invoices group by CustomerRef having COUNT(*)>1)

this query returns only records which customers has more than one invoices.

but i want one record which customer has max rows in my invoices table.

解决方案

Try:

SELECT TOP 1 CustomerRef, COUNT(*) AS InvoiceCount FROM Invoices
GROUP BY CustomerRef
ORDER BY InvoiceCount DESC

Alternativelly to OriginalGriff's solution, i'd suggest to read about ranking functions, which may help you to find a maximum value of count of invoices.
SQL Server 2005 and 2008 Ranking Functions Row_Number and Rank[^]

SELECT A.CustomerRef, ROW_NUMBER() OVER(ORDER BY A.CountOfInvoices DESC) As Position
FROM (
    SELECT CustomerRef, COUNT(*) AS CountOfInvoices
    FROM Invoices 
) AS A
WHERE A.Position=1

Try!

这篇关于如何在SQL中查找哪个客户具有最大行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！