下面语句怎么写?谢谢!

tian790317 2005-11-13 03:07:41
"aid","bid","date1","exchage"
"a","b",2005-01-01 00:00:00,"0.1"
"a","c",2005-01-01 00:00:00,"0.2"
"a","d",2005-01-01 00:00:00,"0.3"
"a","e",2005-01-01 00:00:00,"0.5"
"b","a",2005-03-01 00:00:00,"0.5"
"b","c",2004-03-01 00:00:00,"0.6"
"b","d",2005-03-01 00:00:00,"0.8"
"c","a",2005-04-05 00:00:00,"0.1"
"c","b",2005-04-05 00:00:00,"0.5"
"c","d",2005-04-05 00:00:00,"0.2"
"c","e",2005-04-05 00:00:00,"0.2"
"e","a",2004-01-02 00:00:00,"0.1"
"e","h",2004-02-01 00:00:00,"0.5"
"a","b",2005-08-01 00:00:00,"0.1"
"a","c",2005-08-01 00:00:00,"0.2"
"a","d",2005-08-01 00:00:00,"0.3"
"a","e",2005-08-01 00:00:00,"0.5"
"c","b",2005-07-01 00:00:00,"0.1"
"c","a",2005-07-01 00:00:00,"01"
"c","d",2005-07-01 00:00:00,"0.4"
"c","e",2005-07-01 00:00:00,"0.4"
"d","a",2005-08-01 00:00:00,"0.5"
"d","b",2005-08-01 00:00:00,"0.8"
"d","c",2005-08-01 00:00:00,"0.4"
"d","e",2005-08-01 00:00:00,"0.5"
"d","a",2004-01-01 00:00:00,"0.3"
"d","c",2004-01-01 00:00:00,"0.5"
"d","c",2004-01-01 00:00:00,"0.6"
"d","a",2005-01-01 00:00:00,"0.1"
"d","c",2005-01-01 00:00:00,"014"
"d","b",2005-01-01 00:00:00,"0.4"
"d","e",2005-01-01 00:00:00,"0.6"
我想得到如下结果怎么做?也就是aid每天重复4次,并且date1是最新的日期的记录.谢谢!
"aid","bid","date1","exchage"
"a","b",2005-08-01 00:00:00,"0.1"
"a","c",2005-08-01 00:00:00,"0.2"
"a","d",2005-08-01 00:00:00,"0.3"
"a","e",2005-08-01 00:00:00,"0.5"
"c","b",2005-07-01 00:00:00,"0.1"
"c","a",2005-07-01 00:00:00,"01"
"c","d",2005-07-01 00:00:00,"0.4"
"c","e",2005-07-01 00:00:00,"0.4"
"d","a",2005-08-01 00:00:00,"0.5"
"d","b",2005-08-01 00:00:00,"0.8"
"d","c",2005-08-01 00:00:00,"0.4"
"d","e",2005-08-01 00:00:00,"0.5"
...全文
125 点赞 收藏 5
写回复
5 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
lxzm1001 2005-12-26
create table #tb(aid varchar(1),bid varchar(1), date1 datetime, exchage numeric(8,1))
Insert into #tb (aid, bid, date1,exchage)
select 'a','b','2005-01-01 00:00:00','0.1'
union all select 'a','c','2005-01-01 00:00:00','0.2'
union all select 'a','d','2005-01-01 00:00:00','0.3'
union all select 'a','e','2005-01-01 00:00:00','0.5'
union all select 'b','a','2005-03-01 00:00:00','0.5'
union all select 'b','c','2004-03-01 00:00:00','0.6'
union all select 'b','d','2005-03-01 00:00:00','0.8'
union all select 'c','a','2005-04-05 00:00:00','0.1'
union all select 'c','b','2005-04-05 00:00:00','0.5'
union all select 'c','d','2005-04-05 00:00:00','0.2'
union all select 'c','e','2005-04-05 00:00:00','0.2'
union all select 'e','a','2004-01-02 00:00:00','0.1'
union all select 'e','h','2004-02-01 00:00:00','0.5'
union all select 'a','b','2005-08-01 00:00:00','0.1'
union all select 'a','c','2005-08-01 00:00:00','0.2'
union all select 'a','d','2005-08-01 00:00:00','0.3'
union all select 'a','e','2005-08-01 00:00:00','0.5'
union all select 'c','b','2005-07-01 00:00:00','0.1'
union all select 'c','a','2005-07-01 00:00:00','01'
union all select 'c','d','2005-07-01 00:00:00','0.4'
union all select 'c','e','2005-07-01 00:00:00','0.4'
union all select 'd','a','2005-08-01 00:00:00','0.5'
union all select 'd','b','2005-08-01 00:00:00','0.8'
union all select 'd','c','2005-08-01 00:00:00','0.4'
union all select 'd','e','2005-08-01 00:00:00','0.5'
union all select 'd','a','2004-01-01 00:00:00','0.3'
union all select 'd','c','2004-01-01 00:00:00','0.5'
union all select 'd','c','2004-01-01 00:00:00','0.6'
union all select 'd','a','2005-01-01 00:00:00','0.1'
union all select 'd','c','2005-01-01 00:00:00','014'
union all select 'd','b','2005-01-01 00:00:00','0.4'
union all select 'd','e','2005-01-01 00:00:00','0.6'

