id Time c_ID 种类
------------------------------------------------------------
11 2009-09-23 11:06:59.000 2 A
12 2009-09-23 11:06:59.000 3 A
13 2009-09-23 13:06:59.000 4 A
14 2009-09-23 14:06:59.000 5 A
15 2009-09-23 15:06:59.000 7 A
16 2009-09-23 15:06:59.000 6 A
17 2009-09-23 16:06:59.000 7 B
18 2009-09-23 17:06:59.000 8 B
19 2009-09-23 17:06:59.000 9 B
20 2009-09-23 17:06:59.000 10 B
21 2009-09-23 14:06:59.000 11 A
22 2009-09-23 14:06:59.000 12 A怎么计算一天内各个时间(8~22小时)段内的总和,比如
种类 8 9 10 11 12 13 14 15 16 17.....
----------------------------------------------
A 0 0 0 2 0 1 3 2 0 0 ..........
B 0 0 0 0 0 0 0 0 1 3 ..........
谢谢各位高手了.
------------------------------------------------------------
11 2009-09-23 11:06:59.000 2 A
12 2009-09-23 11:06:59.000 3 A
13 2009-09-23 13:06:59.000 4 A
14 2009-09-23 14:06:59.000 5 A
15 2009-09-23 15:06:59.000 7 A
16 2009-09-23 15:06:59.000 6 A
17 2009-09-23 16:06:59.000 7 B
18 2009-09-23 17:06:59.000 8 B
19 2009-09-23 17:06:59.000 9 B
20 2009-09-23 17:06:59.000 10 B
21 2009-09-23 14:06:59.000 11 A
22 2009-09-23 14:06:59.000 12 A怎么计算一天内各个时间(8~22小时)段内的总和,比如
种类 8 9 10 11 12 13 14 15 16 17.....
----------------------------------------------
A 0 0 0 2 0 1 3 2 0 0 ..........
B 0 0 0 0 0 0 0 0 1 3 ..........
谢谢各位高手了.
參照
http://topic.csdn.net/u/20080612/22/c850499f-bce3-4877-82d5-af2357857872.html
select
种类,
max(case when datepart(hour,Time)=1 then c_ID else 0 end) as [1],
max(case when datepart(hour,Time)=2 then c_ID else 0 end) as [2],
.......
from table1
group by 种类
-- Author :fredrickhu(我是小F，向高手学习)
-- Date :2009-09-27 15:10:07
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[Time] datetime,[c_ID] int,[种类] varchar(1))
insert [tb]
select 11,'2009-09-23 11:06:59.000',2,'A' union all
select 12,'2009-09-23 11:06:59.000',3,'A' union all
select 13,'2009-09-23 13:06:59.000',4,'A' union all
select 14,'2009-09-23 14:06:59.000',5,'A' union all
select 15,'2009-09-23 15:06:59.000',7,'A' union all
select 16,'2009-09-23 15:06:59.000',6,'A' union all
select 17,'2009-09-23 16:06:59.000',7,'B' union all
select 18,'2009-09-23 17:06:59.000',8,'B' union all
select 19,'2009-09-23 17:06:59.000',9,'B' union all
select 20,'2009-09-23 17:06:59.000',10,'B' union all
select 21,'2009-09-23 14:06:59.000',11,'A' union all
select 22,'2009-09-23 14:06:59.000',12,'A'
--------------开始查询--------------------------
declare @sql varchar(8000)
set @sql = 'select [种类] '
select @sql = @sql + ' , sum(case ltrim([Time]) when ''' + ltrim([Time]) + ''' then 1 else 0 end) [' + ltrim([Time]) + ']'
from (select distinct [Time] from tb) as a
set @sql = @sql + ' from tb group by [种类]'
exec(@sql)
----------------结果----------------------------
/* 种类 09 23 2009 11:06AM 09 23 2009 1:06PM 09 23 2009 2:06PM 09 23 2009 3:06PM 09 23 2009 4:06PM 09 23 2009 5:06PM
---- ------------------ ------------------ ------------------ ------------------ ------------------ ------------------
A 2 1 3 2 0 0
B 0 0 0 0 1 3(2 行受影响)*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[Time] datetime,[c_ID] int,[种类] varchar(1))
insert [tb]
select 11,'2009-09-23 11:06:59.000',2,'A' union all
select 12,'2009-09-23 11:06:59.000',3,'A' union all
select 13,'2009-09-23 13:06:59.000',4,'A' union all
select 14,'2009-09-23 14:06:59.000',5,'A' union all
select 15,'2009-09-23 15:06:59.000',7,'A' union all
select 16,'2009-09-23 15:06:59.000',6,'A' union all
select 17,'2009-09-23 16:06:59.000',7,'B' union all
select 18,'2009-09-23 17:06:59.000',8,'B' union all
select 19,'2009-09-23 17:06:59.000',9,'B' union all
select 20,'2009-09-23 17:06:59.000',10,'B' union all
select 21,'2009-09-23 14:06:59.000',11,'A' union all
select 22,'2009-09-23 14:06:59.000',12,'A'select * from [tb]
declare @s varchar(1000)
declare @i int
set @I=8
while @i<=22
begin
set @s=isnull(@s+',','')+'['+ltrim(@i)+']=sum(case when datepart(hour,time)='+ltrim(@i)+' then 1 else 0 end)'
set @i=@i+1
end
exec('select 种类,'+@s+' from tb group by 种类')
/*
种类 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
---- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
A 0 0 0 2 0 1 3 2 0 0 0 0 0 0 0
B 0 0 0 0 0 0 0 0 1 3 0 0 0 0 0(2 行受影响)*/
if object_id('tb')is not null drop table tb
go
CREATE TABLE tb(id int, Time datetime,c_ID int,种类 varchar(10))
INSERT tb SELECT
11, '2009-09-23 11:06:59' ,2, 'A' union all select
12, '2009-09-23 11:06:59' ,3, 'A' union all select
13, '2009-09-23 13:06:59' ,4, 'A' union all select
14, '2009-09-23 14:06:59' ,5, 'A' union all select
15, '2009-09-23 15:06:59' ,7, 'A' union all select
16, '2009-09-23 15:06:59' ,6, 'A' union all select
17, '2009-09-23 16:06:59' ,7, 'B' union all select
18, '2009-09-23 17:06:59' ,8, 'B' union all select
19, '2009-09-23 17:06:59' ,9, 'B' union all select
20, '2009-09-23 17:06:59' ,10, 'B' union all select
21, '2009-09-23 14:06:59', 11, 'A' union all select
22, '2009-09-23 14:06:59', 12, 'A' declare @s varchar(4000)select @s=isnull(@s+',','')+'sum(case when datepart(hour,time)='''+ltrim(number)+''' then 1 else 0 end) ['+ltrim(number)+']'
from master..spt_values
where type='p' and number between 8 and 22set @s='select 种类 ,'+@s+' from tb group by 种类 'exec(@s)种类 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
---------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
A 0 0 0 2 0 1 3 2 0 0 0 0 0 0 0
B 0 0 0 0 0 0 0 0 1 3 0 0 0 0 0(2 行受影响)
declare @tb table (id int,Time datetime,c_ID int,种类 varchar(1))
insert into @tb
select 11,'2009-09-23 11:06:59.000',2,'A' union all
select 12,'2009-09-23 11:06:59.000',3,'A' union all
select 13,'2009-09-23 13:06:59.000',4,'A' union all
select 14,'2009-09-23 14:06:59.000',5,'A' union all
select 15,'2009-09-23 15:06:59.000',7,'A' union all
select 16,'2009-09-23 15:06:59.000',6,'A' union all
select 17,'2009-09-23 16:06:59.000',7,'B' union all
select 18,'2009-09-23 17:06:59.000',8,'B' union all
select 19,'2009-09-23 17:06:59.000',9,'B' union all
select 20,'2009-09-23 17:06:59.000',10,'B' union all
select 21,'2009-09-23 14:06:59.000',11,'A' union all
select 22,'2009-09-23 14:06:59.000',12,'A'--select * from @tbselect
种类,
max(case when datepart(hour,Time)=8 then c_ID else 0 end) as [8],
max(case when datepart(hour,Time)=9 then c_ID else 0 end) as [9],
max(case when datepart(hour,Time)=10 then c_ID else 0 end) as [10],
max(case when datepart(hour,Time)=11 then c_ID else 0 end) as [11],
max(case when datepart(hour,Time)=12 then c_ID else 0 end) as [12],
max(case when datepart(hour,Time)=13 then c_ID else 0 end) as [13],
max(case when datepart(hour,Time)=14 then c_ID else 0 end) as [14],
max(case when datepart(hour,Time)=15 then c_ID else 0 end) as [15],
max(case when datepart(hour,Time)=16 then c_ID else 0 end) as [16],
max(case when datepart(hour,Time)=17 then c_ID else 0 end) as [17],
max(case when datepart(hour,Time)=18 then c_ID else 0 end) as [18],
max(case when datepart(hour,Time)=19 then c_ID else 0 end) as [19],
max(case when datepart(hour,Time)=20 then c_ID else 0 end) as [20],
max(case when datepart(hour,Time)=21 then c_ID else 0 end) as [21],
max(case when datepart(hour,Time)=22 then c_ID else 0 end) as [22]
from @tb
group by 种类/*(所影响的行数为 12 行）种类 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
---- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
A 0 0 0 3 0 4 12 7 0 0 0 0 0 0 0
B 0 0 0 0 0 0 0 0 7 10 0 0 0 0 0（所影响的行数为 2 行）*/
-- Author :fredrickhu(我是小F，向高手学习)
-- Date :2009-09-27 15:10:07
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[Time] datetime,[c_ID] int,[种类] varchar(1))
insert [tb]
select 11,'2009-09-23 11:06:59.000',2,'A' union all
select 12,'2009-09-23 11:06:59.000',3,'A' union all
select 13,'2009-09-23 13:06:59.000',4,'A' union all
select 14,'2009-09-23 14:06:59.000',5,'A' union all
select 15,'2009-09-23 15:06:59.000',7,'A' union all
select 16,'2009-09-23 15:06:59.000',6,'A' union all
select 17,'2009-09-23 16:06:59.000',7,'B' union all
select 18,'2009-09-23 17:06:59.000',8,'B' union all
select 19,'2009-09-23 17:06:59.000',9,'B' union all
select 20,'2009-09-23 17:06:59.000',10,'B' union all
select 21,'2009-09-23 14:06:59.000',11,'A' union all
select 22,'2009-09-23 14:06:59.000',12,'A'
--------------开始查询--------------------------select
[种类] ,
sum(case when datepart(hh,Time)=8 then 1 else 0 end) [8] ,
sum(case when datepart(hour,Time)=9 then 1 else 0 end) [9] ,
sum(case when datepart(hour,Time)=10 then 1 else 0 end) [10] ,
sum(case when datepart(hour,Time)=11 then 1 else 0 end) [11] ,
sum(case when datepart(hour,Time)=12 then 1 else 0 end) [12] ,
sum(case when datepart(hour,Time)=13 then 1 else 0 end) [13],
sum(case when datepart(hour,Time)=14 then 1 else 0 end) [14],
sum(case when datepart(hour,Time)=15 then 1 else 0 end)[15],
sum(case when datepart(hour,Time)=16 then 1 else 0 end)[16],
sum(case when datepart(hour,Time)=17 then 1 else 0 end)[17],
sum(case when datepart(hour,Time)=18 then 1 else 0 end)[18],
sum(case when datepart(hour,Time)=19 then 1 else 0 end)[19],
sum(case when datepart(hour,Time)=20 then 1 else 0 end)[20],
sum(case when datepart(hour,Time)=21 then 1 else 0 end)[21],
sum(case when datepart(hour,Time)=22 then 1 else 0 end)[22]
from
tb
group by [种类]----------------结果----------------------------
/* 种类 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
---- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
A 0 0 0 2 0 1 3 2 0 0 0 0 0 0 0
B 0 0 0 0 0 0 0 0 1 3 0 0 0 0 0(2 行受影响)*/
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c)
1988-2008 Microsoft Corporation Enterprise Evaluation Edition on Windows NT 5.1 <X86>
(Build 2600: Service Pack 3)
愿和大家共同进步
如有雷同、实属巧合
●●●●●2009-09-27 15:46:38.653●●●●●
★★★★★soft_wsx★★★★★
*/
if object_ID('TB') IS NOT NULL DROP TABLE TB
GO
create table tb(id int,time datetime,c_id int,zl nvarchar(10))
GO
insert tb
select'11', '2009-09-23 11:06:59.000', 2, 'A' union all select
'12', '2009-09-23 11:06:59.000', 3, 'A' union all select
'13', '2009-09-23 13:06:59.000', 4, 'A' union all select
'14', '2009-09-23 14:06:59.000', 5, 'A' union all select
'15', '2009-09-23 15:06:59.000', 7, 'A' union all select
'16', '2009-09-23 15:06:59.000', 6, 'A' union all select
'17', '2009-09-23 16:06:59.000', 7, 'B' union all select
'18', '2009-09-23 17:06:59.000', 8, 'B' union all select
'19', '2009-09-23 17:06:59.000', 9, 'B' union all select
'20', '2009-09-23 17:06:59.000', 10, 'B' union all select
'21', '2009-09-23 14:06:59.000', 11, 'A' union all select
'22', '2009-09-23 14:06:59.000', 12 ,'A'
declare @sql nvarchar(4000)
SET @sql=N'select [zl]' --初始化变量必须
select @sql=@sql+N','+
QUOTENAME(right(100+DATENAME(hour,time),2))+
N'=sum(
case when DATENAME(hour,time)='+quotename(right(100+DATENAME(hour,time),2),N'''')
+N' then 1 else 0 end)'
from (select convert(nvarchar,dateadd(hour,b.number,date),120) as time
from (select distinct convert(nvarchar(10),time,120)+' 08:00:00:000' as date from tb) a
inner join master..spt_values b
on b.number<=14 and b.type='p')a
group by right(100+DATENAME(hour,time),2)
order by right(100+DATENAME(hour,time),2)
set @sql=@sql+N' from tb group by zl'
print @sql
exec(@sql)
/*
zl 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22
A 0 0 0 2 0 1 3 2 0 0 0 0 0 0 0
B 0 0 0 0 0 0 0 0 1 3 0 0 0 0 0
*//*
select [zl],[08]=sum(
case when DATENAME(hour,time)='08' then 1 else 0 end),[09]=sum(
case when DATENAME(hour,time)='09' then 1 else 0 end),[10]=sum(
case when DATENAME(hour,time)='10' then 1 else 0 end),[11]=sum(
case when DATENAME(hour,time)='11' then 1 else 0 end),[12]=sum(
case when DATENAME(hour,time)='12' then 1 else 0 end),[13]=sum(
case when DATENAME(hour,time)='13' then 1 else 0 end),[14]=sum(
case when DATENAME(hour,time)='14' then 1 else 0 end),[15]=sum(
case when DATENAME(hour,time)='15' then 1 else 0 end),[16]=sum(
case when DATENAME(hour,time)='16' then 1 else 0 end),[17]=sum(
case when DATENAME(hour,time)='17' then 1 else 0 end),[18]=sum(
case when DATENAME(hour,time)='18' then 1 else 0 end),[19]=sum(
case when DATENAME(hour,time)='19' then 1 else 0 end),[20]=sum(
case when DATENAME(hour,time)='20' then 1 else 0 end),[21]=sum(
case when DATENAME(hour,time)='21' then 1 else 0 end),[22]=sum(
case when DATENAME(hour,time)='22' then 1 else 0 end) from tb group by zl(2 行受影响)*/
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c)
1988-2008 Microsoft Corporation Enterprise Evaluation Edition on Windows NT 5.1 <X86>
(Build 2600: Service Pack 3)
愿和大家共同进步
如有雷同、实属巧合
●●●●●2009-09-27 15:46:38.653●●●●●
★★★★★soft_wsx★★★★★
*/
if object_ID('TB') IS NOT NULL DROP TABLE TB
GO
create table tb(id int,time datetime,c_id int,zl nvarchar(10))
GO
insert tb
select'11', '2009-09-23 11:06:59.000', 2, 'A' union all select
'12', '2009-09-23 11:06:59.000', 3, 'A' union all select
'13', '2009-09-23 13:06:59.000', 4, 'A' union all select
'14', '2009-09-23 14:06:59.000', 5, 'A' union all select
'15', '2009-09-23 15:06:59.000', 7, 'A' union all select
'16', '2009-09-23 15:06:59.000', 6, 'A' union all select
'17', '2009-09-23 16:06:59.000', 7, 'B' union all select
'18', '2009-09-23 17:06:59.000', 8, 'B' union all select
'19', '2009-09-23 17:06:59.000', 9, 'B' union all select
'20', '2009-09-23 17:06:59.000', 10, 'B' union all select
'21', '2009-09-23 14:06:59.000', 11, 'A' union all select
'22', '2009-09-23 14:06:59.000', 12 ,'A' union all select '12', '2009-09-24 11:06:59.000', 3, 'C' union all select
'13', '2009-09-24 13:06:59.000', 4, 'C' union all select
'14', '2009-09-24 14:06:59.000', 5, 'C' union all select
'15', '2009-09-24 15:06:59.000', 7, 'C' union all select
'16', '2009-09-24 15:06:59.000', 6, 'C' union all select
'17', '2009-09-24 16:06:59.000', 7, 'C' union all select
'18', '2009-09-24 17:06:59.000', 8, 'C' union all select
'19', '2009-09-24 17:06:59.000', 9, 'C' union all select
'20', '2009-09-24 17:06:59.000', 10, 'C' union all select
'21', '2009-09-24 14:06:59.000', 11, 'C' union all select
'22', '2009-09-24 14:06:59.000', 12 ,'C'
declare @sql nvarchar(4000)
SET @sql=N'select [zl]' --初始化变量必须
select @sql=@sql+N','+
QUOTENAME(right(100+DATENAME(hour,time),2))+
N'=sum(
case when DATENAME(hour,time)='+quotename(right(100+DATENAME(hour,time),2),N'''')
+N' then 1 else 0 end)'
from (select convert(nvarchar,dateadd(hour,b.number,date),120) as time
from (select distinct convert(nvarchar(10),time,120)+' 08:00:00:000' as date from tb) a
inner join master..spt_values b
on b.number<=14 and b.type='p')a
group by right(100+DATENAME(hour,time),2)
order by right(100+DATENAME(hour,time),2)
set @sql=@sql+N' from tb group by zl'
print @sql
exec(@sql)
/*
zl 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22
A 0 0 0 2 0 1 3 2 0 0 0 0 0 0 0
B 0 0 0 0 0 0 0 0 1 3 0 0 0 0 0
C 0 0 0 1 0 1 3 2 1 3 0 0 0 0 0
*//*
select [zl],[08]=sum(
case when DATENAME(hour,time)='08' then 1 else 0 end),[09]=sum(
case when DATENAME(hour,time)='09' then 1 else 0 end),[10]=sum(
case when DATENAME(hour,time)='10' then 1 else 0 end),[11]=sum(
case when DATENAME(hour,time)='11' then 1 else 0 end),[12]=sum(
case when DATENAME(hour,time)='12' then 1 else 0 end),[13]=sum(
case when DATENAME(hour,time)='13' then 1 else 0 end),[14]=sum(
case when DATENAME(hour,time)='14' then 1 else 0 end),[15]=sum(
case when DATENAME(hour,time)='15' then 1 else 0 end),[16]=sum(
case when DATENAME(hour,time)='16' then 1 else 0 end),[17]=sum(
case when DATENAME(hour,time)='17' then 1 else 0 end),[18]=sum(
case when DATENAME(hour,time)='18' then 1 else 0 end),[19]=sum(
case when DATENAME(hour,time)='19' then 1 else 0 end),[20]=sum(
case when DATENAME(hour,time)='20' then 1 else 0 end),[21]=sum(
case when DATENAME(hour,time)='21' then 1 else 0 end),[22]=sum(
case when DATENAME(hour,time)='22' then 1 else 0 end) from tb group by zl(2 行受影响)*/
if object_id('a') is not null
drop table a
go
create table a(id int , Time datetime , c_id int , type varchar(5))
go
insert into a
select 11 ,'2009-09-23 11:06:59.000', 2 ,'A' union all
select 12 ,'2009-09-23 11:06:59.000',3 ,'A' union all
select 13 ,'2009-09-23 13:06:59.000',4 ,'A' union all
select 14 ,'2009-09-23 14:06:59.000',5 ,'A' union all
select 15 ,'2009-09-23 15:06:59.000',7 ,'A' union all
select 16 ,'2009-09-23 15:06:59.000',6 ,'A' union all
select 17 ,'2009-09-23 16:06:59.000',7 ,'B' union all
select 18 ,'2009-09-23 17:06:59.000',8 ,'B'union all
select 19 ,'2009-09-23 17:06:59.000',9 ,'B' union all
select 20 ,'2009-09-23 17:06:59.000', 10 ,'B' union all
select 21 ,'009-09-23 14:06:59.000', 11 ,'A' union all
select 22 ,'2009-09-23 14:06:59.000' , 12 ,'A'
go
with
tb as
(
select type , [8] , [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22]
from (select id , left(convert(varchar(8) , Time , 108) , 2) as t_min , c_id , type from a) t1
pivot(count(c_id) for t_min
in ([8] , [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22]))t2
)
select type , sum([8]) as[8] ,sum([9]) as[9],sum([10]) as[10],sum([11]) as[11],sum([12]) as[12],sum([13]) as[13],sum([14]) as[14],sum([15]) as[15]
,sum([16]) as[16],sum([17]) as[17],sum([18]) as[18],sum([19]) as[19],sum([20]) as[20],sum([21]) as[21],sum([22]) as[22]
from tb group by type
=============================================================