TSQL实现一对多表关联查询（含子表行数统计与分页）的语法错误解决求助

Hey there, let's fix that query for you! The error you're seeing is because when you use an aggregate function like COUNT(), any non-aggregated columns in your SELECT clause need to be included in a GROUP BY clause. But since you want all columns from the Services table, listing every single column in GROUP BY can get tedious—plus, there's a cleaner way to handle this.

Here are two solid solutions:

1. Using GROUP BY (straightforward for small column lists) #

If you only need a few columns from Services (or don't mind listing all of them), you can add a GROUP BY that includes every non-aggregated column you're selecting:

DECLARE @page int = 0;
DECLARE @pageSize int = 10;

SELECT 
    s.Id,
    s.Name, -- Add all other Services columns you need here
    s.Description,
    COUNT(r.Id) AS TotalReviews 
FROM dbo.Services AS s 
LEFT JOIN dbo.Reviews AS r ON s.Id = r.ServiceId 
GROUP BY s.Id, s.Name, s.Description -- Match all non-aggregated columns from SELECT
ORDER BY s.Id DESC 
OFFSET @page ROWS FETCH NEXT @pageSize ROWS ONLY;

Note: In SQL Server, if Id is the primary key of Services, you can technically just GROUP BY s.Id and the server will allow other columns from Services in the SELECT—but explicitly listing all columns makes the query more readable and compatible with other SQL dialects.

2. Using a subquery to pre-calculate review counts (cleaner for full table columns) #

This approach is better if you want all columns from Services without listing them all in GROUP BY. We first calculate the total reviews per service in a subquery, then join that back to Services:

DECLARE @page int = 0;
DECLARE @pageSize int = 10;

SELECT 
    s.*, -- Gets all columns from Services
    COALESCE(r.TotalReviews, 0) AS TotalReviews -- COALESCE handles services with 0 reviews
FROM dbo.Services AS s 
LEFT JOIN (
    SELECT ServiceId, COUNT(Id) AS TotalReviews
    FROM dbo.Reviews
    GROUP BY ServiceId
) AS r ON s.Id = r.ServiceId
ORDER BY s.Id DESC 
OFFSET @page ROWS FETCH NEXT @pageSize ROWS ONLY;

The COALESCE ensures that services with no reviews show 0 instead of NULL, which is probably what you want for your business logic.

Why your original query failed: Without GROUP BY, the database doesn't know that you want to count reviews per service—it tries to aggregate all rows into a single result, which conflicts with selecting individual service IDs and using pagination.

Hope this gets you sorted out!

内容的提问来源于stack exchange，提问作者S.Minchev