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

khler 2008-11-03 02:58:34
如题

...全文
114 6 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
khler 2008-11-03
  • 打赏
  • 举报
回复
啊,传说中的hyde100 果真名不虚传,搞定
thks
hyde100 2008-11-03
  • 打赏
  • 举报
回复

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 2008-11-03
  • 打赏
  • 举报
回复
都好高级阿,我用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,怎么输出?
水族杰纶 2008-11-03
  • 打赏
  • 举报
回复
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
hyde100 2008-11-03
  • 打赏
  • 举报
回复
最好弄点数据
hyde100 2008-11-03
  • 打赏
  • 举报
回复
这个意思?
select count(*) from my_view
where exists (select 1 from table1 where id = my_view.id)

22,300

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