mysql - 从 MYSQL 查询获取计数

Question

我在 MYSQL 方面是新手，但我正在尝试从已有的 MYSQL 选择中获取计数。
基本上，我想计算项目的数量，然后根据其locations.locationID 对它们进行分组，然后显示locations.Name。

这是我所拥有的(有点乱)。这会获取大于 5000MB 的驱动器并显示它们。

Select DISTINCT 
  'C'
  , computers.computerid
  , computers.Name as ComputerName
  , Convert(CONCAT(clients.name,' ',locations.name) Using utf8) As Location
  , drives.`Size` as TestValue,0 
FROM ((drives 
LEFT JOIN Computers ON Computers.ComputerID=drives.ComputerID) 
LEFT JOIN Locations ON Locations.LocationID=Computers.Locationid)
LEFT JOIN Clients ON Clients.ClientID=Computers.clientid 
JOIN AgentComputerData on Computers.ComputerID=AgentComputerData.ComputerID 
WHERE drives.`Size` > 5000 AND  (1)  
AND Computers.ComputerID NOT IN 
  (Select ComputerID from AgentIgnore Where AgentID=0);

预先感谢您能给我的任何帮助。

Answer 1

试试这个。我替换了 left win 内部，因为如果您缺少任何表中的关系，结果就没有意义。另外，如果可以的话，我建议在脚本方面进行任何字符集转换，并且如果您只需要一种编码，请从一开始就以这种方式对表进行编码。

SELECT COUNT(*) AS NumDrives
     , CONCAT(clients.name,' ',locations.name) As Location 
  FROM drives 
 INNER  
  JOIN Computers 
    ON Computers.ComputerID=drives.ComputerID 
   AND drives.`Size` > 5000
 INNER 
  JOIN AgentComputerData on Computers.ComputerID=AgentComputerData.ComputerID     
 INNER 
  JOIN Locations 
    ON Locations.LocationID=Computers.Locationid
 INNER  
  JOIN Clients 
    ON Clients.ClientID=Computers.clientid 
 WHERE Computers.ComputerID 
   NOT 
    IN (Select ComputerID from AgentIgnore Where AgentID=0) 
 GROUP
    BY CONCAT(clients.name,' ',locations.name)