一个比较复杂的sql

declare @statTime datetime, @endTime datetimeset @statTime = dateadd(hh,datediff(hh,0,getdate())-1,0)set @endTime = dateadd(hh,datediff(hh,0,getdate()),0)

select ua.SPID,@statTime as statTime,mt.BusinessType ,  case when r.运营商名称 is null then '未知' else r.运营商名称 end 运营商名称,   case when r.地区名称 is null then '未知' else r.地区名称 end 地区名称,   count(distinct mt.MTID) as MtOkNum from dbo.SMS_Log_MT mt, sms.SMS_System.dbo.SMS_Users_All ua left join sms.SMS_System.dbo.MS_Rigor r on substring(ua.mobile,1,7) = r.起始号段 where mt.userid = ua.userid and year(mt.time) = year(@statTime) and month(mt.time) = month(@statTime) and day(mt.time) = day(@statTime) and datepart(hh,mt.time) = datepart(hh,@statTime) --匹配时间 and mt.Result = 'DELIVRD'--成功发送 group by ua.SPID,mt.BusinessType ,r.运营商名称,r.地区名称

总结要点：1、declare set的用法.

2、dateadd(dd,datediff(dd,0,someDate),0)的用法

比如：获取当前时间的上一个小时

select dateadd(hh,datediff(hh,0,getdate())-1,0)

--'2008-06-26 09:00:00'

3、语法---case when condition then result1 else result2 end (as) '别名'4、table left join anotherTable on table.key = anotherTable.key