tableAId Type Time7
1 0 2013-01-03 00:00:18
2 1 2013-01-05 01:20:07
3 2 2013-01-08 23:08:02
4 2 2013-01-08 01:07:02
tableC
TypeId TypeName
0 AAA
1 BBB
2 CCCtableA的Type字段对应tableC的TypeId字段我想统计出这样一个效果时段 00:00:01-01:00:00 01:00:01-02:00:00 ... 23:00:01-24:00:00 合计
AAA 1 0 0 1
BBB 0 1 0 1
CCC 0 1 1 2
合计 1 2 1 4
其中时段这一行有24列,分别对应24个小时请问这个语句应该怎么写?
1 0 2013-01-03 00:00:18
2 1 2013-01-05 01:20:07
3 2 2013-01-08 23:08:02
4 2 2013-01-08 01:07:02
tableC
TypeId TypeName
0 AAA
1 BBB
2 CCCtableA的Type字段对应tableC的TypeId字段我想统计出这样一个效果时段 00:00:01-01:00:00 01:00:01-02:00:00 ... 23:00:01-24:00:00 合计
AAA 1 0 0 1
BBB 0 1 0 1
CCC 0 1 1 2
合计 1 2 1 4
其中时段这一行有24列,分别对应24个小时请问这个语句应该怎么写?
参考http://bbs.csdn.net/topics/320026119
最好是能给个具体的sql语句,这样学起来更加方便一点
GO
-->生成表tableAif object_id('tableA') is not null
drop table tableA
Go
Create table tableA([Id] smallint,[Type] nvarchar(1),[Time7] datetime)
Insert into tableA
Select 1,N'0','2013-01-03 00:00:18'
Union all Select 2,N'1','2013-01-05 01:20:07'
Union all Select 3,N'2','2013-01-08 23:08:02'
Union all Select 4,N'2','2013-01-08 01:07:02'-->生成表tableCif object_id('tableC') is not null
drop table tableC
Go
Create table tableC([TypeId] nvarchar(1),[TypeName] nvarchar(3))
Insert into tableC
Select N'0',N'AAA'
Union all Select N'1',N'BBB'
Union all Select N'2',N'CCC'SELECT
TypeName AS [时段]
,SUM(CASE WHEN Time >'1900-01-01 00:00:00' AND Time <='1900-01-01 01:00:00' THEN 1 ELSE 0 END) AS [00:00:01-01:00:00]
,SUM(CASE WHEN Time >'1900-01-01 01:00:00' AND Time <='1900-01-01 02:00:00' THEN 1 ELSE 0 END) AS [01:00:01-02:00:00]
,SUM(CASE WHEN Time >'1900-01-01 02:00:00' AND Time <='1900-01-01 03:00:00' THEN 1 ELSE 0 END) AS [02:00:01-03:00:00]
,SUM(CASE WHEN Time >'1900-01-01 03:00:00' AND Time <='1900-01-01 04:00:00' THEN 1 ELSE 0 END) AS [03:00:01-04:00:00]
,SUM(CASE WHEN Time >'1900-01-01 04:00:00' AND Time <='1900-01-01 05:00:00' THEN 1 ELSE 0 END) AS [04:00:01-05:00:00]
,SUM(CASE WHEN Time >'1900-01-01 05:00:00' AND Time <='1900-01-01 06:00:00' THEN 1 ELSE 0 END) AS [05:00:01-06:00:00]
,SUM(CASE WHEN Time >'1900-01-01 06:00:00' AND Time <='1900-01-01 07:00:00' THEN 1 ELSE 0 END) AS [06:00:01-07:00:00]
,SUM(CASE WHEN Time >'1900-01-01 07:00:00' AND Time <='1900-01-01 08:00:00' THEN 1 ELSE 0 END) AS [07:00:01-08:00:00]
,SUM(CASE WHEN Time >'1900-01-01 08:00:00' AND Time <='1900-01-01 09:00:00' THEN 1 ELSE 0 END) AS [08:00:01-09:00:00]
,SUM(CASE WHEN Time >'1900-01-01 09:00:00' AND Time <='1900-01-01 10:00:00' THEN 1 ELSE 0 END) AS [09:00:01-10:00:00]
,SUM(CASE WHEN Time >'1900-01-01 10:00:00' AND Time <='1900-01-01 11:00:00' THEN 1 ELSE 0 END) AS [10:00:01-11:00:00]
,SUM(CASE WHEN Time >'1900-01-01 11:00:00' AND Time <='1900-01-01 12:00:00' THEN 1 ELSE 0 END) AS [11:00:01-12:00:00]
,SUM(CASE WHEN Time >'1900-01-01 12:00:00' AND Time <='1900-01-01 13:00:00' THEN 1 ELSE 0 END) AS [12:00:01-13:00:00]
,SUM(CASE WHEN Time >'1900-01-01 13:00:00' AND Time <='1900-01-01 14:00:00' THEN 1 ELSE 0 END) AS [13:00:01-14:00:00]
,SUM(CASE WHEN Time >'1900-01-01 14:00:00' AND Time <='1900-01-01 15:00:00' THEN 1 ELSE 0 END) AS [14:00:01-15:00:00]
,SUM(CASE WHEN Time >'1900-01-01 15:00:00' AND Time <='1900-01-01 16:00:00' THEN 1 ELSE 0 END) AS [15:00:01-16:00:00]
,SUM(CASE WHEN Time >'1900-01-01 16:00:00' AND Time <='1900-01-01 17:00:00' THEN 1 ELSE 0 END) AS [16:00:01-17:00:00]
,SUM(CASE WHEN Time >'1900-01-01 17:00:00' AND Time <='1900-01-01 18:00:00' THEN 1 ELSE 0 END) AS [17:00:01-18:00:00]
,SUM(CASE WHEN Time >'1900-01-01 18:00:00' AND Time <='1900-01-01 19:00:00' THEN 1 ELSE 0 END) AS [18:00:01-19:00:00]
,SUM(CASE WHEN Time >'1900-01-01 19:00:00' AND Time <='1900-01-01 20:00:00' THEN 1 ELSE 0 END) AS [19:00:01-20:00:00]
,SUM(CASE WHEN Time >'1900-01-01 20:00:00' AND Time <='1900-01-01 21:00:00' THEN 1 ELSE 0 END) AS [20:00:01-21:00:00]
,SUM(CASE WHEN Time >'1900-01-01 21:00:00' AND Time <='1900-01-01 22:00:00' THEN 1 ELSE 0 END) AS [21:00:01-22:00:00]
,SUM(CASE WHEN Time >'1900-01-01 22:00:00' AND Time <='1900-01-01 23:00:00' THEN 1 ELSE 0 END) AS [22:00:01-23:00:00]
,SUM(CASE WHEN Time >'1900-01-01 23:00:00' THEN 1 ELSE 0 END) AS [23:00:01-24:00:00]
,COUNT(Time) AS [合计]
FROM (
SELECT
a.Id
,b.TypeName
,CONVERT(DATETIME,CONVERT(VARCHAR(8),a.Time7,108)) As Time
FROM
tableA AS a
,tableC AS b
WHERE a.Type=b.TypeId
UNION ALL
SELECT
t.Max_Id+1
,N'合计'
,CONVERT(DATETIME,CONVERT(VARCHAR(8),Time7,108)) As Time
FROM
tableA
,(SELECT MAX(Id) AS Max_Id FROM tableA) AS t
) AS t
GROUP BY TypeName/*
时段 00:00:01-01:00:00 01:00:01-02:00:00 02:00:01-03:00:00 03:00:01-04:00:00 04:00:01-05:00:00 05:00:01-06:00:00 06:00:01-07:00:00 07:00:01-08:00:00 08:00:01-09:00:00 09:00:01-10:00:00 10:00:01-11:00:00 11:00:01-12:00:00 12:00:01-13:00:00 13:00:01-14:00:00 14:00:01-15:00:00 15:00:01-16:00:00 16:00:01-17:00:00 17:00:01-18:00:00 18:00:01-19:00:00 19:00:01-20:00:00 20:00:01-21:00:00 21:00:01-22:00:00 22:00:01-23:00:00 23:00:01-24:00:00 合计
---- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- ----------------- -----------
AAA 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1
BBB 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1
CCC 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 2
合计 1 2 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 4*/
go
a.Id
,b.TypeName
,CONVERT(DATETIME,CONVERT(VARCHAR(8),a.Time7,108)) As Time
FROM
tableA AS a
,tableC AS b
WHERE a.Type=b.TypeId
And a.Time7 Between @startTime And @endTime
UNION ALL
SELECT
t.Max_Id+1
,N'合计'
,CONVERT(DATETIME,CONVERT(VARCHAR(8),Time7,108)) As Time
FROM
tableA
,(SELECT MAX(Id) AS Max_Id FROM tableA) AS t
Where Time7 Between @startTime And @endTime