如何将查询结果进行分组？

dianhua_12 2012-04-04 06:43:25

create table test
(
id int,---编号
sl int,---数量
rq datetime ---日期
)

insert into test select 10, 100,'2002-1-1 6:00:00'
insert into test select 10, 101,'2002-1-1 6:10:00'
insert into test select 10, 100,'2002-1-1 6:20:00'
insert into test select 10, 100,'2002-1-1 6:30:00'
insert into test select 10, 100,'2002-1-1 6:40:00'
insert into test select 10, 100,'2002-1-1 6:50:00'
insert into test select 10, 100,'2002-1-1 7:00:00'

insert into test select 10, 100,'2002-1-1 23:10:00'
insert into test select 10, 100,'2002-1-1 23:20:00'
insert into test select 10, 100,'2002-1-1 23:30:00'
insert into test select 10, 100,'2002-1-1 23:40:00'
insert into test select 10, 100,'2002-1-1 23:50:00'
insert into test select 10, 100,'2002-1-2 00:00:00'

insert into test select 10, 100,'2002-1-2 00:10:00'
insert into test select 10, 100,'2002-1-2 00:20:00'
insert into test select 10, 100,'2002-1-2 00:30:00'
insert into test select 10, 100,'2002-1-2 00:40:00'
insert into test select 10, 100,'2002-1-2 00:50:00'
insert into test select 10, 100,'2002-1-2 01:00:00'


insert into test select 10, 100,'2002-1-2 05:10:00'
insert into test select 10, 100,'2002-1-2 05:20:00'
insert into test select 10, 100,'2002-1-2 05:30:00'
insert into test select 10, 100,'2002-1-2 05:40:00'
insert into test select 10, 100,'2002-1-2 05:50:00'
insert into test select 10, 100,'2002-1-2 06:00:00'

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

insert into test select 10, 102,'2002-1-2 6:10:00'
insert into test select 10, 100,'2002-1-2 6:20:00'
insert into test select 10, 100,'2002-1-2 6:30:00'
insert into test select 10, 100,'2002-1-2 6:40:00'
insert into test select 10, 100,'2002-1-2 6:50:00'
insert into test select 10, 100,'2002-1-2 7:00:00'

insert into test select 10, 100,'2002-1-2 23:10:00'
insert into test select 10, 100,'2002-1-2 23:20:00'
insert into test select 10, 100,'2002-1-2 23:30:00'
insert into test select 10, 100,'2002-1-2 23:40:00'
insert into test select 10, 100,'2002-1-2 23:50:00'
insert into test select 10, 100,'2002-1-3 00:00:00'

insert into test select 10, 100,'2002-1-3 00:10:00'
insert into test select 10, 100,'2002-1-3 00:20:00'
insert into test select 10, 100,'2002-1-3 00:30:00'
insert into test select 10, 100,'2002-1-3 00:40:00'
insert into test select 10, 100,'2002-1-3 00:50:00'
insert into test select 10, 100,'2002-1-3 01:00:00'


insert into test select 10, 100,'2002-1-2 05:10:00'
insert into test select 10, 100,'2002-1-2 05:20:00'
insert into test select 10, 100,'2002-1-2 05:30:00'
insert into test select 10, 100,'2002-1-2 05:40:00'
insert into test select 10, 100,'2002-1-2 05:50:00'
insert into test select 10, 100,'2002-1-2 06:00:00'


select * from test where rq between (select cast(floor(cast(cast('2002-01-01' as datetime) as float))+0.255 as datetime))

and (select cast(floor(cast(cast('2002-01-01' as datetime) as float))+1.25 as datetime))

/*
我想得到这样的结果,我用分割线分开了，分割线以上是 2002-01-01的数据总和，分割线以下是2002-01-02的数据总和，其实就是统计每天从6:10到第二天6:00的sl总和。查询已经完成，就是不知道如何分组。

日期 sl

2002-01-01 2401

2002-01-02 2402

*/

drop table test


...全文
116 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
百年树人 2012-04-04
  • 打赏
  • 举报
 回复 
