SQL分类汇总问题

jinhetian 2010-01-22 10:52:14

有直接汇总成这样结果的语句吗，不要写存储过程或者游标的
Date_Time Type
1 2009-01-22 09:21 A1
2 2009-01-22 09:22 A1
3 2009-01-22 09:23 A1
4 2009-01-22 09:24 A1
5 2009-01-22 09:25 B1
6 2009-01-22 09:26 B1
7 2009-01-22 09:27 B1
8 2009-01-22 09:28 B1
9 2009-01-22 09:29 B1
10 2009-01-22 09:30 A1
11 2009-01-22 09:31 A1
12 2009-01-22 09:32 A1
13 2009-01-22 09:33 A1
14 2009-01-22 09:34 B1
15 2009-01-22 09:35 B1
16 2009-01-22 09:36 A1

汇总成这样，每类数据的开始和结束时间
Start_Time End_Time
A1 2009-01-22 09:21 2009-01-22 09:24
B1 2009-01-22 09:25 2009-01-22 09:29
A1 2009-01-22 09:30 2009-01-22 09:33
B1 2009-01-22 09:34 2009-01-22 09:35
A1 2009-01-22 09:36

...全文

621 14 打赏收藏转发到动态举报

写回复

用AI写文章

14 条回复

切换为时间正序

请发表友善的回复…

发表回复

jinhetian 2010-01-22

打赏
举报

SOS，在出现同类连续两条时间一样的数据的时候，运算就出问题

jinhetian 2010-01-22

打赏
举报

强人辈出，个人认为nianran520 10楼的是正解。sgtzzc结果也是对的。感谢各位

Mr_Nice 2010-01-22

打赏
举报

[Quote=引用 7 楼 sgtzzc 的回复:]
SQL code---测试数据---ifobject_id('[tb]')isnotnulldroptable[tb]gocreatetable[tb]([id]int,[Date_Time]datetime,[Type]varchar(2))insert[tb]select1,'2009-01-22 09:21','A1'unionallselect2,'2009-01-22 09:22','A?-
[/Quote]

学习...

nianran520 2010-01-22

打赏
举报

--> 测试数据：[tb]

if object_id('[tb]') is not null drop table [tb]

create table [tb]([id] int,[Date_Time] datetime,[Type] varchar(2))

insert [tb]

select 1,'2009-01-22 09:21','A1' union all

select 2,'2009-01-22 09:22','A1' union all

select 3,'2009-01-22 09:23','A1' union all

select 4,'2009-01-22 09:24','A1' union all

select 5,'2009-01-22 09:25','B1' union all

select 6,'2009-01-22 09:26','B1' union all

select 7,'2009-01-22 09:27','B1' union all

select 8,'2009-01-22 09:28','B1' union all

select 9,'2009-01-22 09:29','B1' union all

select 10,'2009-01-22 09:30','A1' union all

select 11,'2009-01-22 09:31','A1' union all

select 12,'2009-01-22 09:32','A1' union all

select 13,'2009-01-22 09:33','A1' union all

select 14,'2009-01-22 09:34','B1' union all

select 15,'2009-01-22 09:35','B1' union all

select 16,'2009-01-22 09:36','A1'



select [Type],

min(Date_Time) as Start_Time, 

case count(1) when 1 then null else max(Date_Time) end as End_Time,

count(1)-1 as 连续次数 

from 

(select (select count(1) from tb where 

Type=r.Type and Date_Time<=r.Date_Time)-id as group_id,* 

from [tb] r) t 

group by group_id,[Type]



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

A1	2009-01-22 09:36:00.000	NULL	0

A1	2009-01-22 09:30:00.000	2009-01-22 09:33:00.000	3

A1	2009-01-22 09:21:00.000	2009-01-22 09:24:00.000	3

B1	2009-01-22 09:34:00.000	2009-01-22 09:35:00.000	1

B1	2009-01-22 09:25:00.000	2009-01-22 09:29:00.000	4

nianran520 2010-01-22

打赏
举报

--> 测试数据：[tb]

if object_id('[tb]') is not null drop table [tb]

create table [tb]([id] int,[Date_Time] datetime,[Type] varchar(2))

insert [tb]

select 1,'2009-01-22 09:21','A1' union all

select 2,'2009-01-22 09:22','A1' union all

select 3,'2009-01-22 09:23','A1' union all

select 4,'2009-01-22 09:24','A1' union all

select 5,'2009-01-22 09:25','B1' union all

select 6,'2009-01-22 09:26','B1' union all

select 7,'2009-01-22 09:27','B1' union all

select 8,'2009-01-22 09:28','B1' union all

select 9,'2009-01-22 09:29','B1' union all

select 10,'2009-01-22 09:30','A1' union all

