SQL 查询返回不需要的值选择计数名称姓氏返回所有记录

Question

大家好，我有一个查询，它应该返回带有姓名和姓氏的最大计数值。但它返回所有记录:

Select n.vardas,n.pavarde,MAX(n.cnt) as planu_kiekis
FROM 
(SELECT COUNT(t1.abonentoID) as cnt,t2.vardas,t2.pavarde FROM Abonentas t1
join Asmuo t2 on  t1.asmensID=t2.asmensID
group by t1.asmensID,t2.vardas,t2.pavarde)n
group by n.vardas,n.pavarde
order by planu_kiekis desc 

餐 table 上的美味佳肴:

  abonentoID--asmensID
1    [1]     --  [1]
2    [2]     --  [1]
3    [3]     --  [1]
4    [4]     --  [1]
5    [5]     --  [2]
6    [6]     --  [2]
7    [7]     --  [3]
8    [8]     --  [3]
9    [9]     --  [3]
10   [10]    --  [3]

表asmuo

  asmensID--vardas--------pavarde
1  [1]       [toom]      [bulso]
2  [2]       [john]      [trien]
3  [3]       [zack]      [boolshak]

预期结果:

   vardas -- pavarde -- planukiekis
1   [toom]   [bulso]       [4]
2   [zack]  [boolshack]    [4]

我得到的结果:

   vardas -- pavarde -- planukiekis
1   [toom]   [bulso]       [4]
2   [zack]  [boolshack]    [4]
3   [john]   [trien]       [2]

Answer 1

我想你想要 TOP WITH TIES:

Select TOP (1) WITH TIES n.vardas, n.pavarde, MAX(n.cnt) as planu_kiekis
FROM (SELECT COUNT(t1.abonentoID) as cnt, t2.vardas, t2.pavarde
      FROM Abonentas t1 join
           Asmuo t2
           on t1.asmensID = t2.asmensID
      group by t1.asmensID, t2.vardas, t2.pavarde
     ) n
group by n.vardas, n.pavarde
order by planu_kiekis desc ;

或者，您可以使用 rank():

Select TOP (1) WITH TIES n.vardas, n.pavarde, MAX(n.cnt) as planu_kiekis
FROM (SELECT COUNT(t1.abonentoID) as cnt, t2.vardas, t2.pavarde,
             RANK() OVER (PARTITION BY t2.vardas, t2.pavarde
                          ORDER BY COUNT(t1.abonentoID) DESC) as seqnum
      FROM Abonentas t1 join
           Asmuo t2
           on t1.asmensID = t2.asmensID
      GROUP BY t1.asmensID, t2.vardas, t2.pavarde
     ) n
WHERE seqnum = 1
order by planu_kiekis desc ;