Sql Server 2005 中怎么在一个视图中输出另一个表中满足条件的条数？

[khler]

如题

[hyde100]

这个意思？

select count(*) from my_view
where exists (select 1 from table1 where id = my_view.id)

[hyde100]

最好弄点数据

[水族杰纶]

declare @num int, 
@sqls nvarchar(4000) 
set @sqls='select @a=count(*) from V_test ' 
exec sp_executesql @sqls,N'@a int output',@num output 
select @num

[khler]

都好高级阿，我用Sql 视图设计器生成的代码如下：

SELECT TOP (100) PERCENT dbo.tower.serialNum, dbo.survey.name, 
      dbo.survey.localdesc, dbo.survey.terraindesc, dbo.survey.owner, 
      dbo.survey.version, dbo.survey.status, dbo.survey.submittime
FROM dbo.survey INNER JOIN
      dbo.surveytable ON dbo.survey.id = dbo.surveytable.surveyid INNER JOIN
      dbo.soil ON dbo.survey.id = dbo.soil.surveyid INNER JOIN
      dbo.hole ON dbo.survey.id = dbo.hole.surveyid INNER JOIN
      dbo.tower ON dbo.survey.towerid = dbo.tower.id
ORDER BY dbo.survey.submittime

我还想在输出中输出hole的满足条件dbo.survey.id = dbo.hole.surveyid的条数hole number，怎么输出？

[hyde100]

SELECT TOP (100) PERCENT dbo.tower.serialNum, dbo.survey.name, 
      dbo.survey.localdesc, dbo.survey.terraindesc, dbo.survey.owner, 
      dbo.survey.version, dbo.survey.status, dbo.survey.submittime,
	  (select count(*) from dbo.hole where id = dbo.survey.id) as my_count
FROM dbo.survey INNER JOIN
      dbo.surveytable ON dbo.survey.id = dbo.surveytable.surveyid INNER JOIN
      dbo.soil ON dbo.survey.id = dbo.soil.surveyid INNER JOIN
      dbo.hole ON dbo.survey.id = dbo.hole.surveyid INNER JOIN
      dbo.tower ON dbo.survey.towerid = dbo.tower.id
ORDER BY dbo.survey.submittime

[khler]

啊，传说中的hyde100 果真名不虚传，搞定
thks