vfp:
select com_id,ye,sum(data1),...sum(data6) from yourtable
group by com_id order by com_id,yymm
sql:
select a.com_id,b.ye,a.data1,...,a.data6
from (select com_id,sum(data1) as data1,...,sum(data6) as data6 from yourtable group by com_id) a
(select com_id,yymm,ye
from yourtable where yymm in
(select min(yymm) as yymm from yourtable group by com_id)) b
where a.com_id=b.com_id
order by a.com_id
select com_id,ye,sum(data1),...sum(data6) from yourtable
group by com_id order by com_id,yymm
sql:
select a.com_id,b.ye,a.data1,...,a.data6
from (select com_id,sum(data1) as data1,...,sum(data6) as data6 from yourtable group by com_id) a
(select com_id,yymm,ye
from yourtable where yymm in
(select min(yymm) as yymm from yourtable group by com_id)) b
where a.com_id=b.com_id
order by a.com_id
declare
@startdate datetime,
@enddate datetime
select @startdate='2001-2-3',@enddate='2002-10-2'
select a.com_id,b.yymm,b.ye,a.data1,a.data2,a.data3,a.data4,a.data5,a.data6
from
(select com_id,sum(data1) as data1,sum(data2) as data2,sum(data3) as data3
,sum(data4) as data4,sum(data5) as data5,sum(data6) as data6 from #tmp
where cast((left(yymm,4)+'-'+right(yymm,2)+'-'+'1') as datetime) between @startdate and @enddate
group by com_id
) a,
(select com_id ,yymm,ye from #tmp where
yymm in
(select min(yymm) from #tmp
where cast((left(yymm,4)+'-'+right(yymm,2)+'-'+'1') as datetime) between @startdate and @enddate
group by com_id
)
) b
where a.com_id=b.com_id
(2)但ye字段只能取起始时间的值,又是什么意思?
(3)你用MS SQL吗?
from ...
group by ...
order by ...