统计SQL

最新推荐文章于 2023-07-12 10:38:32 发布

oopts

最新推荐文章于 2023-07-12 10:38:32 发布

阅读量109

点赞数

本文链接：https://blog.csdn.net/oopts/article/details/107118934

版权

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