学生每天竞技比赛，赢了得分，输了负分，0表示当天没参加比赛。现在我想求 （ 如果有两个或多个相同的最低分，只选择 最 靠近2010/11/16的 ）比如2010/10/15 - 2010/11/16期间
每个学生的最低分，
获得这个最低分的日期，
每个学生获得各自最低分的日期之前获得的最高分
每个学生获得各自最低分的日期之后获得的最高分
日期 学号 姓名 成绩
2010/10/15 1 张三 12
2010/10/15 2 李四 -8
2010/10/15 3 王五 15
2010/10/16 1 张三 21
2010/10/16 2 李四 -22
2010/10/17 1 张三 12
2010/10/18 2 李四 -10
2010/10/19 3 王五 11
2010/11/16 1 张三 13
2010/11/18 3 王五 -23
2010/11/18 1 张三 19
2010/11/18 2 李四 0希望求的表格： 学号 姓名 最低分 最低分日期 之前的最高分 之后的最高分 万分感谢各位大牛的帮助啊！！！！
create table tb(日期 datetime,学号 int,姓名 varchar(10),成绩 int)
insert into tb values('2010/10/15', 1 ,'张三', 12)
insert into tb values('2010/10/15', 2 ,'李四', -8)
insert into tb values('2010/10/15', 3 ,'王五', 15)
insert into tb values('2010/10/16', 1 ,'张三', 21)
insert into tb values('2010/10/16', 2 ,'李四', -22)
insert into tb values('2010/10/17', 1 ,'张三', 12)
insert into tb values('2010/10/18', 2 ,'李四', -10)
insert into tb values('2010/10/19', 3 ,'王五', 11)
insert into tb values('2010/11/16', 1 ,'张三', 13)
insert into tb values('2010/11/18', 3 ,'王五', -23)
insert into tb values('2010/11/18', 1 ,'张三', 19)
insert into tb values('2010/11/18', 2 ,'李四', 0)
go
select t1.学号 , t1.姓名 , t1.成绩 最低分 , t1.日期 最低分日期,
t2.成绩 之前的最高分 , t3.成绩 之后的最高分
from
(
select t.* from tb t where not exists(select 1 from tb where 学号 = t.学号 and (成绩 < t.成绩 or (成绩 = t.成绩 and 日期 < t.日期)))
) t1 left join
(
select m.学号,max(m.成绩) 成绩 from tb m ,
(
select t.* from tb t where not exists(select 1 from tb where 学号 = t.学号 and (成绩 < t.成绩 or (成绩 = t.成绩 and 日期 < t.日期)))
) n
where m.学号 = n.学号 and m.日期 < n.日期
group by m.学号
) t2 on t1.学号 = t2.学号 left join
(
select m.学号,max(m.成绩) 成绩 from tb m ,
(
select t.* from tb t where not exists(select 1 from tb where 学号 = t.学号 and (成绩 < t.成绩 or (成绩 = t.成绩 and 日期 < t.日期)))
) n
where m.学号 = n.学号 and m.日期 > n.日期
group by m.学号
) t3 on t1.学号 = t3.学号drop table tb/*
学号 姓名 最低分 最低分日期 之前的最高分 之后的最高分
----------- ---------- ----------- ------------------------------------------------------ ----------- -----------
1 张三 12 2010-10-15 00:00:00.000 NULL 21
2 李四 -22 2010-10-16 00:00:00.000 -8 0
3 王五 -23 2010-11-18 00:00:00.000 15 NULL（所影响的行数为 3 行）
*/--为-999表示没有.
create table tb(日期 datetime,学号 int,姓名 varchar(10),成绩 int)
insert into tb values('2010/10/15', 1 ,'张三', 12)
insert into tb values('2010/10/15', 2 ,'李四', -8)
insert into tb values('2010/10/15', 3 ,'王五', 15)
insert into tb values('2010/10/16', 1 ,'张三', 21)
insert into tb values('2010/10/16', 2 ,'李四', -22)
insert into tb values('2010/10/17', 1 ,'张三', 12)
insert into tb values('2010/10/18', 2 ,'李四', -10)
insert into tb values('2010/10/19', 3 ,'王五', 11)
insert into tb values('2010/11/16', 1 ,'张三', 13)
insert into tb values('2010/11/18', 3 ,'王五', -23)
insert into tb values('2010/11/18', 1 ,'张三', 19)
insert into tb values('2010/11/18', 2 ,'李四', 0)
go
select t1.学号 , t1.姓名 , t1.成绩 最低分 , t1.日期 最低分日期,
isnull(t2.成绩,-999) 之前的最高分 , isnull(t3.成绩,-999) 之后的最高分
from
(
select t.* from tb t where not exists(select 1 from tb where 学号 = t.学号 and (成绩 < t.成绩 or (成绩 = t.成绩 and 日期 < t.日期)))
) t1 left join
(
select m.学号,max(m.成绩) 成绩 from tb m ,
(
select t.* from tb t where not exists(select 1 from tb where 学号 = t.学号 and (成绩 < t.成绩 or (成绩 = t.成绩 and 日期 < t.日期)))
) n
where m.学号 = n.学号 and m.日期 < n.日期
group by m.学号
) t2 on t1.学号 = t2.学号 left join
(
select m.学号,max(m.成绩) 成绩 from tb m ,
(
select t.* from tb t where not exists(select 1 from tb where 学号 = t.学号 and (成绩 < t.成绩 or (成绩 = t.成绩 and 日期 < t.日期)))
) n
where m.学号 = n.学号 and m.日期 > n.日期
group by m.学号
) t3 on t1.学号 = t3.学号drop table tb/*
学号 姓名 最低分 最低分日期 之前的最高分 之后的最高分
----------- ---------- ----------- ------------------------------------------------------ ----------- -----------
1 张三 12 2010-10-15 00:00:00.000 -999 21
2 李四 -22 2010-10-16 00:00:00.000 -8 0
3 王五 -23 2010-11-18 00:00:00.000 15 -999（所影响的行数为 3 行）
*/
declare @t table (日期 datetime,学号 int,姓名 varchar(4),成绩 int)
insert into @t
select '2010/10/15',1,'张三',12 union all
select '2010/10/15',2,'李四',-8 union all
select '2010/10/15',3,'王五',15 union all
select '2010/10/16',1,'张三',21 union all
select '2010/10/16',2,'李四',-22 union all
select '2010/10/17',1,'张三',12 union all
select '2010/10/18',2,'李四',-10 union all
select '2010/10/19',3,'王五',11 union all
select '2010/11/16',1,'张三',13 union all
select '2010/11/18',3,'王五',-23 union all
select '2010/11/18',1,'张三',19 union all
select '2010/11/18',2,'李四',0;WITH maco AS(
select 学号 ,
姓名 ,
成绩 as 最低分 ,
convert(varchar(10), 日期, 120) as 最低分日期 ,
( select ISNULL(max(成绩),-999)
from @t
where 日期 < m.日期
and 学号 = m.学号
) as 之前的最高分 ,
( select ISNULL(max(成绩),-999)
from @t
where 日期 > m.日期
and 学号 = m.学号
) 之后的最高分
from ( select *
from @t t
where 成绩 = ( select min(成绩)
from @t b
where 学号 = t.学号
)
) m)
select * from maco t
where 最低分日期=(select max(最低分日期) from maco where 姓名=t.姓名)
/*
学号 姓名 最低分 最低分日期 之前的最高分 之后的最高分
----------- ---- ----------- ---------- ----------- -----------
1 张三 12 2010-10-17 21 19
2 李四 -22 2010-10-16 -8 0
3 王五 -23 2010-11-18 15 -999
*/
表中数据从2010/1/1 -- 2010/12/31
可能有6W的数据0表示没参加考试，不计入最低分现在想要从中截取 2010/10/15 -- 2010/11/16 的数据进行统计
求出期间最低分（如果有两个相同的最低分，选择最靠近2010/11/16作为最低分）
获得最低分的日期
最低分的日期 之前的最高分（如果有多个相同最高分，选最靠近获得最低分日期的）
最低分的日期 之后的最高分（如果有多个相同最高分，选最靠近获得最低分日期的）日期 学号 姓名 成绩
2010/10/15 1 张三 12
2010/10/15 2 李四 60
2010/10/15 3 王五 0
2010/10/16 1 张三 21
2010/10/16 2 李四 8
2010/10/17 1 张三 12
2010/10/18 2 李四 0
2010/10/19 3 王五 11
2010/11/16 1 张三 13
2010/11/16 3 王五 74
2010/11/16 2 李四 192010/11/18 2 李四 99
2010/11/18 1 张三 99
2010/11/18 3 王五 99
11/18
超出查询范围，不计入希望得到的查询结果学号 姓名 最低分 最低分日期 之前最高分 之后最高分 2010/10/15--2010/11/16
1 张三 12 2010/10/17 21 13
2 李四 8 2010/10/16 60 19
3 王五 11 2010/10/19 -999 99
*/
go
if OBJECT_ID('tbl')is not null
drop table tbl
go
create table tbl(
日期 datetime,
学号 int,
姓名 varchar(4),
成绩 int
)
go
insert tbl
select '2010/10/15',1,'张三',12 union all
select '2010/10/15',2,'李四',8 union all
select '2010/10/15',3,'王五',15 union all
select '2010/10/16',1,'张三',21 union all
select '2010/10/16',2,'李四',22 union all
select '2010/10/17',1,'张三',12 union all
select '2010/10/18',2,'李四',10 union all
select '2010/10/19',3,'王五',11 union all
select '2010/11/16',1,'张三',13 union all
select '2010/11/18',3,'王五',23 union all
select '2010/11/18',1,'张三',19 union all
select '2010/11/18',2,'李四',0;with T
as
(
select 学号,姓名,成绩 as 最低分,日期 as 最低分日期 from
(select row_number()over(partition by 学号,姓名 order by 成绩,datediff(dd,日期,getdate()) asc)
as number,*from tbl where 日期 between '2010/10/16' and '2010/11/15')a where number=1
),
M
as
(
select 学号,MAX(成绩) as 之前最高分 from tbl a
where a.日期<(select 最低分日期 from T where a.学号=T.学号)
group by 学号
),
N
as
(
select 学号,MAX(成绩) as 之后最高分 from tbl b
where b.日期>(select 最低分日期 from T where b.学号=T.学号)
group by 学号
)select T.学号,T.姓名,T.最低分,T.最低分日期,M.之前最高分,N.之后最高分
from T inner join M on T.学号=M.学号
inner join N on T.学号=N.学号/*
学号 姓名 最低分 最低分日期 之前最高分 之后最高分
1 张三 12 2010-10-17 00:00:00.000 21 19
2 李四 10 2010-10-18 00:00:00.000 22 0
3 王五 11 2010-10-19 00:00:00.000 15 23
*//*
我觉得楼主表述有点问题：
--最低分的日期 之前的最高分（如果有多个相同最高分，选最靠近获得最低分日期的）
--最低分的日期 之后的最高分（如果有多个相同最高分，选最靠近获得最低分日期的）
不明白这个意思。
我这样理解，即使有相同的最高分，但最后结果集中没有要求要得到最高分的日期，所以
没有必要去确定那个日期作为最高分，您觉得呢？
*/