select convert(varchar(10),dateadd(mi,-370,rq),120) as rq,sum(sl) as sl from test

where convert(varchar(5),rq,108)  not between '06:01' and '06:09'

group by convert(varchar(10),dateadd(mi,-370,rq),120)



/**

rq         sl

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

2001-12-31 100

2002-01-01 2401

2002-01-02 2402



(3 行受影响)

**/
百年树人 2012-04-04
  • 打赏
  • 举报
 回复
最后一段数据你确认是2号的还是3号？
开着拖拉机泡妞 2012-04-04
  • 打赏
  • 举报
 回复
[Quote=引用 4 楼 的回复:]

查询语句没有错误，可能您理解有误，就是在查询出来的结果集中进行分组统计，把当日6:10到第二天6:00
的数据统计处理，算出一个总和，并把这个总和归到当天的日期里。

例如：要统计2002-01-01的数字总和，就把 2002-01-01 6:10 到 2002-01-02 6:00的所有数据求和，把这个求和结果归到2001-01-01这天。

同理2002-01-02也是如此

……
[/Quote]
汗，你自己看你给的测试数据，你告诉我怎么算出来的，自己拿笔加一下
dianhua_12 2012-04-04
  • 打赏
  • 举报
 回复
啊，不好意思，我看错了，小F的结果是对的
dianhua_12 2012-04-04
  • 打赏
  • 举报
 回复
查询语句没有错误，可能您理解有误，就是在查询出来的结果集中进行分组统计，把当日6:10到第二天6:00
的数据统计处理，算出一个总和，并把这个总和归到当天的日期里。

例如：要统计2002-01-01的数字总和，就把 2002-01-01 6:10 到 2002-01-02 6:00的所有数据求和，把这个求和结果归到2001-01-01这天。

同理2002-01-02也是如此

结果就是这样：

日期 数字总和
2002-01-01 2401

2002-01-02 2402




小F的结果有误：

2002-01-01 2901
2002-01-02 2002
百年树人 2012-04-04
  • 打赏
  • 举报
 回复
如果你确认你的查询语句没错，可以用以下语句，不过你的描述和你的结果好像不一致
select convert(varchar(10),rq,120) as 日期,sum(sl) as 数量

from(

select * from test where rq between (select cast(floor(cast(cast('2002-01-01' as datetime) as float))+0.255 as datetime))



and (select cast(floor(cast(cast('2002-01-01' as datetime) as float))+1.25 as datetime))

) t

group by convert(varchar(10),rq,120)
开着拖拉机泡妞 2012-04-04
  • 打赏
  • 举报
 回复 


create table test

(

id int,---编号

sl int,---数量

rq datetime ---日期

)



insert into test select 10, 100,'2002-1-1 6:00:00'

insert into test select 10, 101,'2002-1-1 6:10:00'

insert into test select 10, 100,'2002-1-1 6:20:00'

insert into test select 10, 100,'2002-1-1 6:30:00'

insert into test select 10, 100,'2002-1-1 6:40:00'

insert into test select 10, 100,'2002-1-1 6:50:00'

insert into test select 10, 100,'2002-1-1 7:00:00'



insert into test select 10, 100,'2002-1-1 23:10:00'

insert into test select 10, 100,'2002-1-1 23:20:00'

insert into test select 10, 100,'2002-1-1 23:30:00'

insert into test select 10, 100,'2002-1-1 23:40:00'

insert into test select 10, 100,'2002-1-1 23:50:00'

insert into test select 10, 100,'2002-1-2 00:00:00'



insert into test select 10, 100,'2002-1-2 00:10:00'

insert into test select 10, 100,'2002-1-2 00:20:00'

insert into test select 10, 100,'2002-1-2 00:30:00'

insert into test select 10, 100,'2002-1-2 00:40:00'

insert into test select 10, 100,'2002-1-2 00:50:00'

insert into test select 10, 100,'2002-1-2 01:00:00'





insert into test select 10, 100,'2002-1-2 05:10:00'

insert into test select 10, 100,'2002-1-2 05:20:00'

