统计SQL

WITH(NOLOCK)用法select * from( SELECT case sa.ActivityTimeType when 1 then sc.EndDate else sa.EndDate end as EndDate, sa.BeginDate, si.Sort, case si.SeckillItemsType when 1 then ii.ItemStatus when 2 then '1' end as ItemStatus, si.ItemCount, si.SaleVolume FROM SeckillItems si WITH(NOLOCK) JOIN SeckillActivitys sa WITH(NOLOCK) ON si.ActivityNo = sa.ActivityNo AND si.CustId = sa.CustId left JOIN ItemInfo ii WITH(NOLOCK) ON si.ObjItemNo = ii.ItemNo AND si.CustId = ii.CustId and ii.ItemType = 3 left JOIN TicketCls tc WITH(NOLOCK) ON si.ObjItemNo = tc.TicketClsNo AND si.CustId = tc.CustId left join SeckillActivityCycle sc WITH(NOLOCK) on sa.CustId=sc.CustId and sa.ActivityNo=sc.ActivityNo WHERE sa.Status = 1 and si.CustId=249217 and ('000' is null or ISNULL(si.BranchNo,'')='' or CHARINDEX(',000,' , ',' + si.BranchNo +',')>0) )a where a.ItemStatus='1' and GETDATE() between a.BeginDate and a.EndDate ) m

　　根据时分秒统计指定数据

select a.CreateDate,convert(varchar,datepart(year,a.CreateDate)) Year,convert(varchar,datepart(month,a.CreateDate)) Month, convert(varchar,datepart(day,a.CreateDate)) Day,convert(varchar,datepart(hour,a.CreateDate)) Hour , {branchNoStr} ISNULL(PayCount,0) NumResult, ISNULL(b.CustID,@custid) CustId, @ModuleType ModuleType from ( SELECT substring(convert(char(32),DATEADD(HH,number,@beginDT),120),1,16) AS CreateDate ,type FROM master..spt_values WHERE type = 'p' AND DATEDIFF(HH,DATEADD(HH,number,@beginDT),@endDT)>=0 ) a left join ( select substring(convert(char(32),DATEADD(HH,datepart(hh,CreateDate),convert(char(32),CreateDate,23)),120),1,16) as st, Count(CustId) PayCount,BranchNo,CustId from OrderMaster where PayFlag = 1 and CustId = @custid {whereStr} and CreateDate between @beginDT and @endDT group by convert(char(32),CreateDate,23),datepart(hh,CreateDate),BranchNo,CustId) b on b.st=a.CreateDate order by CreateDate