计算总和

[智者潜行]

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 ..........


谢谢各位高手了.

[SQL77]

又行转列
參照
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 种类

[soft_wsx]

等一下先！

[--小F--]

----------------------------------------------------------------
-- 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 行受影响)

*/

[xiequan2]

--> 测试数据：[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 * 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 22

set @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 行受影响)

[microsofttyc]

--> 测试数据: @tb
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 @tb

select 
种类, 
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 行）*/

[--小F--]

----------------------------------------------------------------
-- 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 行受影响)

*/

[soft_wsx]

/*
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 行受影响)

*/

[soft_wsx]

/*
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

=============================================================

[小宏]

搞复杂了
呵呵向各位老大学习。。。。

[navy887]

学习

[wolfmvp]

路过，没见过这么复杂的，汗…

[fanzhouqi]

看来蹭不到分了

[ruby512]

不是正解都出来了 何必再发这么多代码呢？

[soft_wsx]

[Quote=引用 16 楼 ruby512 的回复:]
不是正解都出来了 何必再发这么多代码呢？
[/Quote]网速慢！