查找最大查询数(SQL Server 2016) [英] Find max count of query (SQL Server 2016)

问题描述

Q1:如何获得每个虚拟机的NIC数量?例如

Q1: How do I get a count of NICs for each virtual machine? e.g.

virtualMachine1  2
virtualMachine2  3
virtualMachine3  1
virtualMachine4  2

Q2:如何获得代表任何虚拟机的最大NIC数量的值?例如

Q2: How do I get the value that represents the max number of NICs for any virtual machine? e.g.

10

以下查询返回所有虚拟机网卡对:

The following query returns all virtual machine-nic pairs:

SELECT VirtualMachine.VirtualMachineName, VMToNetwork.MacAddress
FROM   VirtualMachine
        INNER JOIN VMToNetwork
         ON VirtualMachine.VirtualMachineID = VMToNetwork.VirtualMachineID 
ORDER BY VirtualMachine.VirtualMachineName

"VMToNetwork.MacAddress"字段仅用于说明联接有效.

The field 'VMToNetwork.MacAddress' is not used other than to illustrate that the join worked.

此查询尝试计算每个虚拟机的NIC数量，但只是将NIC的总数相加.

This query attempts to count the number of NICs per virtual machine but simply sums up the total number of NICs.

SELECT count( VMToNetwork.MacAddress )
FROM   VirtualMachine
        INNER JOIN VMToNetwork
          ON VirtualMachine.VirtualMachineID = VMToNetwork.VirtualMachineID 

谢谢.

推荐答案

要获取每个VM的NIC数量，必须按VM名称分组:

To get a count of NICs per VM, you must group by VM Name:

SELECT VirtualMachine.VirtualMachineName, count(VMToNetwork.MacAddress) as NICCount
FROM VirtualMachine
INNER JOIN VMToNetwork ON VirtualMachine.VirtualMachineID = VMToNetwork.VirtualMachineID
GROUP BY VirtualMachine.VirtualMachineName

要从中获得最大收益，您可以按NICCount订购并获得最高收益:

To get the maximum from that, you can order by NICCount and get the top:

SELECT TOP 1 VirtualMachine.VirtualMachineName, count(VMToNetwork.MacAddress) as NICCount
FROM VirtualMachine
INNER JOIN VMToNetwork ON VirtualMachine.VirtualMachineID = VMToNetwork.VirtualMachineID
GROUP BY VirtualMachine.VirtualMachineName
ORDER BY NICCount

请注意，在平局中，您只会得到一名平局成员.

Note that in a tie, you will only get one of the tie members.

如果只需要最大NIC数，则还可以使用子查询:

If you just wanted the maximum NIC count, you can also use a subquery:

SELECT MAX(T1.NICCount)
FROM (
    SELECT count(VMToNetwork.MacAddress) as NICCount
    FROM VirtualMachine
    INNER JOIN VMToNetwork ON VirtualMachine.VirtualMachineID = VMToNetwork.VirtualMachineID
    GROUP BY VirtualMachine.VirtualMachineName
) as T1

这篇关于查找最大查询数(SQL Server 2016)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！