求一个简单SQL 语句

pennymay 2009-04-22 11:26:50
这个是TABLE
stem volume Starttime
1 2.1603 2009-03-11 06:36:30.000
1 2.1517 2009-03-11 06:39:01.000
1 2.1589 2009-03-11 06:40:26.000
1 2.166 2009-03-11 06:42:27.000
1 0.8291 2009-03-11 06:48:58.000
1 1.3643 2009-03-11 06:50:27.000
1 1.6176 2009-03-11 06:51:58.000
1 1.6746 2009-03-11 07:08:47.000
1 1.6529 2009-03-11 07:10:24.000
1 1.7157 2009-03-11 07:19:22.000
1 1.2184 2009-03-11 07:21:27.000
1 2.0793 2009-03-11 07:28:27.000
1 1.4345 2009-03-11 07:30:23.000
1 2.0397 2009-03-11 07:41:55.000
1 1.3688 2009-03-11 07:47:55.000
1 1.3617 2009-03-11 07:50:28.000
1 2.0448 2009-03-11 07:53:30.000
1 2.0674 2009-03-11 07:56:04.000
1 1.222 2009-03-11 07:59:01.000
1 1.8937 2009-03-11 08:00:28.000
1 1.4801 2009-03-11 08:02:42.000
1 1.4771 2009-03-11 08:04:39.000
1 1.1413 2009-03-11 08:07:11.000
1 1.29 2009-03-11 08:08:57.000
1 1.5294 2009-03-11 08:12:00.000
1 2.0805 2009-03-11 08:19:39.000
1 1.4668 2009-03-11 08:21:29.000
1 1.6401 2009-03-11 08:24:40.000
1 0.6563 2009-03-11 08:44:15.000
1 1.9909 2009-03-11 08:56:36.000
1 1.4843 2009-03-11 08:57:36.000
1 0.9362 2009-03-11 09:00:18.000
1 1.7145 2009-03-11 09:17:22.000
1 0.9844 2009-03-11 09:18:25.000
1 1.155 2009-03-11 09:26:36.000
1 1.6415 2009-03-11 09:28:12.000
1 1.1307 2009-03-11 09:35:23.000
1 0.7049 2009-03-11 09:36:38.000
1 1.6044 2009-03-11 09:38:31.000
1 1.6136 2009-03-11 09:40:08.000
1 1.2456 2009-03-11 09:48:39.000
1 1.7986 2009-03-11 09:53:41.000
1 1.0963 2009-03-11 09:55:03.000
1 1.0475 2009-03-11 09:56:40.000
1 1.715 2009-03-11 09:58:36.000
1 2.2908 2009-03-11 10:01:10.000
1 1.9489 2009-03-11 10:02:32.000
1 1.0986 2009-03-11 10:03:54.000
1 0.7038 2009-03-11 10:11:59.000
1 1.2492 2009-03-11 10:13:23.000
1 0.8679 2009-03-11 10:15:06.000
1 1.1289 2009-03-11 10:17:08.000
1 1.088 2009-03-11 10:19:33.000
1 1.0837 2009-03-11 10:20:41.000
1 1.5693 2009-03-11 10:22:19.000
1 1.172 2009-03-11 10:23:55.000
1 1.2148 2009-03-11 10:26:24.000
1 0.3028 2009-03-11 10:28:08.000
1 0.584 2009-03-11 10:30:14.000
1 0.4876 2009-03-11 10:31:44.000
1 1.0068 2009-03-11 10:32:42.000
1 1.0361 2009-03-11 10:34:04.000
1 0.8613 2009-03-11 10:35:18.000
1 0.7056 2009-03-11 10:36:26.000
1 0.7232 2009-03-11 10:37:57.000
1 0.6216 2009-03-11 10:42:43.000
1 1.9264 2009-03-11 11:01:06.000
1 1.7607 2009-03-11 11:06:18.000
1 0.7605 2009-03-11 12:27:14.000
1 1.5295 2009-03-11 12:29:08.000
1 1.7971 2009-03-11 12:30:21.000
1 1.661 2009-03-11 12:33:45.000
1 1.8431 2009-03-11 12:35:11.000
1 1.7218 2009-03-11 12:37:52.000
1 1.5551 2009-03-11 12:42:23.000
1 1.1667 2009-03-11 12:50:13.000
1 0.8755 2009-03-11 12:56:04.000
1 1.7568 2009-03-11 13:01:45.000
1 2.1247 2009-03-11 13:08:32.000
1 0.8106 2009-03-11 13:09:39.000
1 2.4473 2009-03-11 13:35:41.000
1 1.7244 2009-03-11 14:11:18.000
1 1.0624 2009-03-11 14:17:38.000
1 2.828 2009-03-11 14:19:42.000
1 0.348 2009-03-11 14:21:33.000