insert into test select 10, 100,'2002-1-2 05:30:00'

insert into test select 10, 100,'2002-1-2 05:40:00'

insert into test select 10, 100,'2002-1-2 05:50:00'

insert into test select 10, 100,'2002-1-2 06:00:00'





insert into test select 10, 102,'2002-1-2 6:10:00'

insert into test select 10, 100,'2002-1-2 6:20:00'

insert into test select 10, 100,'2002-1-2 6:30:00'

insert into test select 10, 100,'2002-1-2 6:40:00'

insert into test select 10, 100,'2002-1-2 6:50:00'

insert into test select 10, 100,'2002-1-2 7:00:00'



insert into test select 10, 100,'2002-1-2 23:10:00'

insert into test select 10, 100,'2002-1-2 23:20:00'

insert into test select 10, 100,'2002-1-2 23:30:00'

insert into test select 10, 100,'2002-1-2 23:40:00'

insert into test select 10, 100,'2002-1-2 23:50:00'

insert into test select 10, 100,'2002-1-3 00:00:00'



insert into test select 10, 100,'2002-1-3 00:10:00'

insert into test select 10, 100,'2002-1-3 00:20:00'

insert into test select 10, 100,'2002-1-3 00:30:00'

insert into test select 10, 100,'2002-1-3 00:40:00'

insert into test select 10, 100,'2002-1-3 00:50:00'

insert into test select 10, 100,'2002-1-3 01:00:00'





insert into test select 10, 100,'2002-1-3 05:10:00'

insert into test select 10, 100,'2002-1-3 05:20:00'

insert into test select 10, 100,'2002-1-3 05:30:00'

insert into test select 10, 100,'2002-1-3 05:40:00'

insert into test select 10, 100,'2002-1-3 05:50:00'

insert into test select 10, 100,'2002-1-3 06:00:00'



select CONVERT(varchar(10),rq,120) as rq,SUM(sl) as sumsl from(

select case when 0<= DATEPART(HH,rq) and DATEPART(HH,rq)<6

then DATEADD(HH,-7,rq) else rq end as rq,sl from test)a

where CONVERT(varchar(10),rq,120) between '2002-01-01' and  '2002-01-02'

group by CONVERT(varchar(10),rq,120)



/*

rq	sumsl

2002-01-01	2401

2002-01-02	2402

*/
Mysql数据库进阶/引擎/分组/连接/子查询/事务/Django框架的model
本课程是理论和实战相结合的课程。 主要讲解以下知识点：...by分组/having及多表查询 通过案例来讲解join连接 讲解子查询的实际应用 通过多个案例来讲解如何使用join和子查询 讲解django框架的models中如何应用多表查询
mysql查询结果分组后二次查询_如何对查询的数据进行二次分组？？
首先根据我业务需求，我需要对数据进行按时间分组，这一步可以实现，附上代码：(我使用的是php原生的数据库查询语句)$data = $request->getQueryParams();$from = $data['from'];$to = $data['to'];$res = Plan::...
什么是数据库分组查询（详解）
分组查询为什么要分组？逐级分组对SELECT子句的要求对分组结果集再次做汇总计算HAVING子句的用途HAVING子句的特殊用法 为什么要分组？ 默认情况下汇总函数是对全表范围内的数据做统计 GROUP BY子句的作用是通过一定...
mysql 二次分组_如何对查询的数据进行二次分组？？
首先根据我业务需求，我需要对数据进行按时间分组，这一步可以实现，附上代码：(我使用的是php原生的数据库查询语句)$data = $request->getQueryParams();$from = $data['from'];$to = $data['to'];$res = Plan::...
mybatis中查询结果进行分组
在用mybatis进行数据库查询时，对查询结果进行自动分组，在mapper.xml中的配置有些注意的地方，下面是实际项目中一个例子。在数据库中查询中如下： 在结果集中需要对alarmDate进行分组，比如2017-04-05这两条数据...
MS-SQL Server

34,593

社区成员

254,589

社区内容

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

加载中

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

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

+ 用AI写文章