我试图在政策生命周期内获得net_insurance的总金额.
这是我的表格政策:
ID date_ini date_expired num_policy 1, '2013-01-01', '2014-03-08', 1234 2, '2012-02-11', '2013-02-01', 5678 3, '2013-03-01', '2013-08-03', 9123 4, '2013-04-01', '2013-08-01', 4567 5, '2013-05-01', '2013-09-01', 8912
这是我的餐桌保险
ID initial_date final_date policy_id net_insurance 1, '2013-01-16', '2014-01-01', 1, 1000 2, '2013-01-14', '2014-03-06', 1, 1400 3, '2012-03-17', '2013-04-24', 2, 2000 4, '2012-02-12', '2013-02-01', 2, 2500 5, '2013-03-09', '2013-08-20', 3, 3000 6, '2013-03-11', '2013-08-02', 3, 4000
它将根据这种情况求和(在政策的生命周期内).
WHERE insurances.initial_date >= policies.date_ini AND insurances.final_date <= policies.date_expired
根据条件,我应该这样:
ID NUM_POLICY SUM_INSURANCE 1 1234 2400 2 5678 2500 3 9123 4000 4 4567 0 5 8912 0
这样做:
|jan| |feb| |mar| |apr| |may| |jun| |jul| |ago| |sep| |oct| |nov| |dec| 2400 2400 2400 2400 2400 2400 2400 2400 2400 2400 2400 2400 2500 2500 _______________________________________________________________ ______________4000 4000 4000 4000 4000 4000 ________________________ _______________________0 0 0 0 0 ________________________ ____________________________0 0 0 0 0______________________
以下是每月总和的最终结果:
|jan| |feb| |mar| |apr| |may |jun| |jul| |ago| |sep| |oct| |nov| |dec| 4900 4900 6400 6400 6400 6400 6400 6400 2400 2400 2400 2400
但是我没有工作我的查询我有另一个值:
JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC 8900 8900 8900 8900 8900 8900 8900 8900 8900 8900 8900 8900
这是我试过的http://sqlfiddle.com/#!2/e75ea/1
请有人帮我这个吗?
我真的很感激帮助.