下面语句怎么写?谢谢!

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"
...全文
169 5 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
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



什么？mybatis-plus的多表查询，你还在写sql语句？！【CRUD2】多表联查的三种方式
⚠⚠⚠自信攻城狮小名又来攻城啦⚠⚠⚠ 文章目录公用部分：1. VO2. PracticeTeacherService一、SQL1. PracticeTeacherMapper.xml2. PracticeTeacherMapper.java3. PracticeTeacherServiceImpl.java4. 结果：二、链式调用 lambda 式，lambdaQuery()+foreach1. PracticeTeacherServiceImpl.java2. 结果三、stream1. Practice
Spring ORM+Hibernate？Out！换 Spring Data JPA 吧！
Spring Data JPA 的目标是通过让一些必须的工作变得更简单，来极大的提高数据访问层的实现效率。通常我们只需要姐访问方法，而 Spring 给你提供自动实现。！ 在一切开始之前，先举个简单的例子，以提高大家的兴致！ 如果一张表user有三个字段，id、name和age，要查找指定姓氏在某年龄以上的user，在传统的Spring+Hibernate中，dao层我们是这样写的...
java中update怎么写,update语句
手机评站网今天精心准备的是《update语句》，下面是详解！SQL的update语句怎么写？当a列的值为f时,对x表的b，c，d，e列同时更新数据...当a列的值为f时,对x表的b，c，d，e列同时更新数据UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值，update语句的写法：1、UPDATE table_name2、SET column1=value1,column...
在finereport上误删了写好的sql语句，怎么才能恢复？
在finereport上误删了写好的sql语句，怎么才能恢复？有没有大佬帮帮我，太难了！！！ 就是在下面的数据集里面写了一些sql语句被我不小心按了ctrl加z，要怎么恢复？谢谢大佬！！！
c语言整蛊朋友小程序
c语言整蛊朋友小程序 文章目录c语言整蛊朋友小程序前言一、我是猪关机程序1.dos命令（强制关机）2.源码二、无限弹窗1.vbs2.创建text.vbs，并打开2.想要通过程序指定创建位置怎么办？3.源码总结 前言 感觉学了c语言后仍然一无是处？！！想要整蛊一下朋友仍然不会？！！ 别慌，看完这篇文章，你就会了。 下面给大家分享两个基础的整蛊小程序 1.我是猪关机程序 2.无限弹窗程序 一、我是猪关机程序 效果:运行程序后电脑在60s后关机，如果输入“我是猪”则取消关机；如果输入“你是猪”则立即关
疑难问题

22,302

社区成员

121,734

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

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

+ 用AI写文章