首页 新闻 会员 周边

sql server

0
悬赏园豆：5 [已解决问题] 解决于 2012-09-05 16:53

一个学生表Students：

Id　　Name　　Sex

一个是学生得分表Scores(每天可以有多个得分记录)：

Id　　Time　　Scoe

Time为得分的日期，Score为分数（非100制）。

create table Students
(
Id int,
Name nvarchar(20),
Sex nchar(1)
)
go
create table Scores
(
Id int,
Time datetime,
Score int
)
go
insert into Students
select 1001,'小小','男' union all
select 1002,'小强','男'
go
insert into Scores
select 1001,'2012-1-1 10:00',1 union all
select 1001,'2012-1-1 11:00',3 union all
select 1001,'2012-1-1 13:00',4 union all
select 1002,'2012-1-1 8:00',7 union all
select 1002,'2012-1-1 8:00',8 union all
select 1002,'2012-1-1 11:00',1 union all
select 1001,'2012-1-8 8:00',4 union all
select 1002,'2012-1-8 14:00',1 union all
select 1002,'2012-1-8 15:00',5 union all
select 1002,'2012-1-8 16:00',6 union all
select 1001,'2012-1-15 8:00',5 union all
select 1002,'2012-1-15 8:00',6 union all
select 1001,'2012-1-21 8:00',7 union all
select 1002,'2012-1-21 8:00',16 union all
select 1001,'2012-1-29 8:00',15 union all
select 1002,'2012-1-29 8:00',9
go

 

我希望得到的是一个月份统计表的结果，输入参数是一个月份，显示是这个月里面每天每位学生的得分情况，如果输入参数为2012-1-1：

Id　　　　Name　　　　Sex　　Time　　　  　Score　　MaxScore　　MaxTime

1001　　 小小　　　　　男　　　2012-1-1      12　　　 7　　　　　　2012-1-1 9:00

1002　　 小强　　　　　男　　　2012-1-1      17 　　   10　　　　   2012-1-1 9:30

1001　　 小小　　　　　男　　　2012-1-2      10          .                 .

1002　　 小强　　　　　男　　　2012-1-2      16　　　 .                 .

                 　　　　　　.

                 　　　　　　.

                 　　　　　　.

                 　　　　　　.      

1001　　 小小　　　　　男　   　2012-1-31      12         .                  .

1002　　 小强　　　　　男　　   2012-1-31      15        .                  .

MaxScore为当天最大得分，MaxTime为当天最大得分的时间。

下面是sql:

declare @startTime datetime ='2012-1-1 00:00:00'--输入参数,开始时间

declare @endTime datetime 
set @endTime = DATEADD(MM,1,@startTime)--结束时间