现在如何按每小时输出?


select * from View_ProductivityPerHour where StartTime between '2009-Mar-11' and '2009-MAR-12'

order by (每小时)


最后出现的是
stem volume starttime
** ***** 6-7(或者6:00)
** ***** 7-8(或者7:00) <---如果时间段没有,可以不显示
** ***** 10-11(或者10:00)
...全文
90 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
SQL77 2009-04-22
  • 打赏
  • 举报
 回复
select sum(stem) as stem, sum(volume) as volume,DATEPART(HH,STARTTIME) [Starttime]
from DATETB
where StartTime between '2009-3-11' and '2009-3-12'
group by DATEPART(HH,STARTTIME)
order by DATEPART(HH,STARTTIME)
zhangle861010 2009-04-22
  • 打赏
  • 举报
 回复 


select * from View_ProductivityPerHour where StartTime between '2009-Mar-11' and '2009-MAR-12'

order by datepart(hh,StartTime)
htl258_Tony 2009-04-22
  • 打赏
  • 举报
 回复 
select sum(stem) as stem, sum(volume) as volume,left(convert(varchar,Starttime,8),3)+'00' [Starttime] 

from tb 

where StartTime between '2009-03-11' and '2009-03-12' 

group by left(convert(varchar,Starttime,8),3)+'00'

order by [Starttime]

/*

stem        volume                                  Starttime

----------- --------------------------------------- ---------

7           12.4479                                 06:00

12          19.8798                                 07:00

12          18.1305                                 08:00

14          18.3882                                 09:00

21          21.7449                                 10:00

2           3.6871                                  11:00

9           12.9103                                 12:00

4           7.1394                                  13:00

4           5.9628                                  14:00



(9 行受影响)

*/
htl258_Tony 2009-04-22
  • 打赏
  • 举报
 回复 
if object_id('tb') is not null drop table tb 

 go 

create table tb([stem] int,[volume] dec(18,4),[Starttime] datetime)

insert tb select 1,2.1603,'2009-03-11 06:36:30.000'

union all select 1,2.1517,'2009-03-11 06:39:01.000'

union all select 1,2.1589,'2009-03-11 06:40:26.000'

union all select 1,2.166,'2009-03-11 06:42:27.000'

union all select 1,0.8291,'2009-03-11 06:48:58.000'

union all select 1,1.3643,'2009-03-11 06:50:27.000'

union all select 1,1.6176,'2009-03-11 06:51:58.000'

union all select 1,1.6746,'2009-03-11 07:08:47.000'

union all select 1,1.6529,'2009-03-11 07:10:24.000'

union all select 1,1.7157,'2009-03-11 07:19:22.000'

union all select 1,1.2184,'2009-03-11 07:21:27.000'

union all select 1,2.0793,'2009-03-11 07:28:27.000'

union all select 1,1.4345,'2009-03-11 07:30:23.000'

union all select 1,2.0397,'2009-03-11 07:41:55.000'

union all select 1,1.3688,'2009-03-11 07:47:55.000'

union all select 1,1.3617,'2009-03-11 07:50:28.000'

union all select 1,2.0448,'2009-03-11 07:53:30.000'

