全部
基础类
应用实例
新技术前沿

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

evilkoyou 2007-12-03 03:33:35

请高手帮忙一下..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

...全文

54 点赞收藏 7

写回复

7 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

cxmcxm 2007-12-03

--假设表名为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

回复赞

-狙击手- 2007-12-03

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 行）



*/

回复赞

中国风 2007-12-03

或:

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 行）

回复赞

中国风 2007-12-03

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 行）

回复赞

-狙击手- 2007-12-03





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)

回复赞

joymxg 2007-12-03





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 = @total



select * from #total