下面语句怎么写?谢谢!

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



 回复 赞
相关推荐
入门学习Linux常用必会60个命令实例详解doc/txt 本资源来自网络，如有纰漏还请告知，如觉得还不错，请留言告知后来人，谢谢！！！！！ 入门学习Linux常用必会60个命令实例详解 Linux必学的60个命令 Linux提供了大量的命令，利用它可以有效地完成大量的工作，如...
什么？mybatis-plus的多表查询，你还在写sql语句？！【CRUD2】多表联查的三种方式 ⚠⚠⚠自信攻城狮小名又来攻城啦⚠⚠⚠ 文章目录 公用部分： 1.... 如果觉得小名的文章帮助到了您，请关注小名的新专栏 MyBatis-Plus【CRUD】，支持一下小名，给小名的文章点赞、评论✍、收藏谢谢大家啦~♥♥♥
Spring ORM+Hibernate？Out！换 Spring Data JPA 吧！ Spring Data JPA 的目标是通过让一些必须的工作变得更简单，来极大的提高数据... 如果一张表user有三个字段，id、name和age，要查找指定姓氏在某年龄以上的user，在传统的Spring+Hibernate中，dao层我们是这样写的...
switch case 语句要注意!!! c语言中的switch case 语句相比大家也是非常的清楚的。
C语言图形化界面——含图形、按钮、鼠标、进度条等部件制作（带详细代码、讲解及注释） \qquadSleep()是毫秒级延迟，当然界面变亮时间不一定是准确的15ms×255/5=0.765s，因为其他语句还需要执行时间。 \qquadclosegraph()：关闭绘图界面。注意，如果初始化了绘图界面但没有在主函数结束前关闭它，可能...
C语言逆序输出整数 下面我给出一个最简单的例子。 #include <stdio.h> int main() { int x; int i; int sum=0; printf("请输入一个整数："); scanf("%d",&x); while(x!=0) ...
Python 脚本编写 该块中的代码是 Python 在 try 语句中 首先运行的代码 。 except ：如果 Python 在运行 try 块时遇到异常，它将跳到 处理该异常的 except 块 。 else ：如果 Python 在运行 try 块时没有遇到异常 ，它将在运行 ...
收藏！一篇教会你写90%的shell脚本！ 还不会写shell脚本？这篇文章教会你写90%的shell脚本！
程序员简历应该怎么写？ 当然你也可以使用其他文档工具来写，但务必参考下面几条关于格式和排版的建议，你也可以用 Markdown 来写，这样子会更佳~ 导出为 PDF 格式，考虑不同平台下的阅读体验 可阅读性强，美观大方，支持跨平台，版本...
For语句在python中的作用如何？ 下面就让我们一步一步地来揭开for语句的面纱。 一：for语句 1、作用： 用来遍历（遍历指每个数都运行一次）可迭代对象的数据元素（可迭代对象是指能依次获取数据元素的对象，程序执行多少次，由可迭代对象决定）。可...
MySQL创建数据库 创建数据库是指在数据库系统中划分一块空间，用来存储相应的数据。这是进行表操作的基础，也是进行数据库管理的基础。 一、通过SQL语句创建 　 ...　在MySQL中，创建数据库必须通过...　后期会更新此部分内容，谢谢！
PHP购物网站（含购物车、全部源码、数据库设计表及其源码） 因为不会PHP，为了写这个作业而新学的。 做这个网站用了两周时间，在此把这个小项目做一个总结。 功能： DONE. 用户权限管理。包括管理员和普通用户。管理员有所有权限，包括更新网站状态 登录的其他值为用户名...
Python 程序设计(第二版)董付国_清华大学出版社_习题答案与分析【针对8.4及其之前的】 更多精彩内容：（没有设置公众号获得，麻烦动动小手~谢谢） CSDN下载：Python编程无师自通电子书,[美]科里·奥尔索夫（Cory Althoff）-文档类-CSDN下载 百度云：链接：...bt08 点击跳转：Python 程序设计(第二版)董付国...
C语言重要知识点总结【6】：C语言9种控制语句（详解） 9种控制语句【详解】表格+代码实例+详细概念
2020华为面经春招Java软件开发岗华为南京研究所公共开发部(已发offer) 华为一般是三轮面试，前两轮为技术面试，第三轮...下面我将按照第一轮面试到第三轮面试的顺序，依次谈一下每轮面试的细节以及需要注意的地方；（提示：每轮面试是平级的，并不是越往后问的东西越难！） 第一轮：技...
java中switch()case语句的嵌套示例，可在下面的case各级嵌套中继续添加语句块（根据业务要求添加）。 package switcandcase;  import java.util.Scanner; public class switchduochongjiegou {  public static void main(String[] args) { ... System.out.println("谢谢您的使用！");  }      } }
[GO语言基础] 五.顺序控制语句和条件控制语句（if、else、switch） 这篇文章将详细讲解顺序控制语句和条件控制语句。 这系列文章入门部分将参考“尚硅谷”韩顺平老师的视频和书籍《GO高级编程》，详见参考文献，并结合作者多年的编程经验进行学习和丰富，且看且珍惜吧！后续会结合...
java中的选择（包括条件表达式）和循环语句（包括foreach循环语句） 在我们敲代码的时候经常会遇到要使用选择语句和循环语句的时候，因为合理的使用这些语句能有效的提高我们代码的质量和性能。 接下来我们说说这些语句！ 1、选择语句 先来说说java的关系操作符： java操作符 数学...
Python循环语句 同时也谢谢点赞的朋友们。 Python有两种循环模式，我们先讲遍历循环。 先看一个案例： for i in range(1,10): print(i,end = '') 打印出来些什么？是不是123456789？大家仔细看这个代码，是不是有我们昨天说的：...
libiscsi-devel-1.18.0-8.module_el8.5.0+746+bbd5d70c.x86_64.rpm 离线安装包，亲测可用
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

12.1w+

社区内容

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