declare @tb table(name varchar(20),stime datetime,smonth varchar(20))
insert into @tb
select 'test1','2008-12-09 16:59:10.000','8月' union all
select 'test1','2008-12-09 17:04:24.000','8月' union all
select 'test1','2008-12-10 11:30:32.000','10月' union allselect 'test2','2008-12-10 11:32:08.000','11月' union all
select 'test2','2008-12-10 11:37:58.000','8月' union all
select 'test2','2008-12-10 11:42:42.000','9月' union allselect 'test3','2008-12-10 11:46:38.000','10月' union all
select 'test3','2008-12-11 09:12:00.000','10月' union all
select 'test3','2008-12-11 10:06:28.000','11月' union all
select 'test3','2008-12-11 11:45:42.000','12月' select * from @tb /*** 若以时间为条件查询我要得到下面得结果,分别为每个人名前面加 序号,这个sql怎么写??***/select * from @tb where stime between '2008-12-09 17:04:24.000' and '2008-12-10 11:37:58.000'
/*
id name stime smonth
-------------------- ------------------------------ -----------------------
1 test1 2008-12-09 17:04:24.000 8月
2 test1 2008-12-10 11:30:32.000 10月
1 test2 2008-12-10 11:32:08.000 8月
2 test2 2008-12-10 11:37:58.000 11月
*/select * from @tb where stime between '2008-12-09 16:59:10.000' and '2008-12-11 11:45:42.000'/*
id name stime smonth
-- -------------------- ---------------------- -------
1 test1 2008-12-09 16:59:10.000 8月
2 test1 2008-12-09 17:04:24.000 8月
3 test1 2008-12-10 11:30:32.000 10月
1 test2 2008-12-10 11:37:58.000 8月
2 test2 2008-12-10 11:42:42.000 9月
3 test2 2008-12-10 11:32:08.000 11月
1 test3 2008-12-10 11:32:08.000 10月
2 test3 2008-12-11 09:12:00.000 10月
3 test3 2008-12-11 10:06:28.000 11月
4 test3 2008-12-11 11:45:42.000 12月
*/
insert into @tb
select 'test1','2008-12-09 16:59:10.000','8月' union all
select 'test1','2008-12-09 17:04:24.000','8月' union all
select 'test1','2008-12-10 11:30:32.000','10月' union allselect 'test2','2008-12-10 11:32:08.000','11月' union all
select 'test2','2008-12-10 11:37:58.000','8月' union all
select 'test2','2008-12-10 11:42:42.000','9月' union allselect 'test3','2008-12-10 11:46:38.000','10月' union all
select 'test3','2008-12-11 09:12:00.000','10月' union all
select 'test3','2008-12-11 10:06:28.000','11月' union all
select 'test3','2008-12-11 11:45:42.000','12月' select * from @tb /*** 若以时间为条件查询我要得到下面得结果,分别为每个人名前面加 序号,这个sql怎么写??***/select * from @tb where stime between '2008-12-09 17:04:24.000' and '2008-12-10 11:37:58.000'
/*
id name stime smonth
-------------------- ------------------------------ -----------------------
1 test1 2008-12-09 17:04:24.000 8月
2 test1 2008-12-10 11:30:32.000 10月
1 test2 2008-12-10 11:32:08.000 8月
2 test2 2008-12-10 11:37:58.000 11月
*/select * from @tb where stime between '2008-12-09 16:59:10.000' and '2008-12-11 11:45:42.000'/*
id name stime smonth
-- -------------------- ---------------------- -------
1 test1 2008-12-09 16:59:10.000 8月
2 test1 2008-12-09 17:04:24.000 8月
3 test1 2008-12-10 11:30:32.000 10月
1 test2 2008-12-10 11:37:58.000 8月
2 test2 2008-12-10 11:42:42.000 9月
3 test2 2008-12-10 11:32:08.000 11月
1 test3 2008-12-10 11:32:08.000 10月
2 test3 2008-12-11 09:12:00.000 10月
3 test3 2008-12-11 10:06:28.000 11月
4 test3 2008-12-11 11:45:42.000 12月
*/
*
from @tb
where stime between '2008-12-09 17:04:24.000' and '2008-12-10 11:37:58.000'
insert into @tb
select 'test1','2008-12-09 16:59:10.000','8月' union all
select 'test1','2008-12-09 17:04:24.000','8月' union all
select 'test1','2008-12-10 11:30:32.000','10月' union all
select 'test2','2008-12-10 11:32:08.000','11月' union all
select 'test2','2008-12-10 11:37:58.000','8月' union all
select 'test2','2008-12-10 11:42:42.000','9月' union all
select 'test3','2008-12-10 11:46:38.000','10月' union all
select 'test3','2008-12-11 09:12:00.000','10月' union all
select 'test3','2008-12-11 10:06:28.000','11月' union all
select 'test3','2008-12-11 11:45:42.000','12月'
select id=(select count(*)+1 from (select* from @tb t where t.stime between '2008-12-09 17:04:24.000' and '2008-12-10 11:37:58.000') a where a.name=b.name and cast(left(a.smonth,len(a.smonth)-1)as int)<cast(left(b.smonth,len(b.smonth)-1)as int)),* from
(select* from @tb t where t.stime between '2008-12-09 17:04:24.000' and '2008-12-10 11:37:58.000')b
/*id name stime smonth
----------- -------------------- ------------------------------------------------------ --------------------
1 test1 2008-12-09 17:04:24.000 8月
2 test1 2008-12-10 11:30:32.000 10月
2 test2 2008-12-10 11:32:08.000 11月
1 test2 2008-12-10 11:37:58.000 8月*/
insert into @tb
select 'test1','2008-12-09 16:59:10.000','8月' union all
select 'test1','2008-12-09 17:04:24.000','8月' union all
select 'test1','2008-12-10 11:30:32.000','10月' union all
select 'test2','2008-12-10 11:32:08.000','11月' union all
select 'test2','2008-12-10 11:37:58.000','8月' union all
select 'test2','2008-12-10 11:42:42.000','9月' union all
select 'test3','2008-12-10 11:46:38.000','10月' union all
select 'test3','2008-12-11 09:12:00.000','10月' union all
select 'test3','2008-12-11 10:06:28.000','11月' union all
select 'test3','2008-12-11 11:45:42.000','12月'
select id=(select count(*)+1 from (select* from @tb t where t.stime between '2008-12-09 17:04:24.000' and '2008-12-10 11:37:58.000') a where a.name=b.name and cast(left(a.smonth,len(a.smonth)-1)as int)<cast(left(b.smonth,len(b.smonth)-1)as int)),* from
(select* from @tb t where t.stime between '2008-12-09 17:04:24.000' and '2008-12-10 11:37:58.000')b order by name,id
/*id name stime smonth
----------- -------------------- ------------------------------------------------------ --------------------
1 test1 2008-12-09 17:04:24.000 8月
2 test1 2008-12-10 11:30:32.000 10月
1 test2 2008-12-10 11:37:58.000 8月
2 test2 2008-12-10 11:32:08.000 11月*/
select row_number() over(partition by name order by cast(replace(smonth,'月','') as int),stime) as id,
*
from @tb
where stime between '2008-12-09 17:04:24.000' and '2008-12-10 11:37:58.000'
order by name
select (select count(*) as id from @tb as B where stime between '2008-12-09 16:59:10.000' and '2008-12-11 11:45:42.000' and B.stime>=A.stime and A.name=B.name) as id, *
from @tb as A where stime between '2008-12-09 16:59:10.000' and '2008-12-11 11:45:42.000'
order by name,id
*
from @tb
where stime between '2008-12-09 17:04:24.000' and '2008-12-10 11:37:58.000'
先 分割partition by name 在排序 order by stime