请教一个SQL语句

[叔到用时方恨嫂]

表A(运费信息)
车牌号 运输总金额 运费 备注
aaa 50000 200
aaa 3000 100
bbb 30000 600
bbb 10000 200
bbb 8000 100
表B(运费调整信息)
车牌号 补拉运费(+) 扣除运费(-) 备注
aaa 100 0
aaa 0 120
aaa 50 60
bbb 100 200
要求得到表如下：
车牌号 运输总金额 运费 补拉运费 扣除运费 实际运费 备注
aaa 53000 300 150 180 270
bbb 48000 900 100 200 800
根据车牌号分组,实际运费=运费+补拉运费-扣除运费

[SQL77]

select a.车牌号,a.运输总金额,a.运费,b.补拉运费,b.扣除运费

from (select 车牌号,sum( 运输总金额 ) as  运输总金额 ,sum(运费) as  运费 ,max(备注) 备注
from a group by 
车牌号) a,
(
select 车牌号,sum( 补拉运费 ) as  补拉运费 ,sum(扣除运费) as 扣除运费 ,max(备注) 备注
from b group by 
车牌号) b where a.车牌号=b.车牌号

[yibey]

[Quote=引用 1 楼 sql77 的回复:]

SQL code
select a.车牌号,a.运输总金额,a.运费,b.补拉运费,b.扣除运费

from (select 车牌号,sum( 运输总金额 ) as 运输总金额 ,sum(运费) as 运费 ,max(备注) 备注
from a group by
车牌号) a,
(
select 车牌号,sum( 补拉运费 ) as 补拉运费 ,sum(扣除运费) as 扣除运费 ,m……
[/Quote]


分组后连接顶下咯

[--小F--]

select
 a.车牌号,a.运输总金额,a.运费,b.补拉运费,b.扣除运费
from
 (select 车牌号,sum( 运输总金额 ) as  运输总金额 ,sum(运费) as  运费 ,max(备注) 备注 from a group by 车牌号) a,
 (select 车牌号,sum( 补拉运费 ) as  补拉运费 ,sum(扣除运费) as 扣除运费 ,max(备注) 备注 from b group by 车牌号) b 
where
 a.车牌号=b.车牌号

[叔到用时方恨嫂]

thank!

[gogodiy]

create table t1
(
	id varchar(5),
	jine int,
	yunfei int,
	memo varchar(10)
)
insert into t1
select 'aaa', 50000, 200,'' union all
select 'aaa', 3000, 100,'' union all
select 'bbb', 30000, 600,'' union all
select 'bbb', 10000, 200,'' union all
select 'bbb', 8000, 100,''
create table t2
(
	id varchar(5),
	yunfeijia int,
	yunfeijian int,
	memo varchar(10)
)
insert into t2
select 'aaa', 100, 0,'' union all
select 'aaa', 0, 120,'' union all
select 'aaa', 50, 60,'' union all
select 'bbb', 100, 200,''
select * from t1
select * from t2

;with aaa as
(select id,SUM(jine) as jine,SUM(yunfei) as yunfei from t1 group by id)
,bbb as
(select id,SUM(yunfeijia) as yunfeijia,SUM(yunfeijian) as yunfeijian from t2 group by id)
,ccc as
(select aaa.id,aaa.jine,aaa.yunfei,bbb.yunfeijia,bbb.yunfeijian,aaa.yunfei+bbb.yunfeijia-bbb.yunfeijian as yunfeicha 
from aaa inner join bbb on aaa.id=bbb.id)
select * from ccc