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

Ben sin aspa 发表于 Dev

我是阿斯帕

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

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

表订户：

  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]

表人

  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]

戈登·利诺夫

我想你要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 ;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。