select a.*,b.sumCar
from volumn a,(select locationno,max(bigcar+middlecar+smallcar) as sumCar
from volumn
group by locationno) b
where a.locationno=b.locationno
and a.bigcar+a.middlecar+a.smallcar=b.sumCar
from volumn a,(select locationno,max(bigcar+middlecar+smallcar) as sumCar
from volumn
group by locationno) b
where a.locationno=b.locationno
and a.bigcar+a.middlecar+a.smallcar=b.sumCar
from Volumn A, (select locationno,max(bigcar+middlecar+smallcar) as SumCar from volumn group by locationno) B
where A.locationno = B.locationno and A.bigcar + A.middlecar + A.smallcar = B.SumCar
bigcar,middlecar,smallcar
from volumn a ,(select locationno,max(bigcar+middlecar+smallcar) as sumcar
from volumn group by locationno) b
where a.bigcar+middlecar+smallcar=b.sumcar
and a.locationno=b.locationno
insert into Volumn select 1,1,'2004-9-13 00:00:00',1,2,3
union select 1,1,'2004-9-13 00:00:00',2,4,5
union select 1,1,'2004-9-13 00:00:00',3,1,2
union select 1,2,'2004-9-13 00:00:00',4,0,1
union select 2,1,'2004-9-13 00:00:00',1,1,1
union select 2,1,'2004-9-13 00:00:00',2,3,1
union select 2,2,'2004-9-13 00:00:00',1,2,1条件:
按照SectionHour,如果LocationNo相同,PathNo不同,则相加取最大,然后取出所有LocationNo中的最大,取出和最大的总和数据和分合数据
结果:
SectionHour SumCar BigCar MiddleCar SmallCar
'2004-9-13 00:00:00' 16 6 4 6谢谢!
Create Table Volumn(LocationNo tinyint,PathNo tinyint,SectionHour DateTime,BigCar int,MiddleCar int,SmallCar int)