-
本版专家分：191结帖率 99.43% -
"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"![]()
-
本版专家分：67868
-
- 铜牌 2013年9月 总版技术专家分月排行榜第三
-
-
红花
2013年7月 MS-SQL Server大版内专家分月排行榜第一
2009年3月 Oracle大版内专家分月排行榜第一
2009年2月 Oracle大版内专家分月排行榜第一
-
-
黄花
2013年9月 MS-SQL Server大版内专家分月排行榜第二
2013年8月 MS-SQL Server大版内专家分月排行榜第二
2009年4月 Oracle大版内专家分月排行榜第二
2009年1月 Oracle大版内专家分月排行榜第二
2004年10月 MS-SQL Server大版内专家分月排行榜第二
-
-
蓝花
2013年10月 MS-SQL Server大版内专家分月排行榜第三
2008年12月 Oracle大版内专家分月排行榜第三
2004年11月 MS-SQL Server大版内专家分月排行榜第三
-
-
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
-
本版专家分：191
-
楼上的错了,不是我说的意思,仔细看看结果就知道题目的意思了
-
本版专家分：191
-
我自己已经写出来了:
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 )
-
本版专家分：191
-
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 )
-
本版专家分：10449 -
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 )![]()