--创建一个保存数据的临时表
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[#MyTable]') AND type in (N'U'))
DROP TABLE [dbo].[#MyTable]

create table #MyTable
(
    Id   char(8),
    Name char(8),
    Sex  char(8),
    Time datetime,
    Score decimal(8,3),
    MaxScoore decimal(8,3),
    MaxTime datetime
)

--我先遍历时间,从开始到结束
while @startTime<@endTime
    begin
        insert into #MyTable (Id,Name,Sex,Time,Score,MaxScoore,MaxTime)--添加数据
        select 
              Students.Id, Students.Name,Students.Sex,
              @startTime as Time,
              (select sum(Score) from Scores
                where Time >= @startTime and Time< DATEADD(DD,1,@startTime)
                     and Students.Id = Scores.Id
               ) as Score,
               
               (select MAX(Score) from Scores
                 where Time >= @startTime and Time< DATEADD(DD,1,@startTime)
                     and Students.Id = Scores.Id
               ) as MaxScore,  --当天最大得分
               
               (select top 1 Time from Scores
                 where Time >= @startTime and Time< DATEADD(DD,1,@startTime)
                     and Students.Id = Scores.Id
                 order by Score desc
               ) as MaxTM   --当天最大得分时间
               
        from  Students
    set @startTime = DATEADD(DD,1,@startTime)
    end

不知道可不可以优化,有其他方法也可以。

问题补充：

其中一个方案,别人提供的但是不太懂.

declare @date datetime
set @date = '2012-01-01';
with ctea as         --一个月的所有天,构成的列表
(
select dateadd(dd,number,@date) as time from master..spt_values 
where type = 'p' and dateadd(dd,number,@date) <= dateadd(dd,-day(dateadd(m,1,@date)),dateadd(m,1,@date))
),
cteb as          
(
    select
      id,
      convert(varchar(10),[time],121) time,
      sum(Score) score,
      max([time]) maxtime,  --最大时间,而我要的是最大得分时候的时间
      max(Score) maxscore,
      cast(id as varchar)+convert(varchar(10),[time],121) un 
    from Scores group by id,convert(varchar(10),[time],121) 
),
ctec as
(
    select 
      id,name,sex,
      cast(id as varchar)+convert(varchar(10),time,121) un,
      convert(varchar(10),time,121) time from Students 
      cross join ctea
)
select c.id,c.name,c.sex,c.time,b.maxtime,b.score,b.maxscore
from ctec c left join cteb b on c.un = b.un
order by c.time

 
凡一二三的主页 凡一二三 | 初学一级 | 园豆：85
提问于：2012-08-31 12:25
< >
分享
最佳答案
0

你后面实现的代码跟你描述的有点出入吧.看你的实现代码,应该是要变成这样:

select s.Id,s.Name,s.Sex,convert(date,s2.[Time]) as [time],sum(s2.Score) as Score,max(s2.Score) as MaxScore,max(s2.[Time]) as MaxTime
from dbo.Students as s
join dbo.Scores as s2 on s.Id = s2.Id
group by s.Id,s.Name,s.Sex,convert(date,s2.[Time])
收获园豆：5
老玉米 | 菜鸟二级 |园豆：207 | 2012-09-05 10:32

max(s2.[Time]) as MaxTime
虽然这个是最大时间，我要的是最值的发生时间。但我在套一个select,用select top 1 Time from scores where score = maxscore就获取了那个点的时间。没了循环性能的确提升了。

凡一二三 | 园豆：85 (初学一级) | 2012-09-05 16:56
其他回答(2)
0 
DECLARE @startTime DATETIME = '2012-1-1 00:00:00'
--输入参数,开始时间
SELECT  A.*, B.*, ( SELECT MAX (Score) FROM Scores WHERE DATEDIFF (dd, time, A.time)= 0 AND id= A.ID
                  ) AS MaxScore,  --当天最大得分
        ( SELECT TOP 1 time from Scores
          WHERE     DATEDIFF(dd, time, A.time) = 0   --按天比较
                    AND id = A.ID
          ORDER BY  Score DESC
        ) AS MaxTM --当天最大得分时间
FROM    Scores A
        LEFT JOIN Students B ON A.id = B.id
WHERE   DATEDIFF(mm, a.time, '2012-1-1') = 0  --按月比较

 

可以使用日期处理 结合子查询避免使用循环返回结果

 

 

 

acepro | 园豆：1218 (小虾三级) | 2012-08-31 13:08
0 
select t2.Id,t2.Name,t2.Sex, t1.day from  (
select  Id,sum(Score) as Score,max(Score) as MaxScore,substr(Time,0,8) as day from Scores group by substr(Time,0,8),Id
) t1 left join Students t2 on t1.Id=t2.Id

left join Scores t3 on t1.Id=t3.Id and t1.day=substr(t3.Time,0,8) and t1.MaxScore=t3.Score
把substr(Time,0,8)改成可运行的，忘了截取时间怎么写。。。
向往-SONG | 园豆：4853 (老鸟四级) | 2012-08-31 13:22

这个不行吗？

支持(0) 反对(0) 向往-SONG | 园豆：4853 (老鸟四级) | 2012-09-03 18:08
清除回答草稿
   您需要登录以后才能回答，未注册用户请先注册。