数据库结构
create table quote(code char(8),edate date,open char(8),close char(8),high char(8),low char(8),volum char(16),adjust char(8));
我的查询语句
数据导入完成后,希望找出每个code,在edate时间内,最小的low值
也就是
select code,min(low) from quote group by code;
每个min(low)对应的edate,请问如何写sql语句?
我这样写不行 select code,edate,min(low) from quote group by code;
这样做得到的 edate不是每个code的low取最小值时,对应的edate日期,发现令人不解的是所有edate输出都是整个edate的最后一天??
create table quote(code char(8),edate date,open char(8),close char(8),high char(8),low char(8),volum char(16),adjust char(8));
我的查询语句
数据导入完成后,希望找出每个code,在edate时间内,最小的low值
也就是
select code,min(low) from quote group by code;
每个min(low)对应的edate,请问如何写sql语句?
我这样写不行 select code,edate,min(low) from quote group by code;
这样做得到的 edate不是每个code的low取最小值时,对应的edate日期,发现令人不解的是所有edate输出都是整个edate的最后一天??
[征集]分组取最大N条记录方法征集,及散分....
select * from (select * from t2 order by gid,col2 desc) t group by gid;
这里的t指什么呢?
期望结果
1) N=1 取GID每组 COL2最大的记录
SELECT a.id,a.gid,a.col1,a.col2
FROM t2 as a,t2 as b
where a.gid=b.gid AND a.col2 <=b.col2
GROUP BY a.id,a.gid,a.col1,a.col2
having a.col2>=max(b.col2)
ORDER BY a.gid,a.col2 desc2) N=3 取GID每组 COL2最大的3条记录
SELECT a.id,a.gid,a.col1,a.col2
FROM t2 a,t2 b
where a.gid=b.gid AND a.col2 <=b.col2
GROUP BY a.id,a.gid,a.col1,a.col2
HAVING COUNT(b.id) <=3
ORDER BY a.gid,a.col2 desc有看不懂的地方请教,
SELECT a.id,a.gid,a.col1,a.col2
FROM t2 a,t2 b
这句话是什么意思呢?
这句话里面的t,如何理解?
2.
SELECT a.id,a.gid,a.col1,a.col2
FROM t2 as a,t2 as b
它要干什么呢?t2当成a, t2又当成b?
select * from (select * from t2 order by gid,col2 desc) t group by gid;
就可以得到需要的结果呢,想不通?
MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html
为何仅仅输出5个结果呢?
不是标准的SQL语句
select * from t2 group by gid;
得到的结果就是
运行 select * from t2 的前5个数据行?
为什么?
这句话是
select * from t2 group by gid limit 5;缩写形式??
如何输出所有的结果呢?
为何
select * from t2 group by gid limit 20;
也仅仅输出5个数据行呢??
id int primary key,
gid char,
col1 int,
col2 int
) engine=myisam;insert into t2 values
(1,'A',31,6),
(2,'B',25,83),
(3,'C',76,21),
(4,'D',63,56),
(5,'E',3,17),
(6,'A',29,97),
(7,'B',88,63),
(8,'C',16,22),
(9,'D',25,43),
(10,'E',45,28),
(11,'A',2,78),
(12,'B',30,79),
(13,'C',96,73),
(14,'D',37,40),
(15,'E',14,86),
(16,'A',32,67),
(17,'B',84,38),
(18,'C',27,9),
(19,'D',31,21),
(20,'E',80,63),
(21,'A',89,9),
(22,'B',15,22),
(23,'C',46,84),
(24,'D',54,79),
(25,'E',85,64),
(26,'A',87,13),
(27,'B',40,45),
(28,'C',34,90),
(29,'D',63,8),
(30,'E',66,40),
(31,'A',83,49),
(32,'B',4,90),
(33,'C',81,7),
(34,'D',11,12),
(35,'E',85,10),
(36,'A',39,75),
(37,'B',22,39),
(38,'C',76,67),
(39,'D',20,11),
(40,'E',81,36);
t2是这样建立的,请你们看看,select * from t2 group by gid ;它的输出结果我想不通,请帮助我一下
select * from t2 group by gid
不是标准的SQL语句
你要得到什么结果
select gid from t2 group by gid ?
select * from t2 group by gid;
+----+------+------+------+
| id | gid | col1 | col2 |
+----+------+------+------+
| 1 | A | 31 | 6 |
| 2 | B | 25 | 83 |
| 3 | C | 76 | 21 |
| 4 | D | 63 | 56 |
| 5 | E | 3 | 17 |
+----+------+------+------+
5 rows in set (0.00 sec)
为何不输出下面:
| id | gid | col1 | col2 |
+----+------+------+------+
| 6 | A | 29 | 97 |
| 7 | B | 88 | 63 |
| 8 | C | 16 | 22 |
| 9 | D | 25 | 43 |
| 10 | E | 45 | 28 |
+----+------+------+------+