sql根据日期分组查询

sykkys 2012-09-14 10:33:43

我用sql2005，表（table1）如下

id zhuti riqi
1 大家好 2012-1-3
2 大 2012-1-5
3 家 2012-1-5
4 好 2012-1-10
5 欢迎 2012-1-10
6 光临 2012-1-10

我想要实现的功能是，一段sql语句实现下列输出：

2012-1-10
光临
欢迎
好
2012-1-5
家
大
2012-1-3
大家好

如何实现？请教大家了，谢谢！

...全文

146 5 打赏收藏转发到动态举报

写回复

用AI写文章

5 条回复

切换为时间正序

请发表友善的回复…

发表回复

sykkys 2012-09-14

打赏
举报

谢谢子龙！
请问大家还有其它更好的办法么

spiritofdragon 2012-09-14

打赏
举报

with t1 as (
select max(id) id,riqi from table1 group by riqi
),t2 as (
select id,zhuti from table1
),t3 as (
select id,riqi as a,1 id2 from t1
union all
select id,zhuti,2 id2 from t2
)
select a from t3 order by id desc,id2

sykkys 2012-09-14

打赏
举报

输出多行：（

pt1314917 2012-09-14

打赏
举报

输出一列？多行？

nanchangniat 2012-09-14

打赏
举报

create table tb#

(id int not null,

 zhuti varchar(10),

 riqi datetime)



insert into tb#(id, zhuti ,riqi)

select 1, '大家好','2012-1-3'

union all select 2,'大',' 2012-1-5'

union all select 3 , '家 ','2012-1-5'

union all select 4 , '好 ','2012-1-10'

union all select 5 , '欢迎','2012-1-10'

union all select 6 , '光临 ','2012-1-10'



select * from tb#



select zhuti 

from(

select distinct cast(year(riqi) as varchar(4))+' - '+ cast(month(riqi) as varchar(2))+' - '+ cast(day(riqi) as varchar(2)) as zhuti,riqi from tb#

union all

select zhuti,riqi from tb# ) as a

order by riqi desc,zhuti



--结果

/*

2012 - 1 - 10

光临 

好 

欢迎

2012 - 1 - 5

大

家 

2012 - 1 - 3

大家好

*/