这个语句应该怎么写?

abz87870 2013-01-23 11:57:51

tableA

Id 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 CCC

tableA的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个小时

请问这个语句应该怎么写?

...全文

186 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

-Tracy-McGrady- 2013-01-23

打赏
举报

关键字查找吧，里面全是

-Tracy-McGrady- 2013-01-23

打赏
举报

abz87870 2013-01-23

打赏
举报

引用 4 楼 yangsh0722 的回复:

引用 3 楼 abz87870 的回复: 有谁能给我写一下具体的sql语句吗? 人家不给你发链接了嘛，自己学一下，下次就不用问人了。

最好是能给个具体的sql语句,这样学起来更加方便一点

-Tracy-McGrady- 2013-01-23

打赏
举报

引用 3 楼 abz87870 的回复:

有谁能给我写一下具体的sql语句吗?

人家不给你发链接了嘛，自己学一下，下次就不用问人了。

abz87870 2013-01-23

打赏
举报

有谁能给我写一下具体的sql语句吗?

-Tracy-McGrady- 2013-01-23

打赏
举报

行列转化！！

Mr_Nice 2013-01-23

打赏
举报

典型的行列转化参考http://bbs.csdn.net/topics/320026119

我腫了 2013-01-23

打赏
举报

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

abz87870 2013-01-23

打赏
举报

引用 8 楼 bbbbbben 的回复:

SQL code ? 12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485 USE test GO ……

谢谢你写的语句,我想请问一下,假如要加个时间搜索条件的话,应该放在哪里?

我腫了 2013-01-23

打赏
举报

USE test
GO


-->生成表tableA

if 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'

-->生成表tableC

if 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

sql2000学生管理数据库这是个备份的数据库，用于练习SQL语句，在SQL2000中恢复该数据库就可以使用里面的数据了，注意恢复时在选项里要更改一下恢复的路径，还要更改恢复后的数据库的名字，里面有三张表student,course,SC

我们从键盘输入语句或者是代码的时候，其实Python提供了一些标准的输入函数，今天我们要学习的是input函数，很多的小伙伴不知道如何去使用它，它的主要作用是标准的输入读入一行文本。下面让我们一起来看看今天这篇有关Python输入语句是什么？在Python中，从键盘上输入的语句使用的是input函数，它是Python中的一个内置函数，表示的是从键盘中标准的输入，它还可以接收内容为一个表达式，最后会将运行结果进行返回。以上就是有关Python输入语句是什么？的全部内容啦，希望对大家的学习能够带来一定的帮助。

作为初步进入java开发学习的小白来说，就像是小时候刚刚学说话一样，这种经历既是必然的也是有趣的，学习java语言一开始的时候也是得一步步的学习，比如说，java输入语句应该这么去实现呢?一起跟小编来看看吧。第一步:导包。先将java.io.*;以及java.util.*;导入Java代码中。char c=(char)System.in.read();是输入单个字符;int a=cin.nextI...

指针变量p与“零值”进行比较的if 语句怎么写？

java中的复杂查询sql语句实现