有表 车辆通行记录:trafficrecord:
字段有:
1、车牌号 vehiclenumber
2、总里程 totallength
3、收费金额 totalmoneyvehiclenumber totallength totalmoney
京A12345 20 5
京A12345 20 5
京B12345 30 10想要得到的结果是:里程和费用要相加起来,车牌只显示一次
vehiclenumber totallength totalmoney
京A12345 40 10
京B12345 30 10
字段有:
1、车牌号 vehiclenumber
2、总里程 totallength
3、收费金额 totalmoneyvehiclenumber totallength totalmoney
京A12345 20 5
京A12345 20 5
京B12345 30 10想要得到的结果是:里程和费用要相加起来,车牌只显示一次
vehiclenumber totallength totalmoney
京A12345 40 10
京B12345 30 10
from trafficrecord
group by vehiclenumber
京A12345 20 5
京A12345 20 5
京B12345 30 10 想要得到的结果是:里程和费用要相加起来,车牌只显示一次
vehiclenumber totallength totalmoney
京A12345 40 10
京B12345 30 10 现在是这样的,还有一个时间限制
vehiclenumber totallength totalmoney exittime
京A12345 20 5 2008-11
京A12345 20 5 2008-11
京A12345 20 5 2008-12
京B12345 30 10 2008-11想要得到的结果是:里程和费用要相加起来,车牌只显示一次
vehiclenumber totallength totalmoney exittime
京A12345 40 10 2008-11
京B12345 30 10 2008-11这个应该怎么写啊
select distinct vehiclenumber,zs1 totallength,zs2 totalmoney,exittime
from (select t.*,
sum(totallength) over(partition by vehiclenumber) zs1,
sum(totalmoney) over(partition by vehiclenumber) zs2
from trafficrecord t
where exittime='2008-11');
select vehiclenumber,sum(totallength),sum(totalmoney),exittime
from trafficrecord
where exittime='2008-11'
group by vehiclenumber,exittime
order by 1;
这种基础的统计是经常用到的
from trafficrecord
group by vehiclenumber,exittime
其实要得到LZ所说的数据,二楼仁兄所写的就很好拉.
简单实用,实现LZ的需求很好的拉.