计算select查询返回的行数
我需要计算以下查询返回的行数。计算select查询返回的行数
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)
它给在查询分析器的错误消息,其表示如下:
消息102,级别15,状态1,行7不正确的语法靠近')'。
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
+1派生表需要一个别名 – 2012-03-30 15:26:09
它帮助我很多..thnx – 2015-07-03 17:01:13
尝试包裹整个选择括号,然后在该
select count(*)
from
(
select m.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
) myNewTable
它的工作现在。谢谢。为什么你使用myNewTable。 – Joshua 2012-03-30 15:21:23
这只是一个制作的名字,你可以随心所欲地调用它。我想一个更好的名字是[MonitorRequestsWith5OrMoreMonitors] – 2012-03-30 15:26:46
+1派生表需要一个别名 – 2012-03-30 15:27:25
语法运行COUNT(*)错误只是由于子查询缺少别名:
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) mySubQuery /* Alias */
+1派生表需要一个别名 – 2012-03-30 15:28:23
是否考虑过@@ ROWCOUNT? – HABO 2012-03-30 15:43:14