【已解决】求个SQL查询(分组取极值)
-
- 帖子: 1010
- 注册时间: 2006-09-29 5:11
【已解决】求个SQL查询(分组取极值)
不知道这样说是不是清楚简单一点,比如说我把每天的温度、湿度、风力。。。都记录下来,然后我要一次取出最后两次的温度湿度风力记录。
如果你已经清楚我说的,以下跳过。
table (id, name,val,time)
取出name对应的val记录,但是只要每个name的最后2次记录。
eg.:
(1, 'a',''v1',1111111111)
(2, 'a','v2',1111111112)
(3, 'b','v3',1111111113)
(4, 'a','v4',1111111116)
(5, 'a','v5',1111111115)
(6, 'a','v6',1111111114)
(7, 'b','v7',1111111117)
(8, 'b','v8',1111111118)
............
须取出的就是第4,5(“a”的最后2条),和7,8("b"的最后2条).
如果你已经清楚我说的,以下跳过。
table (id, name,val,time)
取出name对应的val记录,但是只要每个name的最后2次记录。
eg.:
(1, 'a',''v1',1111111111)
(2, 'a','v2',1111111112)
(3, 'b','v3',1111111113)
(4, 'a','v4',1111111116)
(5, 'a','v5',1111111115)
(6, 'a','v6',1111111114)
(7, 'b','v7',1111111117)
(8, 'b','v8',1111111118)
............
须取出的就是第4,5(“a”的最后2条),和7,8("b"的最后2条).
上次由 sanz 在 2014-10-16 15:22,总共编辑 1 次。
Dell D620 -> Asus U44SG
Dapper->Edgy->Feisty->Gutsy->Hardy->Karmic->Lucid -> Oneric -> Precise->Trusty
Python+JS
Dapper->Edgy->Feisty->Gutsy->Hardy->Karmic->Lucid -> Oneric -> Precise->Trusty
Python+JS
-
- 论坛版主
- 帖子: 5215
- 注册时间: 2007-01-14 16:23
Re: 求个SQL查询
如果只是选择表的最后两行,是微软的那个好像有个LAST参数可以用,MYSQL不记得怎样选择最后的了,你可以先将A和B分开,然后再选择最后两条,反正MYSQL自带的那个帮助文件里面没找到
#include <stdio.h>
void main()
{
double world;
unsigned letter;
short stay;
long memories;
printf("I miss you.\n");
}
void main()
{
double world;
unsigned letter;
short stay;
long memories;
printf("I miss you.\n");
}
-
- 帖子: 38
- 注册时间: 2007-10-17 10:12
Re: 求个SQL查询
如果是oracle的话,oracle有三个分组排序的函数,row_number() dense_rank() rank()
select * from (
select t.*, row_number() over(partition by name order by time desc) as xuhao,
from table t)
where xuhao <= 2
按name分组,在每个组里按time倒序排列生成序号
再在外面套一层查询,查序号小于等于2的
select * from (
select t.*, row_number() over(partition by name order by time desc) as xuhao,
from table t)
where xuhao <= 2
按name分组,在每个组里按time倒序排列生成序号
再在外面套一层查询,查序号小于等于2的
-
- 帖子: 1010
- 注册时间: 2006-09-29 5:11
Re: 求个SQL查询
Mysql无解?
Dell D620 -> Asus U44SG
Dapper->Edgy->Feisty->Gutsy->Hardy->Karmic->Lucid -> Oneric -> Precise->Trusty
Python+JS
Dapper->Edgy->Feisty->Gutsy->Hardy->Karmic->Lucid -> Oneric -> Precise->Trusty
Python+JS
-
- 论坛版主
- 帖子: 5215
- 注册时间: 2007-01-14 16:23
Re: 求个SQL查询
你考虑下重新设计表的结构,把这些数据分开放在不一样的表里,或者有别的解决方法
#include <stdio.h>
void main()
{
double world;
unsigned letter;
short stay;
long memories;
printf("I miss you.\n");
}
void main()
{
double world;
unsigned letter;
short stay;
long memories;
printf("I miss you.\n");
}
- 妞,抱一个
- 帖子: 108
- 注册时间: 2011-11-30 12:52
Re: 求个SQL查询
MySQL:
代码: 全选
select id, name, val, time from 表 where id = '4' or id = '5' or id = '7' or id = '8';
'小生不才'@'新手一枚'
-
- 帖子: 1010
- 注册时间: 2006-09-29 5:11
Re: 求个SQL查询
这是个纯技术贴诶。搞什么无厘头啊?妞,抱一个 写了:MySQL:代码: 全选
select id, name, val, time from 表 where id = '4' or id = '5' or id = '7' or id = '8';
Dell D620 -> Asus U44SG
Dapper->Edgy->Feisty->Gutsy->Hardy->Karmic->Lucid -> Oneric -> Precise->Trusty
Python+JS
Dapper->Edgy->Feisty->Gutsy->Hardy->Karmic->Lucid -> Oneric -> Precise->Trusty
Python+JS
- power255
- 帖子: 12
- 注册时间: 2009-04-01 9:32
-
- 帖子: 356
- 注册时间: 2007-02-15 6:26
Re: 求个SQL查询
根据实际运用的需要,来设计合理的表机构:
(select * from tab where name='a' order by time desc limit 2)
union
(select * from tab where name='b' order by time desc limit 2)
=================
http://sidu.sf.net
(select * from tab where name='a' order by time desc limit 2)
union
(select * from tab where name='b' order by time desc limit 2)
=================
http://sidu.sf.net
- oneleaf
- 论坛管理员
- 帖子: 10441
- 注册时间: 2005-03-27 0:06
- 系统: Ubuntu 12.04
Re: 求个SQL查询
不确定是否可以执行,不过思路如下:
select * from table where id in (select max(id) from table group by name)
union
select * from table where id in (select max(id) from table where id not in (select max(id) from table group by name) group by name)
如果使用临时表就比较简单
select max(id) as id into temptable from table group by name;
insert into temptable (id) select max(id) from table where id not in (select id from temptable) group by name;
select * from table where id in (select id in temptable);
select * from table where id in (select max(id) from table group by name)
union
select * from table where id in (select max(id) from table where id not in (select max(id) from table group by name) group by name)
如果使用临时表就比较简单
select max(id) as id into temptable from table group by name;
insert into temptable (id) select max(id) from table where id not in (select id from temptable) group by name;
select * from table where id in (select id in temptable);
- chenjiancong
- 帖子: 130
- 注册时间: 2007-05-17 17:49
Re: 求个SQL查询
oneleaf 写了:不确定是否可以执行,不过思路如下:
select * from table where id in (select max(id) from table group by name)
union
select * from table where id in (select max(id) from table where id not in (select max(id) from table group by name) group by name)
如果使用临时表就比较简单
select max(id) as id into temptable from table group by name;
insert into temptable (id) select max(id) from table where id not in (select id from temptable) group by name;
select * from table where id in (select id in temptable);
我受楼上这位大哥的启示,弄了个
select * from table_name where id in (select max(id) from table_name) union select * from table_name where id in (select max(id)-1 from table_name);
-
- 帖子: 1010
- 注册时间: 2006-09-29 5:11
Re: 求个SQL查询
几年过去了。遇到过几次类似问题,每次解决了都后来都忘了。索性在这里记下。省得下次再忘记。
select distinct table_name.* from (select val, max(gettime) as gettime from table_name group by table_name.name) as tmp_tb left join table_name on tmp_tb.gettime=table_name.gettime
个人认为这是最佳方案。好像是在stackoverflow上问来的。
select distinct table_name.* from (select val, max(gettime) as gettime from table_name group by table_name.name) as tmp_tb left join table_name on tmp_tb.gettime=table_name.gettime
个人认为这是最佳方案。好像是在stackoverflow上问来的。
Dell D620 -> Asus U44SG
Dapper->Edgy->Feisty->Gutsy->Hardy->Karmic->Lucid -> Oneric -> Precise->Trusty
Python+JS
Dapper->Edgy->Feisty->Gutsy->Hardy->Karmic->Lucid -> Oneric -> Precise->Trusty
Python+JS