create table ta
(
aid int,
constraint pk_ta_aid primary key (aid)
)
create table tb
(
bid int,
aid int,
constraint pk_tb_bid primary key (bid),
constraint fk_tb_aid foreign key (aid) references ta(aid)
)
create table tc
(
cid int,
aid int,
constraint pk_tc_cid primary key (cid),
constraint fk_tc_aid foreign key (aid) references ta(aid)
)insert into ta values(1)
insert into ta values(2)insert into tb values(1 , 1)
insert into tb values(2 , 1)
insert into tb values(3 , 2)insert into tc values(1 , 1)
insert into tc values(2 , 2)
insert into tc values(3 , 2)我想查询出 这样的结果aid bnums cnums
1 2 1
2 1 2我是这样写的:SELECT v1.aid,v1.bnum,v2.cnum FROM
(SELECT ta.aid, COUNT(tb.bid) AS bnum
FROM ta LEFT OUTER JOIN tb ON ta.aid = tb.aid
GROUP BY ta.aid) v1 inner join
(SELECT ta.aid, COUNT(tc.cid) AS cnum
FROM ta LEFT OUTER JOIN tc ON ta.aid = tc.aid
GROUP BY ta.aid)v2
ON v1.aid=v2.aid但是总认为不好,求更好的语句,谢谢
解决方案 »
- CROSS APPLY的用法
- dat数据文件如何打开?
- 帮帮忙吧,这个数据库题目啊,明天要考试了,在线等........
- 解决sql server 2005 windows身份验证登录失败
- 这个简单的sql怎么写
- 为什么我在windows XP SP1中不能装sql server2000标准版?出现问题如下:
- 两个个SQL问题
- 我在写存储过程时遇到了问题。没有返回值
- SQL2000下的数据库文件(rtwork.dat),如何连接到SQL7.0下使用(SQL2000已不存在)?在线给分。
- 很奇怪,请教高手?加急!!!!!!
- 怎样将excle表格数据导入数据库sql sever2005中
- 请教成本调整的问题,
bnums = isnull((select count(1) from tb where tb.aid = ta.aid),0),
cnums = isnull((select count(1) from tc where tc.aid = ta.aid),0)
from ta
/*
aid bnums cnums
----------- ----------- -----------
1 2 1
2 1 2(所影响的行数为 2 行)
*/感谢楼主写好的测试语句.
bnums=sum(case when flag='c' then 1 else 0 end)
from(
select *,flag='b' from tb union all
select *,flag='c' from tc)g
group by aid/*
aid bnums bnums
----------- ----------- -----------
1 2 1
2 1 2(所影响的行数为 2 行)*/