请高手帮忙一下..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
...全文
98 7 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
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)
一铜钱&Moon 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
evilkoyou 2007-12-03
  • 打赏
  • 举报
回复
请高手帮忙一下..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

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