首页 新闻 赞助 找找看

一条sql语句,求高手帮忙

0
悬赏园豆:5 [已关闭问题] 关闭于 2013-08-07 14:47

最后希望的结果是:在Table1里面增加一列“数量”,此列显示的是比如OfficeID=67在table2和table3中一共有多少条数据;OfficeID=77在table2和table3中一共有多少条数据…

Table1:

 

Table2:

Table3:

 

sql
渡边Q的主页 渡边Q | 初学一级 | 园豆:196
提问于:2013-08-07 14:26
< >
分享
所有回答(3)
0

这个不难实现

蔚蓝鸟 | 园豆:150 (初学一级) | 2013-08-07 14:45
0
select officeid ,门诊1,门诊2,门诊3,officeName,
(select
( select count(*) as Bcount from Table2 B where B.id=A.officeId
group by B.id ) +
( select count(*) as Ccount from Table3 C
where C.id==A.officeId group by C.id)) as AllCount
from Table1 A
渡边Q | 园豆:196 (初学一级) | 2013-08-07 14:46
0
 SELECT officeid ,
        COUNT(*) AS total
 INTO   #temp
 FROM   ( SELECT    offceid
          FROM      table2
          UNION ALL
          SELECT    offceid
          FROM      table3
        ) a
 GROUP BY offceid          
SELECT  officeid ,
        ( CASE WHEN EXISTS ( SELECT total
                             FROM   #temp
                             WHERE  officeid = table1.officeid )
               THEN ( SELECT    total
                      FROM      #temp
                      WHERE     officeid = table1.officeid
                    )
               ELSE 0
          END ) AS Total
FROM    table1
DROP TABLE #temp
田林九村 | 园豆:2367 (老鸟四级) | 2013-08-07 14:56
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册