时间检索的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
其他回答(2)
0
select top 5 a,b from table order by c desc
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
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
