SQL技巧：创建用来按小时报告的查询

要创建一个可以每个小时报告的查询，首先要创建一个表格。该表格一列记录日期，而没有时间信息；另一列记录钟点。下面的表格有一列记录了不同的处理类型。例如，我们可以按小时找出处理类型的总数。;CREATE;TABLE;test(StartTime;DATETIME;NOT;NULLDEFAULT;CURRENT_TIMESTAMP,StartDate;DATETIME;NOT;NULLDEFAULT;CONVERT(DATETIME,;CONVERT(CHAR(10),CURRENT_TIMESTAMP,;110)),StartHour;INT;NOT;NULLDEFAULT;DATEPART(hh,CURRENT_TIMESTAMP),TranType;INT;NOT;NULLCONSTRAINT;ck_TranType;CHECK;(;TranType;IN(;1,;--;insert2,;--;update3,;--;delete)DEFAULT;1)GO;接下来，插入test的数据来模拟一个可能的样本。INSERT;test;(StartTime,;TranType);VALUES;(CURRENT_TIMESTAMP,;3)INSERT;test;(StartTime,;TranType);VALUES;(CURRENT_TIMESTAMP,;2)INSERT;test;(StartTime,;TranType);VALUES;(CURRENT_TIMESTAMP,;3)GODECLARE;@hr;intSET;@hr;=;DATEPART(hh,;DATEADD(hh,-1,CURRENT_TIMESTAMP);)INSERT;test;(StartTime,;TranType,;StartHour);_;;VALUES;(DATEADD(hh,-1,CURRENT_TIMESTAMP),;3,;@hr)INSERT;test;(StartTime,;TranType,;StartHour);_;;VALUES;(DATEADD(hh,-1,CURRENT_TIMESTAMP),;1,;@hr)INSERT;test;(StartTime,;TranType,;StartHour);_;;VALUES;(DATEADD(hh,-1,CURRENT_TIMESTAMP),;2,;@hr)GO;然后用一个查询来找出按日和小时的处理总数。SELECT;StartDate;tran_day,StartHour;tran_hour,;CASE;trantype;WHEN;1;THEN;'insert'WHEN;2;THEN;'update'WHEN;3;THEN;'delete'ELSE;'unknown'END;trantype,COUNT(*);tran_totalFROMTestGROUP;BYStartDate,StartHour,trantypeORDER;BY;StartDate,;StartHourCOMPUTE;SUM(COUNT(*));BY;StartDate,;StartHourGO;去掉test可以清空test表格。DROP;TABLE;testGO;