表A:日期,车号,里程
2010-01-01,AA,10000
2010-01-02,BB,20000
2010-02-03,BB,10000
2010-03-04,AA,20000
2010-04-05,CC,30000
....很多数据表B,年月,车号,保险费
2010-01,AA,300
2010-01,BB,320
2010-02, CC, 310
2010-02, AA, 300
2010-02, BB, 320
2010-03, AA, 300
2010-03, BB, 320
2010-03, CC, 310怎么把的第一季度的保险保险按照里程分摊到每公里:这样写如何:
select 日期,车号,(select sum(b.保险费) from 表B b where b.车号=车号)*10000*(select sum(a.里程) from 表A a where a.车号=车号)
from A
2010-01-01,AA,10000
2010-01-02,BB,20000
2010-02-03,BB,10000
2010-03-04,AA,20000
2010-04-05,CC,30000
....很多数据表B,年月,车号,保险费
2010-01,AA,300
2010-01,BB,320
2010-02, CC, 310
2010-02, AA, 300
2010-02, BB, 320
2010-03, AA, 300
2010-03, BB, 320
2010-03, CC, 310怎么把的第一季度的保险保险按照里程分摊到每公里:这样写如何:
select 日期,车号,(select sum(b.保险费) from 表B b where b.车号=车号)*10000*(select sum(a.里程) from 表A a where a.车号=车号)
from A
select a.车号,b.保费/a.里程
from (select 车号,sum(里程) from 表a group by 车号) a inner join
(select 车号,sum(保费) from 表b group by 车号) b on a.车号=b.车号
有里程记录而没有交保记录的
有交保而没有里程记录
这两种情况会存在吗,应当怎么处理