select 11,'2009-01-22 09:31','A1' union all

select 12,'2009-01-22 09:32','A1' union all

select 13,'2009-01-22 09:33','A1' union all

select 14,'2009-01-22 09:34','B1' union all

select 15,'2009-01-22 09:35','B1' union all

select 16,'2009-01-22 09:36','A1'



select [Type],

min(Date_Time) as Start_Time, 

max(Date_Time) as End_Time,

count(1) as 连续次数 

from 

(select (select count(1) from tb where 

Type=r.Type and Date_Time<=r.Date_Time)-id as group_id,* 

from [tb] r) t 

group by group_id,[Type]



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

A1	2009-01-22 09:36:00.000	2009-01-22 09:36:00.000	1

A1	2009-01-22 09:30:00.000	2009-01-22 09:33:00.000	4

A1	2009-01-22 09:21:00.000	2009-01-22 09:24:00.000	4

B1	2009-01-22 09:34:00.000	2009-01-22 09:35:00.000	2

B1	2009-01-22 09:25:00.000	2009-01-22 09:29:00.000	5

--小F-- 2010-01-22

打赏
举报

sgtzzc 2010-01-22

打赏
举报

[Quote=引用 6 楼 jinhetian 的回复:]
忘记说了，如果在汇总的数据后面再加上连续的数据个数就更好了
[/Quote]



---查询---

select px=identity(int,1,1),*  into #1

from tb t 

where not exists(select 1 from tb where type=t.type and datediff(mi,date_time,t.date_time)=1) 

go

select px=identity(int,1,1),* into #2

from tb t 

where not exists(select 1 from tb where type=t.type and datediff(mi,date_time,t.date_time)=-1) 

go



select a.type,

Start_Time=a.Date_Time,

End_Time=case when b.Date_Time=a.Date_Time then null else b.Date_Time end,

datediff(mi,a.Date_Time,b.Date_Time) as 连续个数

from #1 a

left join #2 b

on a.type=b.type and a.px=b.px



/**

type Start_Time                                             End_Time                                               连续个数        

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

A1   2009-01-22 09:21:00.000                                2009-01-22 09:24:00.000                                3

B1   2009-01-22 09:25:00.000                                2009-01-22 09:29:00.000                                4

A1   2009-01-22 09:30:00.000                                2009-01-22 09:33:00.000                                3

B1   2009-01-22 09:34:00.000                                2009-01-22 09:35:00.000                                1

A1   2009-01-22 09:36:00.000                                NULL                                                   0



（所影响的行数为 5 行）

**/

sgtzzc 2010-01-22

打赏
举报

---测试数据---

if object_id('[tb]') is not null drop table [tb]

go

create table [tb]([id] int,[Date_Time] datetime,[Type] varchar(2))

insert [tb]

select 1,'2009-01-22 09:21','A1' union all

select 2,'2009-01-22 09:22','A1' union all

select 3,'2009-01-22 09:23','A1' union all

select 4,'2009-01-22 09:24','A1' union all

select 5,'2009-01-22 09:25','B1' union all

select 6,'2009-01-22 09:26','B1' union all

select 7,'2009-01-22 09:27','B1' union all

select 8,'2009-01-22 09:28','B1' union all

select 9,'2009-01-22 09:29','B1' union all

select 10,'2009-01-22 09:30','A1' union all

select 11,'2009-01-22 09:31','A1' union all

select 12,'2009-01-22 09:32','A1' union all

select 13,'2009-01-22 09:33','A1' union all

select 14,'2009-01-22 09:34','B1' union all

select 15,'2009-01-22 09:35','B1' union all

select 16,'2009-01-22 09:36','A1'

 

---查询---

select px=identity(int,1,1),*  into #1

from tb t 

where not exists(select 1 from tb where type=t.type and datediff(mi,date_time,t.date_time)=1) 

go

select px=identity(int,1,1),* into #2

from tb t 

where not exists(select 1 from tb where type=t.type and datediff(mi,date_time,t.date_time)=-1) 

go



select a.type,

Start_Time=a.Date_Time,

End_Time=case when b.Date_Time=a.Date_Time then null else b.Date_Time end

from #1 a

left join #2 b

on a.type=b.type and a.px=b.px



---结果---

type Start_Time                                             End_Time                                               

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

A1   2009-01-22 09:21:00.000                                2009-01-22 09:24:00.000

B1   2009-01-22 09:25:00.000                                2009-01-22 09:29:00.000

A1   2009-01-22 09:30:00.000                                2009-01-22 09:33:00.000

B1   2009-01-22 09:34:00.000                                2009-01-22 09:35:00.000

A1   2009-01-22 09:36:00.000                                NULL



（所影响的行数为 5 行）

