首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

求条三个表查询的SQL语句

2013-03-12 
求条3个表查询的SQL语句表A客户companyIDcompanyName5张明2张三3李四4王二表B客户运输车牌号商品吨数商品

求条3个表查询的SQL语句
表A
客户
companyIDcompanyName
5        张明
2        张三
3        李四
4        王二

表B
          客户     运输车牌号商品吨数    商品单价      运输单价
CarriageIDcompanyNameCarNumberGoodsTunnageGoodsUnitPraceCarriageUnitPrace
153        张明       黑B36579           3             1                2369
154        张明       黑B36579           4             2                28816
155        张明       黑B36579          10             8                28020100
                                                                      9134125
88        张三       黑B36579           7             3                2211435
89        张三       黑B36579           8             2                2161632
                                                                      373067
95        李四       黑B36579           4             4                216824
98        李四       黑B36579           5             6                2301040
                                                                      461864


100        王二       黑B36579           3             8                224630
101         王二       黑B36579           6             9                2541266
102        王二       黑B36579           9             9                2811899
                                                                      15936195
                                                                      
                                                                      
表C
            客户付费金额
CashIDcompanyNameMoney
43          张明30
8            张明20
10          张明10
11          张明50
12          张明10120
13          张三500
14          张三300
18          张三2001000
15          李四20
16          李四30
17          李四10
19          李四1070


期待结果

客户运输次数汇总商品金额汇总运费汇总总费用汇总已经付款未付款
张明3                      95      34       125     120  5
张三2                      37      30       67      1000  0


李四2                      46      18       64        70  0
王二3                     159      36       195       0195

后面多出来的两列是我用excel算出来了,和表没关系的,贴到上面格式就变了,希望各位大牛们知道下小弟
这个sql该如何写呢
[解决办法]
select
  客户
  ,sum(运输次数汇总) as 运输次数汇总
  ,sum(商品金额汇总) as 商品金额汇总
  ,sum(运费汇总) as 运费汇总 
  ,sum(总费用汇总) as 总费用汇总
  ,sum(已经付款) as 已经付款
  ,sum(总费用汇总) - sum(已经付款) as 未付款
from (
select 
  companyName as 客户
  ,1 as 运输次数汇总
  ,GoodsTunnage*GoodsUnitPrace as 商品金额汇总
  ,GoodsTunnage*CarriageUnitPrace  as 运费汇总 
  ,GoodsTunnage*GoodsUnitPrace + GoodsTunnage*CarriageUnitPrace as 总费用汇总
  ,cast(0 as numberic(20,4) as 已经付款
from b
union all
select 
  companyName as 客户
  ,0 as 运输次数汇总
  ,0 as 商品金额汇总
  ,0  as 运费汇总 
  ,0 as 总费用汇总
  ,[Money]  as 已经付款
from c
) as t
group by 客户

热点排行