select * from #tb where
aid in (select aid from
(select aid,date1 ,count(aid) aa from #tb
where date1 in (select bb from (select aid,max(date1) bb from #tb group by aid) b) group by aid,date1 having count(aid)=4) a )
and date1 in (select bb from (select aid,max(date1) bb from #tb group by aid) b )
回复
tian790317 2005-11-13
select * from test where aid in (select aid from (select aid,date1 ,count(aid) aa from test where date1 in (select bb from (select aid,max(date1) bb from test group by aid) b) group by aid,date1 having count(aid)=4) a ) and date1 in
(select bb from (select aid,max(date1) bb from test group by aid) b )
回复
tian790317 2005-11-13
我自己已经写出来了:
select * from test where aid in (select aid from (select aid,date1 ,count(aid) aa from test group by aid,date1 having count(aid)=4) a ) and date1 in
(select bb from (select aid,max(date1) bb from test group by aid) b )
回复
tian790317 2005-11-13
楼上的错了,不是我说的意思,仔细看看结果就知道题目的意思了
回复
Andy__Huang 2005-11-13

create table #tb(aid varchar(1),bid varchar(1), date1 datetime, exchage numeric(8,1))
Insert into #tb (aid, bid, date1,exchage)
select 'a','b','2005-01-01 00:00:00','0.1'
union all select 'a','c','2005-01-01 00:00:00','0.2'
union all select 'a','d','2005-01-01 00:00:00','0.3'
union all select 'a','e','2005-01-01 00:00:00','0.5'
union all select 'b','a','2005-03-01 00:00:00','0.5'
union all select 'b','c','2004-03-01 00:00:00','0.6'
union all select 'b','d','2005-03-01 00:00:00','0.8'
union all select 'c','a','2005-04-05 00:00:00','0.1'
union all select 'c','b','2005-04-05 00:00:00','0.5'
union all select 'c','d','2005-04-05 00:00:00','0.2'
union all select 'c','e','2005-04-05 00:00:00','0.2'
union all select 'e','a','2004-01-02 00:00:00','0.1'
union all select 'e','h','2004-02-01 00:00:00','0.5'
union all select 'a','b','2005-08-01 00:00:00','0.1'
union all select 'a','c','2005-08-01 00:00:00','0.2'
union all select 'a','d','2005-08-01 00:00:00','0.3'
union all select 'a','e','2005-08-01 00:00:00','0.5'
union all select 'c','b','2005-07-01 00:00:00','0.1'
union all select 'c','a','2005-07-01 00:00:00','01'
union all select 'c','d','2005-07-01 00:00:00','0.4'
union all select 'c','e','2005-07-01 00:00:00','0.4'
union all select 'd','a','2005-08-01 00:00:00','0.5'
union all select 'd','b','2005-08-01 00:00:00','0.8'
union all select 'd','c','2005-08-01 00:00:00','0.4'
union all select 'd','e','2005-08-01 00:00:00','0.5'
union all select 'd','a','2004-01-01 00:00:00','0.3'
union all select 'd','c','2004-01-01 00:00:00','0.5'
union all select 'd','c','2004-01-01 00:00:00','0.6'
union all select 'd','a','2005-01-01 00:00:00','0.1'
union all select 'd','c','2005-01-01 00:00:00','014'
union all select 'd','b','2005-01-01 00:00:00','0.4'
union all select 'd','e','2005-01-01 00:00:00','0.6'

select * from #tb

select a.*
from #tb a
inner join (select aid,bid,date1=max(date1) from #tb group by aid,bid)b
on a.aid=b.aid and a.bid=b.bid and a.date1=b.date1
order by 1,2

aid bid date1 exchage
a b 2005-08-01 00:00:00.000 .1
a c 2005-08-01 00:00:00.000 .2
a d 2005-08-01 00:00:00.000 .3
a e 2005-08-01 00:00:00.000 .5
b a 2005-03-01 00:00:00.000 .5
b c 2004-03-01 00:00:00.000 .6
b d 2005-03-01 00:00:00.000 .8
c a 2005-07-01 00:00:00.000 1.0
c b 2005-07-01 00:00:00.000 .1
c d 2005-07-01 00:00:00.000 .4
c e 2005-07-01 00:00:00.000 .4
d a 2005-08-01 00:00:00.000 .5
d b 2005-08-01 00:00:00.000 .8
d c 2005-08-01 00:00:00.000 .4
d e 2005-08-01 00:00:00.000 .5
e a 2004-01-02 00:00:00.000 .1
e h 2004-02-01 00:00:00.000 .5



回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2005-11-13 03:07
社区公告
暂无公告