日期:2014-05-19  浏览次数:20391 次

请教大家:如何统计~~~~~~~~~~~~~~~~~~
例如:有三个表
A表
--------
Aid号(主键)     
101       
102       
103      

B表
--------
Bid号(主键)  Aid号  Did号
201        101  401
202        102  402
203        103  403

C表:
---------
Cid号(主键)  Did号   字段一
301       401    10
302       401    15
303       402    25
304       403    35
305       403    45
306       403    55
307       403    65

想要得到A表中有Aid号3个;对应的C表中的字段一总值为(10+15+25+35+45+55+65)
           
我刚学,我把三个表进行关联,结果得到A表中有Aid号7个。
请大家帮我想想,谢谢了
回答请详细一些 




------解决方案--------------------
declare @A表 table (Aid int)
insert into @A表
select 101
union all select 102
union all select 103

declare @B表 table(Bid int,Aid int,Did int)
insert into @B表
select 201,101,401
union all select 202,102,402
union all select 203,103,403

declare @C表 table (Cid int,Did int,Col1 int)
insert into @C表
select 301,401,10
union all select 302,401,15
union all select 303,402,25
union all select 304,403,35
union all select 305,403,45
union all select 306,403,55
union all select 307,403,65



select count(distinct a.Aid) as 个数,sum(c.Col1) as 合计 from @A表 a left join @B表 b on a.Aid=b.Aid
left join @C表 c on b.Did=c.Did

/*
个数 合计
---------------------
3 250
*/

------解决方案--------------------
----创建测试数据
declare @ta table(Aid int, 状态 varchar(10))
declare @tb table(Bid int,Aid int,Did int)
declare @tc table(Cid int,Did int,字段一 int)
insert @ta
select 101, '已售 ' union all
select 102, '已售 ' union all
select 103, '待售 '
insert @tb
select 201,101,401 union all
select 202,102,402 union all
select 203,103,403
insert @tc
select 301,401,10 union all
select 302,401,15 union all
select 303,402,25 union all
select 304,403,35 union all
select 305,403,45 union all
select 306,403,55 union all
select 307,403,65

----查询
select
数量 = count(*),
已售数量 = (select count(*) from @ta where 状态 = '已售 ' ),
总计 = sum(c.num)
from @ta as a
left join @tb as b on a.Aid = b.Aid
left join (select Did,sum(字段一) as num from @tc group by Did) as c
on b.Did = c.Did

/*结果
数量 已售数量 总计
----------- ----------- -----------
3 2 250
*/