union all select 1,2.0674,'2009-03-11 07:56:04.000'

union all select 1,1.222,'2009-03-11 07:59:01.000'

union all select 1,1.8937,'2009-03-11 08:00:28.000'

union all select 1,1.4801,'2009-03-11 08:02:42.000'

union all select 1,1.4771,'2009-03-11 08:04:39.000'

union all select 1,1.1413,'2009-03-11 08:07:11.000'

union all select 1,1.29,'2009-03-11 08:08:57.000'

union all select 1,1.5294,'2009-03-11 08:12:00.000'

union all select 1,2.0805,'2009-03-11 08:19:39.000'

union all select 1,1.4668,'2009-03-11 08:21:29.000'

union all select 1,1.6401,'2009-03-11 08:24:40.000'

union all select 1,0.6563,'2009-03-11 08:44:15.000'

union all select 1,1.9909,'2009-03-11 08:56:36.000'

union all select 1,1.4843,'2009-03-11 08:57:36.000'

union all select 1,0.9362,'2009-03-11 09:00:18.000'

union all select 1,1.7145,'2009-03-11 09:17:22.000'

union all select 1,0.9844,'2009-03-11 09:18:25.000'

union all select 1,1.155,'2009-03-11 09:26:36.000'

union all select 1,1.6415,'2009-03-11 09:28:12.000'

union all select 1,1.1307,'2009-03-11 09:35:23.000'

union all select 1,0.7049,'2009-03-11 09:36:38.000'

union all select 1,1.6044,'2009-03-11 09:38:31.000'

union all select 1,1.6136,'2009-03-11 09:40:08.000'

union all select 1,1.2456,'2009-03-11 09:48:39.000'

union all select 1,1.7986,'2009-03-11 09:53:41.000'

union all select 1,1.0963,'2009-03-11 09:55:03.000'

union all select 1,1.0475,'2009-03-11 09:56:40.000'

union all select 1,1.715,'2009-03-11 09:58:36.000'

union all select 1,2.2908,'2009-03-11 10:01:10.000'

union all select 1,1.9489,'2009-03-11 10:02:32.000'

union all select 1,1.0986,'2009-03-11 10:03:54.000'

union all select 1,0.7038,'2009-03-11 10:11:59.000'

union all select 1,1.2492,'2009-03-11 10:13:23.000'

union all select 1,0.8679,'2009-03-11 10:15:06.000'

union all select 1,1.1289,'2009-03-11 10:17:08.000'

union all select 1,1.088,'2009-03-11 10:19:33.000'

union all select 1,1.0837,'2009-03-11 10:20:41.000'

union all select 1,1.5693,'2009-03-11 10:22:19.000'

union all select 1,1.172,'2009-03-11 10:23:55.000'

union all select 1,1.2148,'2009-03-11 10:26:24.000'

union all select 1,0.3028,'2009-03-11 10:28:08.000'

union all select 1,0.584,'2009-03-11 10:30:14.000'

union all select 1,0.4876,'2009-03-11 10:31:44.000'

union all select 1,1.0068,'2009-03-11 10:32:42.000'

union all select 1,1.0361,'2009-03-11 10:34:04.000'

union all select 1,0.8613,'2009-03-11 10:35:18.000'

union all select 1,0.7056,'2009-03-11 10:36:26.000'

union all select 1,0.7232,'2009-03-11 10:37:57.000'

union all select 1,0.6216,'2009-03-11 10:42:43.000'

union all select 1,1.9264,'2009-03-11 11:01:06.000'

union all select 1,1.7607,'2009-03-11 11:06:18.000'

union all select 1,0.7605,'2009-03-11 12:27:14.000'

union all select 1,1.5295,'2009-03-11 12:29:08.000'

union all select 1,1.7971,'2009-03-11 12:30:21.000'

union all select 1,1.661,'2009-03-11 12:33:45.000'

union all select 1,1.8431,'2009-03-11 12:35:11.000'

