没有太好的方法,可以先给临时表做code,department,year索引,然后统计:
修改你的查询:
select identity(int,1,1) id,* into #temp from 表
alter table #temp
add CONSTRAINT PK_t_temp PRIMARY KEY NONCLUSTERED
(
code,department,year
)
go
select code,department,year,
(select top 1 mb from #temp where code=tem.code and department=tem.department and year=tem.year order by id),
sum(md) md,sum(mc) mc,
(select top 1 me from #temp where code=tem.code and department=tem.department and year=tem.year order by id desc)
from #temp tem
group by code,department,year
drop table #temp
修改你的查询:
select identity(int,1,1) id,* into #temp from 表
alter table #temp
add CONSTRAINT PK_t_temp PRIMARY KEY NONCLUSTERED
(
code,department,year
)
go
select code,department,year,
(select top 1 mb from #temp where code=tem.code and department=tem.department and year=tem.year order by id),
sum(md) md,sum(mc) mc,
(select top 1 me from #temp where code=tem.code and department=tem.department and year=tem.year order by id desc)
from #temp tem
group by code,department,year
drop table #temp
add CONSTRAINT PK_t_temp PRIMARY KEY NONCLUSTERED
(
code,department,year
)
改为:
CREATE INDEX [in_t_temp ] ON #temp (code,department,year) ON [PRIMARY]
GO