求助:sql查询
原表的数据是这样的：
现要通过sql实现下面的效果:
不知道如何实现，求助于高手指点，非常谢谢O(∩_∩)O
[解决办法]
select convert(char(10),play_time,120),convert(char(5),play_time,108)
select (case when row=1 then play_time else '' end)play_time,play_name,begin_time,url
from
(select row=row_number()over(partition by convert(char(10),play_time,120) order by convert(varchar(10),cast(play_time as datetime),24)),convert(char(10),play_time,120)play_time,play_name,convert(varchar(10),cast(play_time as datetime),24)begin_time,url from tb)t
select id,
(case when exists(select 1 from tb where convert(varchar(10),play_time,120)=convert(varchar(10),a.play_time,120))
then '' else convert(varchar(10),play_time,120) end) as play_time,
play_name,convert(varchar(5),play_time,108),url
from tb a order by id
create table tb(id int,play_name nvarchar(10),play_time datetime,url nvarchar(30))
insert into tb select 1,'aaa','2013-03-04 19:30:00','u1'
insert into tb select 2,'bbb','2013-03-04 22:30:00','u2'
insert into tb select 3,'ccc','2013-03-04 23:24:00','u3'
insert into tb select 4,'ddd','2013-03-05 17:32:00','u4'
go
select id,
(case when exists(select 1 from tb where id<a.id and convert(varchar(10),play_time,120)=convert(varchar(10),a.play_time,120))
then '' else convert(varchar(10),play_time,120) end) as play_time,
play_name,convert(varchar(5),play_time,108) begintime,url
from tb a order by id
/*
id play_time play_name begintime url
----------- ---------- ---------- --------- ------------------------------
1 2013-03-04 aaa 19:30 u1
2 bbb 22:30 u2
3 ccc 23:24 u3
4 2013-03-05 ddd 17:32 u4
（所影响的行数为 4 行）
*/
go
drop table tb
你这种干嘛一定要用sql来实现，你可以sql语句只用order by play_time就可以了。具体的显示，你放到你的客户端程序中去控制。
