select a.staffId,B.Name,
sum(case day(a.ProduceDate) when '1' then a.complement+a.ProduceTime else 0 end)'1',
sum(case day(a.ProduceDate) when '2' then a.complement+a.ProduceTime else 0 end)'2',
............
sum(case day(a.ProduceDate) when '31' then a.complement+a.ProduceTime else 0 end)'31'
from dbo.ProduceTimeBomForMaster a left join [MRP.NET.DB].dbo.Staff B
on a.staffId collate Chinese_PRC_CI_AS = B.StaffID collate Chinese_PRC_CI_AS
where datediff(month,ProduceDate,'2010-06-01') = 0--耗时2秒
--where convert(varchar(7),ProduceDate,120) ='2010-06' --耗时7秒
group by a.staffId,B.Name
在行转列中,查询条件用Convert(varchar(7),ProduceDate,120)来查询,查询结果集耗时7秒,
用Datediff(ProduceDate,'2010-06')来查询结果集耗时2秒,
在相同的条件下datediff的效率高很多。