统计SQL
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了统计SQL,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含2125字,纯文字阅读大概需要4分钟。
内容图文
![统计SQL](/upload/InfoBanner/zyjiaocheng/526/5eabe23bc49b4edf8eb310977abfcca7.jpg)
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
统计SQL
标签:convert flag rest 时分秒 tcl info har kill stat
本文系统来源:https://www.cnblogs.com/opts/p/13229424.html
内容总结
以上是互联网集市为您收集整理的统计SQL全部内容,希望文章能够帮你解决统计SQL所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。