一个比较复杂的sql

declare @statTime datetime, @endTime datetime

set @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