首页 新闻 搜索 专区 学院

时间检索的SQL语句如何写

0
[已关闭问题]

数据库AAA如下
a b c
A 2 2010-02-05
A 3 2010-03-04
A 5 2010-05-06
A 6 2010-07-08
A 8 2010-07-09
A 4 2010-08-01
B 3 2010-02-05
B 2 2010-03-04
B 1 2010-05-06
B 3 2010-07-08
B 8 2010-07-09
B 3 2010-08-01

求最大的一天和最大的一天的前四天(注意不是自然天,而是小于最大天的数据库里有的那四天) b的总和

结果如下:
a b
A 26
B 17

xzf_fancy的主页 xzf_fancy | 初学一级 | 园豆:28
提问于:2010-03-09 23:12
< >
分享
其他回答(2)
0

select top 5 a,b from table order by c desc

Ou lei | 园豆:619 (小虾三级) | 2010-03-10 09:31
0

代码
declare @t table(a varchar(1),b int,c datetime)
insert @t

select 'A', 2, '2010-02-05'
union all
select 'A', 3, '2010-03-04'
union all
select 'A', 5, '2010-05-06'
union all
select 'A', 6, '2010-07-08'
union all
select 'A', 8, '2010-07-09'
union all
select 'A', 4, '2010-08-01'
union all
select 'B', 3, '2010-02-05'
union all
select 'B', 2, '2010-03-04'
union all
select 'B', 1, '2010-05-06'
union all
select 'B', 3, '2010-07-08'
union all
select 'B', 8, '2010-07-09'
union all
select 'B', 3, '2010-08-01'

select * from @t

select a,sum(b)
from @t t
where c <> (select min(c) from @t where a=t.a)
group by a

 

清海扬波 | 园豆:825 (小虾三级) | 2010-03-10 09:36
0

if OBJECT_ID('AAAAAAA') is not null
drop table AAAAAAA
go

Create table AAAAAAA
(a
Nvarchar(10)
,b
int
,c
datetime
)
go
insert into AAAAAAA
select 'A', 2, '2010-02-05'
union all select 'A', 3 ,'2010-03-04'
union all select 'A', 5, '2010-05-06'
union all select 'A', 6 ,'2010-07-08'
union all select 'A', 8 ,'2010-07-09'
union all select 'A', 4 ,'2010-08-01'
union all select 'B', 3, '2010-02-05'
union all select 'B', 2, '2010-03-04'
union all select 'B', 1 ,'2010-05-06'
union all select 'B', 3, '2010-07-08'
union all select 'B', 8 ,'2010-07-09'
union all select 'B', 3 ,'2010-08-01'

select * from AAAAAAA
go


select a,sum(b)as bCount
from AAAAAAA t
where c in (select top 5 c from AAAAAAA where a=t.a order by c desc)
group by a

 

结果:
a    bCount
A    26
B    17

邀月 | 园豆:25475 (高人七级) | 2010-03-10 10:09
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册