sql-server - 计算选择查询返回的行数

Question

我需要计算以下查询返回的行数。

select m.Company_id
from Monitor as m
    inner join Monitor_Request as mr on mr.Company_ID=m.Company_id
    group by m.Company_id
    having COUNT(m.Monitor_id)>=5

我尝试过以下

select COUNT(*) from
(
select m.Company_id
from Monitor as m
    inner join Monitor_Request as mr on mr.Company_ID=m.Company_id
    group by m.Company_id
    having COUNT(m.Monitor_id)>=5)  

它在查询分析器中给出一条错误消息，内容如下:

Msg 102, Level 15, State 1, Line 7 Incorrect syntax near ')'.

Answer 1

SQL Server 要求您 SELECT FROM 或 JOIN 的子查询具有别名。

向子查询添加别名(在本例中为 x):

select COUNT(*) from
(
select m.Company_id
from Monitor as m
    inner join Monitor_Request as mr on mr.Company_ID=m.Company_id
    group by m.Company_id
    having COUNT(m.Monitor_id)>=5)  x