本文來源于網(wǎng)頁設(shè)計(jì)愛好者web開發(fā)社區(qū)http://www.html.org.cn收集整理，歡迎訪問。要?jiǎng)?chuàng)建一個(gè)可以每個(gè)小時(shí)報(bào)告的查詢，首先要?jiǎng)?chuàng)建一個(gè)表格。該表格一列記錄日期，而沒有時(shí)間信息；另一列記錄鐘點(diǎn)。下面的表格有一列記錄了不同的處理類型。例如，我們可以按小時(shí)找出處理類型的總數(shù)。
create table test
(starttime datetime not null
default current_timestamp,
startdate datetime not null
default convert(datetime, convert(char(10),current_timestamp, 110)),
starthour int not null
default datepart(hh,current_timestamp),
trantype int not null
constraint ck_trantype check ( trantype in
(
1, -- insert
2, -- update
3, -- delete
)
default 1
)
go
接下來，插入test的數(shù)據(jù)來模擬一個(gè)可能的樣本。
insert test (starttime, trantype) values (current_timestamp, 3)
insert test (starttime, trantype) values (current_timestamp, 2)
insert test (starttime, trantype) values (current_timestamp, 3)
go
declare @hr int
set @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
然后用一個(gè)查詢來找出按日和小時(shí)的處理總數(shù)。
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_total
from
test
group by
startdate,
starthour
,trantype
order by startdate, starthour
compute sum(count(*)) by startdate, starthour
go
去掉test可以清空test表格。
drop table test
go
