sql - 获取每个客户的前 6 名总数

Question

我希望创建一个脚本，从每个客户那里选择前 6 个月的数据。我目前所拥有的绝对是不正确的，有人可以帮我解决这个问题。

select Ticket_ClientName, count(Ticket_ClientName) as DisplayNameCount, 
concat(datepart(year, cast(t.ticket_opendate as date)), RIGHT('00'+ convert(NVARCHAR(2),(datepart(month, cast(t.ticket_opendate as date)))),2)) as OpenDate, 
datename(month, cast(t.ticket_opendate as date)) as Month_Name
from dbo.Ticket t
where t.Ticket_ClientName is not null
and Ticket_DisplayId not like 'EH%'
and Ticket_Statusname not like 'Deleted'
group by ticket_ClientName,concat(datepart(year, cast(t.ticket_opendate as date)), RIGHT('00'+ convert(NVARCHAR(2),(datepart(month, cast(t.ticket_opendate as date)))),2)), datename(month, cast(t.ticket_opendate as date))

我知道我在某个地方需要前 6 名，但我无法解决这个问题。我通常会想到一个 for 循环，例如

for distinct ticket_clientname in tickets:

我只是在努力寻找解决这个问题的最佳方法。当我运行当前查询时，我得到：

Ticket_ClientName DisplayNameCount OpenDate       Month_Name
------------------------------------------------------------
ClientName           1                202006         June
ClientName           1                202008         August
ClientName           13               202009         September

我想要的数据是我期待从某人那里听到的每个客户的前 6 个数据。

标签： sqlsql-servertsql

Answer 1

我在这里添加一个示例答案。您可以使用 Windows 函数 Top (n) order by 来获得答案。

 SELECT 
   A.Ticket_ClientName
  ,A.DisplayNameCount
  ,A.OpenDate
  ,A.Month_Name
FROM
(

     select Ticket_ClientName, 
       count(Ticket_ClientName) as DisplayNameCount
       ,concat(datepart(year, cast(t.ticket_opendate as date)), RIGHT('00'+ convert(NVARCHAR(2),(datepart(month, cast(t.ticket_opendate as date)))),2)) as OpenDate, 
      datename(month, cast(t.ticket_opendate as date)) as Month_Name
      ,DATEPART(MM, cast(t.ticket_opendate as date)) as [Month_Name_int]
      from dbo.Ticket t
     where t.Ticket_ClientName is not null
       and Ticket_DisplayId not like 'EH%'
       and Ticket_Statusname not like 'Deleted'
       group by DATEPART(MM, cast(t.ticket_opendate as date)),ticket_ClientName,concat(datepart(year, cast(t.ticket_opendate as date)), RIGHT('00'+ convert(NVARCHAR(2),(datepart(month, cast(t.ticket_opendate as date)))),2)), datename(month, cast(t.ticket_opendate as date))
        )AS A 
      WHERE A.Month_Name_int <=6

我添加了我的样本数据并获得了按月排名的数据（int） 我使用的样本数据：

Create table dbo.Ticket
(
 Ticket_ClientName varchar(100)
,ticket_opendate Date
,Ticket_DisplayId varchar(22) 
,Ticket_Statusname  varchar(100)
 )
 insert into  dbo.Ticket values ('test_client1' , '2020-01-01', 'AH', 'open')
  ,('test_client1' , '2020-01-02', 'AH', 'open')
 ,('test_client1' , '2020-01-03', 'AH', 'open')
 ,('test_client1' , '2020-01-04', 'AH', 'open')
 ,('test_client1' , '2020-01-05', 'AH', 'open')
 ,('test_client1' , '2020-01-06', 'AH', 'open')
 ,('test_client1' , '2020-02-02', 'AH', 'open')
 ,('test_client1' , '2020-04-03', 'AH', 'open')
 ,('test_client1' , '2020-03-04', 'AH', 'open')
 ,('test_client1' , '2020-02-05', 'AH', 'open')
 ,('test_client1' , '2020-03-06', 'AH', 'open')