请高手帮忙一下..sql.语法

Question

请高手帮忙一下..sql.语法我要在下面列表中查出总合时间(ttime),又可以列出最近一笔日期的数据要这么做呢?User_id  test_area quest score ttime       learn_time
S001          2^30      28    76      54     2008-06-27 10:47:00
S001          2^23      21    76      88     2008-06-22 10:47:00
S001          2^24      22    76      32     2008-06-21 10:47:00
S001          2^25      23    76      77     2008-06-20 10:47:00
=>
User_id  test_area    quest   score               learn_time                    total_ttime
S001           2^30      28         76          2008-06-27 10:47:00                     251    

Answer 1

请高手帮忙一下..sql.语法 我要在下面列表中查出总合时间(ttime),又可以列出最近一笔日期的数据要这么做呢? User_id                test_area          quest    score           ttime               learn_time 
S001                     2^30             28         76             54           2008-06-27   10:47:00 
S001                     2^23             21         76             88           2008-06-22   10:47:00 
S001                     2^24             22         76             32           2008-06-21   10:47:00 
S001                     2^25             23         76             77           2008-06-20   10:47:00 
=> 
User_id     test_area         quest       score         learn_time                   total_ttime 
S001          2^30             28           76     2008-06-27   10:47:00                 251         

Answer 2

select a.* into #total from (
SELECT top 1 [User_id]
      ,[test_area]
      ,[quest]
      ,[score]
      ,[ttime]
      ,[learn_time], 1 as total_ttime   
FROM [dbo].[A]
order by [learn_time] desc) a
declare @total int
 select @total =sum([ttime]) from [dbo].[A]
update #total
set total_ttime = @totalselect * from #total

Answer 3

select a.*,b.total_time
from tablea a 
left join (select user_id,sum(ttime) as total_time from tablea group by user_id) b
on a.user_id = b.user_id
where not exists(select 1 from tablea where a.userid = userid and learn_time < a.learn_time)

Answer 4

use test
go
declare @T table(User_id char(4),     test_area nvarchar(4),  quest int,   score int,  ttime   int,            learn_time datetime)
insert @T select 'S001',                     '2^30',             28,         76,             54,           '2008-06-27   10:47:00' 
insert @T select 'S001',                     '2^23',             21,         76,             88,           '2008-06-22   10:47:00' 
insert @T select 'S001',                     '2^24',             22,         76,             32,           '2008-06-21   10:47:00' 
insert @T select 'S001',                     '2^25',             23,         76,             77,           '2008-06-20   10:47:00'select 
User_id,test_area,quest,score,
[ttime]=(select sum(ttime) from @t where User_id=t.User_id),
learn_time
 from 
@T  t 
where 
not exists(select 1 from @T where User_id=t.User_id and learn_time>t.learn_time) ser_id test_area quest       score       ttime       learn_time                                             
------- --------- ----------- ----------- ----------- ------------------------------------------------------ 
S001    2^30      28          76          251         2008-06-27 10:47:00.000（所影响的行数为 1 行）

Answer 5

或:
use test
go
declare @T table(User_id char(4),     test_area nvarchar(4),  quest int,   score int,  ttime   int,            learn_time datetime)
insert @T select 'S001',                     '2^30',             28,         76,             54,           '2008-06-27   10:47:00' 
insert @T select 'S001',                     '2^23',             21,         76,             88,           '2008-06-22   10:47:00' 
insert @T select 'S001',                     '2^24',             22,         76,             32,           '2008-06-21   10:47:00' 
insert @T select 'S001',                     '2^25',             23,         76,             77,           '2008-06-20   10:47:00'select 
User_id,test_area,quest,score,
[ttime]=(select sum(ttime) from @t where User_id=t.User_id),
learn_time
 from 
@T  t 
where 
learn_time=(select max(learn_time) from @T where User_id=t.User_id) 
User_id test_area quest       score       ttime       learn_time                                             
------- --------- ----------- ----------- ----------- ------------------------------------------------------ 
S001    2^30      28          76          251         2008-06-27 10:47:00.000（所影响的行数为 1 行）

Answer 6

declare @T table(User_id char(4),     test_area nvarchar(4),  quest int,   score int,  ttime   int,            learn_time datetime)
insert @T select 'S001',                     '2^30',             28,         76,             54,           '2008-06-27   10:47:00' 
insert @T select 'S001',                     '2^23',             21,         76,             88,           '2008-06-22   10:47:00' 
insert @T select 'S001',                     '2^24',             22,         76,             32,           '2008-06-21   10:47:00' 
insert @T select 'S001',                     '2^25',             23,         76,             77,           '2008-06-20   10:47:00'select a.*,b.total_time
from @T a 
left join (select user_id,sum(ttime) as total_time from @T group by user_id) b
on a.user_id = b.user_id
where not exists(select 1 from @T where a.user_id = user_id and learn_time > a.learn_time)
/*User_id test_area quest       score       ttime       learn_time                                             total_time  
------- --------- ----------- ----------- ----------- ------------------------------------------------------ ----------- 
S001    2^30      28          76          54          2008-06-27 10:47:00.000                                251（所影响的行数为 1 行）*/

Answer 7

--假设表名为t1
select t1.user_id,t1.test_area,t1.quest,t1.score,t1.learn_time,a.total_ttime
(select user_id,max(learn_time) learn_time,sum(ttime) total_ttime from t1 group by user_id) a,
t1
where a.user_id=t1.user_id and a.learn_time=t1.learn_time