union all select 1,1.7218,'2009-03-11 12:37:52.000'

union all select 1,1.5551,'2009-03-11 12:42:23.000'

union all select 1,1.1667,'2009-03-11 12:50:13.000'

union all select 1,0.8755,'2009-03-11 12:56:04.000'

union all select 1,1.7568,'2009-03-11 13:01:45.000'

union all select 1,2.1247,'2009-03-11 13:08:32.000'

union all select 1,0.8106,'2009-03-11 13:09:39.000'

union all select 1,2.4473,'2009-03-11 13:35:41.000'

union all select 1,1.7244,'2009-03-11 14:11:18.000'

union all select 1,1.0624,'2009-03-11 14:17:38.000'

union all select 1,2.828,'2009-03-11 14:19:42.000'

union all select 1,0.348,'2009-03-11 14:21:33.000'

go

select sum(stem) as stem, sum(volume) as volume,left(convert(varchar,Starttime,8),3)+'00' [Starttime] 

from tb 

where StartTime between '2009-03-11' and '2009-03-12' 

group by left(convert(varchar,Starttime,8),3)+'00'

order by stem

/*

stem        volume                                  Starttime

----------- --------------------------------------- ---------

2           3.6871                                  11:00

4           7.1394                                  13:00

4           5.9628                                  14:00

7           12.4479                                 06:00

9           12.9103                                 12:00

12          19.8798                                 07:00

12          18.1305                                 08:00

14          18.3882                                 09:00

21          21.7449                                 10:00



(9 行受影响)

*/
usher_gml 2009-04-22
  • 打赏
  • 举报
 回复
select a.*,datepart(hh,StartTime) from View_ProductivityPerHour a
where StartTime between '2009-Mar-11' and '2009-MAR-12'
order by datepart(hh,StartTime)
usher_gml 2009-04-22
  • 打赏
  • 举报
 回复
select * from View_ProductivityPerHour where StartTime between '2009-Mar-11' and '2009-MAR-12'
order by datepart(hh,StartTime)

pennymay 2009-04-22
  • 打赏
  • 举报
 回复 


select sum(stem) as stem, sum(volume) as volume from View_ProductivityPerHour where StartTime between '2009-Mar-11' and '2009-MAR-12' group by (每小时)


每小时应该怎么写?
pennymay 2009-04-22
  • 打赏
  • 举报
 回复
楼上的不行阿~!~
lg3605119 2009-04-22
  • 打赏
  • 举报
 回复 


select stem,volume,starttime = substring(starttime,1,charindex('-',starttime)-1)

 from

(

	select * from View_ProductivityPerHour where StartTime between '2009-Mar-11' and '2009-MAR-12'

	order by (每小时)

)t
数据库SQL基本语句（半天即可学会，轻松简单）
只要你花半天时间，就能轻松掌握SQL基本语句的使用方式，很好的参考资料，主要看文章中的例子，因为例子具有可读连续性，因此，文档描述非常简单，容易掌握，也较通俗易懂！与大家分享！与大家共享！
SQL语句五种分类
SQL的英文全称为Structured Query Language，SQL用来和数据库打交道，完成和数据库的通信，SQL是一套标准，但是每一个数据库都有自己的特性，当使用这个数据库特性相关的功能，这时SQL语句可能就不是标准了，但是...
如何用sql语句创建一个表（简单基础）
使用CREATE TABLE 语句 CREATE TABLE 语句用于创建数据库中...创建一个actor表，包含如下列信息 列表 类型 是否为NULL 含义 actor_id smallint(5) not null 主键id first_name varchar(45)
SQL常用语句大全
SQL常用语句大全
ORACLE动态SQL语句
问题的提出我们经常需要运行可变化的SQL语句，这种通常称为动态SQL，在ORACLE中...因为它不是标准的，其中含有变化的成分，因此ORACLE提供了一个执行动态SQL语句的模式： EXECUTE IMMEDIATE 这句话的含义是立即执行
MS-SQL Server

34,593

社区成员

254,590

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章