sql - sql语法有什么问题

Question

SELECT 
    a.Name, a.About, 
    COUNT(b.Id) AS TotalCities, 
    SUM(b.NoOfDwellers) AS TotalCityDwellers 
FROM 
    Countries a 
LEFT JOIN 
    Cities b ON a.Id = b.CountryId 
WHERE 
    a.Name LIKE '%some str%'  
GROUP BY 
    a.Id 
ORDER BY 
    a.Name ASC

此SQL返回错误:

Msg 8120, Level 16, State 1, Line 1
Column 'Countries.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Answer 1

如果您有一个带有SELECT子句的GROUP BY，则SELECT列列表中的每一列都必须是或聚合(SUM，COUNT，MAX等)，否则它必须位于GROUP BY子句中。

您的a.Name列表中同时包含a.About和SELECT，它们没有由聚合处理-因此，这两列必须出现在GROUP BY子句中

SELECT 
    a.Name, a.About, 
    COUNT(b.Id) AS TotalCities, 
    SUM(b.NoOfDwellers) AS TotalCityDwellers 
FROM 
    Countries a 
LEFT JOIN 
    Cities b ON a.Id = b.CountryId 
WHERE 
    a.Name LIKE '%some str%'  
GROUP BY 
    a.Id, a.About, a.Name 
ORDER BY 
    a.Name ASC