求sql：按相同日期分组并显示组号

2013-10-29 03:30:24 509 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

SQL自学：分组数据GROUP BY子句和HAVING子句 GROUP BY 子句和 HAVING 子句是 SQL 中非常强大的工具，它们可以帮助我们对数据进行高效的分组和筛选。GROUP BY 子句必须放在 WHERE 子句之后，ORDER BY 子句之前。在 GROUP BY 子句中指定的列必须是在 SELECT 子句中出现的列，或者是聚合函数的参数。HAVING 子句只能用于筛选聚合结果，不能用于筛选单个行。在使用 HAVING 子句时，可以使用聚合函数和逻辑运算符来构建复杂的筛选条件。阅读详情

如题：



create table #t(s_time datetime,na varchar(20))



insert #t

select '2013-10-1','aa'

union all

select '2013-10-1','bb'

union all

select '2013-10-1','cc'

union all

select '2013-10-15','dd'

union all

select '2013-10-15','ee'

union all

select '2013-10-20','ff'



select * from #t



/*

	希望得到的结果



	group_no	s_time	        na

	1		2013-10-01 	aa

	1		2013-10-01 	bb

	1		2013-10-01 	cc

	2		2013-10-15 	dd

	2		2013-10-15 	ee

	3		2013-10-20 	ff



*/

...全文

一文读懂SQL中的连续日期、连续登陆问题一文学会sql中的连续问题阅读详情

红火火火

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

红火火火 2013-10-29

打赏
举报

谢谢各位，问题解决了，同时也学习到了知识，结贴啦：D

LongRui888 2013-10-29

打赏
举报

通过dense_rank函数，进行密集的排名，就可以实现的：



if object_id('tb') is not null
   drop table tb
go
create table tb(s_time datetime,na varchar(20))
 
insert tb
select '2013-10-1','aa'
union all
select '2013-10-1','bb'
union all
select '2013-10-1','cc'
union all
select '2013-10-15','dd'
union all
select '2013-10-15','ee'
union all
select '2013-10-20','ff'
 
 
select DENSE_RANK() over(order by s_time) as group_no,
       convert(varchar(10),s_time,120) as s_time,
       na 
from tb
/*
group_no             s_time     na
-------------------- ---------- --------------------
1                    2013-10-01 aa
1                    2013-10-01 bb
1                    2013-10-01 cc
2                    2013-10-15 dd
2                    2013-10-15 ee
3                    2013-10-20 ff
*/

Andy__Huang 2013-10-29

打赏
举报

create table #t(s_time datetime,na varchar(20))
insert #t
select '2013-10-1','aa'
union all
select '2013-10-1','bb'
union all
select '2013-10-1','cc'
union all
select '2013-10-15','dd'
union all
select '2013-10-15','ee'
union all
select '2013-10-20','ff'

select group_no=(select count(distinct s_time) from #t b where b.s_time<=a.s_time),s_time,na
from #t a


/*
group_no	s_time	na
1	2013-10-01 00:00:00.000	aa
1	2013-10-01 00:00:00.000	bb
1	2013-10-01 00:00:00.000	cc
2	2013-10-15 00:00:00.000	dd
2	2013-10-15 00:00:00.000	ee
3	2013-10-20 00:00:00.000	ff

*/

發糞塗牆 2013-10-29

打赏
举报

 SELECT b.group_no ,
        a.*
 FROM   #t a
        INNER JOIN ( SELECT s_time ,
                            ROW_NUMBER() OVER ( ORDER BY s_time ) group_no
                     FROM   ( SELECT DISTINCT
                                        s_time
                              FROM      #t
                            ) b
                   ) b ON a.s_time = b.s_time

chen357313771 2013-10-29

打赏
举报


create table #t(s_time datetime,na varchar(20))

insert #t
select '2013-10-1','aa'
union all
select '2013-10-1','bb'
union all
select '2013-10-1','cc'
union all
select '2013-10-15','dd'
union all
select '2013-10-15','ee'
union all
select '2013-10-20','ff'

SELECT DENSE_RANK() OVER(ORDER BY s_time) AS group_no
	,CAST(s_time AS DATE) AS Date
	,na
FROM #t

/*
1	2013-10-01	aa
1	2013-10-01	bb
1	2013-10-01	cc
2	2013-10-15	dd
2	2013-10-15	ee
3	2013-10-20	ff

*/

-Tracy-McGrady- 2013-10-29

打赏
举报


create table #t(s_time datetime,na varchar(20))
 
insert #t
select '2013-10-1','aa'
union all
select '2013-10-1','bb'
union all
select '2013-10-1','cc'
union all
select '2013-10-15','dd'
union all
select '2013-10-15','ee'
union all
select '2013-10-20','ff'
 
select DENSE_RANK()over(order by s_time) as group_no
,convert(char(10),s_time,120) as s_time,na 
from #t

drop table #t

--结果
group_no             s_time     na
-------------------- ---------- --------------------
1                    2013-10-01 aa
1                    2013-10-01 bb
1                    2013-10-01 cc
2                    2013-10-15 dd
2                    2013-10-15 ee
3                    2013-10-20 ff

(6 行受影响)

-Tracy-McGrady- 2013-10-29

打赏
举报


create table #t(s_time datetime,na varchar(20))
 
insert #t
select '2013-10-1','aa'
union all
select '2013-10-1','bb'
union all
select '2013-10-1','cc'
union all
select '2013-10-15','dd'
union all
select '2013-10-15','ee'
union all
select '2013-10-20','ff'
 
select DENSE_RANK()over(order by s_time) as group_no,* from #t

drop table #t

--
group_no             s_time                  na
-------------------- ----------------------- --------------------
1                    2013-10-01 00:00:00.000 aa
1                    2013-10-01 00:00:00.000 bb
1                    2013-10-01 00:00:00.000 cc
2                    2013-10-15 00:00:00.000 dd
2                    2013-10-15 00:00:00.000 ee
3                    2013-10-20 00:00:00.000 ff

(6 行受影响)

求sql：按相同日期分组并显示组号

相关推荐