jinhetian 2010-01-22

打赏
举报

忘记说了，如果在汇总的数据后面再加上连续的数据个数就更好了

--小F-- 2010-01-22

打赏
举报

--先这样 剩下的就简单了吧 呵呵

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

-- Author  :fredrickhu(我是小F，向高手学习)

-- Date    :2010-01-22 10:59:42

-- 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,[Date_Time] datetime,[Type] varchar(2))

insert [tb]

select 1,'2009-01-22 09:21','A1' union all

select 2,'2009-01-22 09:22','A1' union all

select 3,'2009-01-22 09:23','A1' union all

select 4,'2009-01-22 09:24','A1' union all

select 5,'2009-01-22 09:25','B1' union all

select 6,'2009-01-22 09:26','B1' union all

select 7,'2009-01-22 09:27','B1' union all

select 8,'2009-01-22 09:28','B1' union all

select 9,'2009-01-22 09:29','B1' union all

select 10,'2009-01-22 09:30','A1' union all

select 11,'2009-01-22 09:31','A1' union all

select 12,'2009-01-22 09:32','A1' union all

select 13,'2009-01-22 09:33','A1' union all

select 14,'2009-01-22 09:34','B1' union all

select 15,'2009-01-22 09:35','B1' union all

select 16,'2009-01-22 09:36','A1'

--------------开始查询--------------------------

select id0=identity(int,1,1),* into # from tb

select 

  id,date_time,type,px=(select 

          count(1) 

        from

          #

        where

         [Type]=a.[Type] and id0<=a.id0 

        and

         id>=(select isnull(max(id0),0) from # where id0<a.id0 and [Type]!=a.[Type])) 

from

  # a





drop table #

----------------结果----------------------------

/* id          date_time               type px

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

1           2009-01-22 09:21:00.000 A1   1

2           2009-01-22 09:22:00.000 A1   2

3           2009-01-22 09:23:00.000 A1   3

4           2009-01-22 09:24:00.000 A1   4

5           2009-01-22 09:25:00.000 B1   1

6           2009-01-22 09:26:00.000 B1   2

7           2009-01-22 09:27:00.000 B1   3

8           2009-01-22 09:28:00.000 B1   4

9           2009-01-22 09:29:00.000 B1   5

10          2009-01-22 09:30:00.000 A1   1

11          2009-01-22 09:31:00.000 A1   2

12          2009-01-22 09:32:00.000 A1   3

13          2009-01-22 09:33:00.000 A1   4

14          2009-01-22 09:34:00.000 B1   1

15          2009-01-22 09:35:00.000 B1   2

16          2009-01-22 09:36:00.000 A1   1



(16 行受影响)



*/

Mr_Nice 2010-01-22

打赏
举报

整理一下：相同type,连续ID的数据整合

有意思！！！

--小F-- 2010-01-22

打赏
举报

这个题目有点意思

sgtzzc 2010-01-22

打赏
举报

哦，不对，要显示多条记录

sgtzzc 2010-01-22

打赏
举报

select type,

min(Date_Time) as Start_Time,

max(Date_Time) as End_Time 

from tb

group by type

例如：对商品销售数据进行分类汇总： SELECT 商品名 , COUNT(销售量） FROM 表名 GROUP BY 商品名；注意：SELECT 和 GROUP BY 后面的列名是相同的。这个语句的执行顺序为：FROM->WHERE->GROUP BY->...

SQL代码如下： -- 判断临时表#SaleInfo是否已经存在，若存在，则删除 if OBJECT_ID('tempdb..#SaleInfo') is not null drop table #SaleInfo -- 为了保护原始数据，我们将销售表sales中的数据查询出来放入临时...

SQL SERVER的分类汇总SQL SERVER中使用GROUP BY对数据进行分类汇总，我们也可以使用WITH ROLLUP和WITH CUBE配合GROUP BY进行“增强”了的分类汇总，那么他们两个是如何增强GROUP BY的汇总能力的呢？一.功能增强1....

#sql 查询和汇总 #1、GROUP BY 分组 #2、count 统计数量 #ORDER BY 顺序不用写倒序要写 ORDER BY ( 这里的要倒序的内容的列名) desc #查询各个部门统计结果 select dept_name, COUNT(dept_name) from par_...

group by having 实现对数据的分类汇总，因此 group by 主要和统计类型计算函数一起使用，例如：count（），sum（）等，而 having 是对我们统计的结果集再进行筛选，得到我们最后需要得到的数据集。不需要对统计的...

疑难问题

22,207

社区成员

121,730

社区内容

发帖

与我相关

我的任务

社区管理员

加入社区

近7日
近30日
至今

加载中

查看更多榜单

社区公告

暂无公告

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

+ 用AI写文章