o.BillDate格式为YYYY-MM-DD hh:mm:ss:nnn,
要求根据日期分组查询。
主表数据量21000+,从表数据量28000+,
语句一执行时间略优于语句二
SQL语句一:
select Extract(Year from o.BillDate) AYear
, Extract(Month from o.BillDate) AMonth
, Extract(Day from o.BillDate) ADay
, p.BraizeTypeID, Count(p.BraizeTypeID) RecordCount
from OrderBills o
left join orderbillsplans p on (p.Sysid=o.Sysid and p.Entityid=o.Entityid)
where o.Sysid=1
and (o.BillDate between '2011-11-1' and '2011-11-30')
and p.BraizeTypeID is not null
group by
Extract(Year from o.BillDate)
, Extract(Month from o.BillDate)
, Extract(Day from o.BillDate)
, p.BraizeTypeID
order by
Extract(Year from o.BillDate)
, Extract(Month from o.BillDate)
, Extract(Day from o.BillDate)
, p.BraizeTypeID
select aa.AYear, aa.AMonth, aa.ADay, aa.BraizeTypeID, Count(aa.BraizeTypeID) RecordCount
from
(
select Extract(Year from o.BillDate) AYear
, Extract(Month from o.BillDate) AMonth
, Extract(Day from o.BillDate) ADay
, p.BraizeTypeID
from OrderBills o
left join OrderBillsPlans p on (p.Sysid=o.Sysid and p.Entityid=o.Entityid)
where o.Sysid=1
and (o.BillDate between '2011-11-1' and '2011-11-30')
and p.BraizeTypeID is not null
) aa
group by aa.AYear, aa.AMonth, aa.ADay, aa.BraizeTypeID
order by aa.AYear, aa.AMonth, aa.ADay, aa.BraizeTypeID