数据库的查询（给定一个表数据，生成新表中数据），望大侠们指导

Question

要求是：给定DAY,MONTH两张表，及DAY的数据，生成MONTH的数据
，用SQL直接完成，但需要健壮性和速度
注意：month的OPEN=该月第一天的OPEN
       HIGH=该月的MAX(HIGH)
       LOW=该月的MIN(LOW)
       CLOSE=该月最后一天的CLOSE
TRADEVOL,TRADEMONEY=该月所有TRADEVOL,TRADEMONEY之和
NUMRISE,NUMDECLINE分别为默认值注意PRICEMONTH的格式请自动调整为年-月格式  CREATE TABLE `day` (
  `stock_id` char(8) NOT NULL DEFAULT '',
  `pricedate` date NOT NULL DEFAULT '0000-00-00',
  `open` float DEFAULT NULL,
  `high` float DEFAULT NULL,
  `low` float DEFAULT NULL,
  `close` float DEFAULT NULL,
  `tradevol` float DEFAULT NULL,
  `trademoney` float DEFAULT NULL,
  `numrise` smallint(6) DEFAULT '-1',
  `numdecline` smallint(6) DEFAULT '-1',
  PRIMARY KEY (`stock_id`,`pricedate`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 | CREATE TABLE `month` (
  `stock_id` char(8) NOT NULL DEFAULT '',
  `pricemonth` date NOT NULL DEFAULT '0000-00-00',
  `open` float DEFAULT NULL,
  `high` float DEFAULT NULL,
  `low` float DEFAULT NULL,
  `close` float DEFAULT NULL,
  `tradevol` float DEFAULT NULL,
  `trademoney` float DEFAULT NULL,
  `numrise` smallint(6) DEFAULT '-1',
  `numdecline` smallint(6) DEFAULT '-1',
  PRIMARY KEY (`stock_id`,`pricemonth`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
小弟我接触数据库知识三天了，对于此我的想法是先建立索引再查询。
到目前为止我知道 怎么用day表查询 给定日期 的每个月的第一天和最后一天，基本的sum和max也会用，等等……
但是还是不知道怎么将那些基础的语法串联起来，然后完成这个要求。
请各位大侠给予指导，感激不尽。

Answer 1

没看懂你想做什么。   建议你列出你的表结构，并提供测试数据以及基于这些测试数据的所对应正确结果。
   参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
   
   1. 你的 create table xxx .. 语句
   2. 你的 insert into xxx ... 语句
   3. 结果是什么样，（并给以简单的算法描述）
   4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
   
   这样想帮你的人可以直接搭建和你相同的环境，并在给出方案前进行测试，避免文字描述理解上的误差。   

Answer 2

如果这样，建议还是再花一点时间掌握一下SQL语句的基础。推荐教材《数据库系统概论（第四版）》 王珊 萨师煊   高等教育出版社 (掌握基础知识和概念) MYSQL的官方手册。（方便以后查找，避免类似于考试的时候，给你本政治书也不知道答案在第几章，第几页）MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html

Answer 3

感谢大佬的悉心回复，以前曾经学过王珊的数据库概论，这两天也把mysql的官方手册中的第3章教程认真读了一遍。简单的查询已经比较熟悉。呵呵，一上来就做上面给的任务，我觉得是难度跨度挺大的。待会我会再把问题整理一下的。：）

Answer 4

贴建表及插入记录的SQL，及要求结果出来看看

Answer 5

CREATE TABLE day (
 stock_id char(8) NOT NULL DEFAULT '',
 pricedate date NOT NULL DEFAULT '0000-00-00',
 open float DEFAULT NULL,
 high float DEFAULT NULL,
 tradevol float DEFAULT NULL,
 trademoney float DEFAULT NULL,
  PRIMARY KEY (stock_id,pricedate)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 CREATE TABLE month (
 stock_id char(8) NOT NULL DEFAULT '',
 pricemonth date NOT NULL DEFAULT '0000-00-00',
 open float DEFAULT NULL,
 high float DEFAULT NULL,
 tradevol float DEFAULT NULL,
  PRIMARY KEY (stock_id,pricemonth)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 
day表数据如下：
stock_id pricedate open high tradevol
2008053378 2008-01-01 10 10 111
2008053379 2008-01-01 20 30 222
2008053378 2008-01-02 12 40 111
2008053379 2008-01-03 33 10 1112008053378 2008-02-01 16 20 111
2008053378 2008-02-02 12 35 222
由day表生成month表，结果生成要求：month表中的  open=该月第一天的open，high=该月的max(high)，
tradevol=该月所有tradevol

想要得到的效果如下：
在month表中：
stock_id pricemonth open high tradevol
2008053378 2008-01 10 40 222
2008053379 2008-01 20 30 3332008053378 2008-01 16 35 333
在下使用的是mysql-5.5.11-win32，求指导~~

Answer 6

不好意思，数据修改为insert语句如下：insert into day (stock_id,pricedate,open,high,tradevol) values(2008053378,'2008-01-01',10,10,111);
insert into day (stock_id,pricedate,open,high,tradevol) values(2008053379,'2008-01-02',20,30,222);
insert into day (stock_id,pricedate,open,high,tradevol) values(2008053378,'2008-01-02',12,40,111);
insert into day (stock_id,pricedate,open,high,tradevol) values(2008053379,'2008-01-03',20,30,111);insert into day (stock_id,pricedate,open,high,tradevol) values(2008053378,'2008-02-01',16,20,111);
insert into day (stock_id,pricedate,open,high,tradevol) values(2008053378,'2008-02-01',12,35,222);

Answer 7

SELECT *,
(SELECT MAX(high) FROM `day` WHERE a1.stock_id=stock_id AND 
DATE_FORMAT(pricedate,'%Y-%m')=DATE_FORMAT(a1.pricedate,'%Y-%m')),
(SELECT SUM(tradevol) FROM `day` WHERE a1.stock_id=stock_id AND 
DATE_FORMAT(pricedate,'%Y-%m')=DATE_FORMAT(a1.pricedate,'%Y-%m')) FROM `day` a1 WHERE DAY(a1.pricedate)=1

Answer 8

SELECT *,
(SELECT MAX(high) FROM `day` WHERE a2.stock_id=stock_id AND 
DATE_FORMAT(pricedate,'%Y-%m')=DATE_FORMAT(a2.mday,'%Y-%m')),
(SELECT SUM(tradevol) FROM `day` WHERE a2.stock_id=stock_id AND 
DATE_FORMAT(pricedate,'%Y-%m')=DATE_FORMAT(a2.mday,'%Y-%m')) FROM 
 (SELECT a1.stock_id,DATE_FORMAT(a1.pricedate,'%Y-%m'),MIN(pricedate) AS mday FROM `DAY` a1 GROUP BY a1.stock_id,DATE_FORMAT(a1.pricedate,'%Y-%m')) a2

Answer 9

open=该月第一天的openinsert into day (stock_id,pricedate,open,high,tradevol) values(2008053378,'2008-02-01',16,20,111);
insert into day (stock_id,pricedate,open,high,tradevol) values(2008053378,'2008-02-01',12,35,222);
同为第1天，取什么OPEN

Answer 10

实在不好意思，由于自己的疏忽大意，造成insert语句，数据的错误
现在修正如下：insert into day (stock_id,pricedate,open,high,tradevol) values('2008053378','2008-01-01',10,10,111);
insert into day (stock_id,pricedate,open,high,tradevol) values('2008053379','2008-01-01',20,30,222);
insert into day (stock_id,pricedate,open,high,tradevol) values('2008053378','2008-01-02',12,40,111);
insert into day (stock_id,pricedate,open,high,tradevol) values('2008053379','2008-01-03',20,30,111);insert into day (stock_id,pricedate,open,high,tradevol) values('2008053378','2008-02-01',16,20,111);
insert into day (stock_id,pricedate,open,high,tradevol) values('2008053378','2008-02-02',12,35,222);
版主教诲得很好，我会吸取这个教训的。谢谢两位高手的指导

Answer 11

呜呜，泪奔啊……
我真的真的错了，真是对不起，我是想当然就这样了，没有经过测试就贴上来了。insert into day (stock_id,pricedate,open,high,tradevol) values('2008053378','2008-01-01',10,10,111);
insert into day (stock_id,pricedate,open,high,tradevol) values('2008053379','2008-01-01',20,30,222);
insert into day (stock_id,pricedate,open,high,tradevol) values('2008053378','2008-01-02',12,40,111);
insert into day (stock_id,pricedate,open,high,tradevol) values('2008053379','2008-01-03',20,30,111);insert into day (stock_id,pricedate,open,high,tradevol) values('2008053378','2008-02-01',16,20,111);
insert into day (stock_id,pricedate,open,high,tradevol) values('2008053378','2008-02-02',12,35,222);

Answer 12

~~~~(>_<)~~~~ ，我会记住这个教训的，实在抱歉。让版主费心，谢谢您的耐心

Answer 13

OMG，怎么得了，我刚才又把错的贴上去了，下面是正确的，自己测试过了。insert into day (stock_id,pricedate,open,high,tradevol) values('08053378','2008-01-01',10,10,111);
insert into day (stock_id,pricedate,open,high,tradevol) values('08053379','2008-01-01',20,30,222);
insert into day (stock_id,pricedate,open,high,tradevol) values('08053378','2008-01-02',12,40,111);
insert into day (stock_id,pricedate,open,high,tradevol) values('08053379','2008-01-03',20,30,111);insert into day (stock_id,pricedate,open,high,tradevol) values('08053378','2008-02-01',16,20,111);
insert into day (stock_id,pricedate,open,high,tradevol) values('08053378','2008-02-02',12,35,222);

Answer 14

mysql> select * from day;
+----------+------------+------+------+----------+------------+
| stock_id | pricedate  | open | high | tradevol | trademoney |
+----------+------------+------+------+----------+------------+
| 08053378 | 2008-01-01 |   10 |   10 |      111 |       NULL |
| 08053378 | 2008-01-02 |   12 |   40 |      111 |       NULL |
| 08053378 | 2008-02-01 |   16 |   20 |      111 |       NULL |
| 08053378 | 2008-02-02 |   12 |   35 |      222 |       NULL |
| 08053379 | 2008-01-01 |   20 |   30 |      222 |       NULL |
| 08053379 | 2008-01-03 |   20 |   30 |      111 |       NULL |
+----------+------------+------+------+----------+------------+
6 rows in set (0.00 sec)mysql> insert into `month`
    -> select stock_id,pricedate-interval (day(pricedate)-1) day ,
    ->  (select open from `day` where stock_id=t.stock_id and DATE_FORMAT(pricedate,'%Y%m')=DATE_FORMAT(t.pricedate,'%Y%m') order by pricedate limit 1),
    ->  max(high),
    ->  sum(tradevol)
    -> from `day` t
    -> group by stock_id,pricedate-interval (day(pricedate)-1) day;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0mysql> select * from `month`;
+----------+------------+------+------+----------+
| stock_id | pricemonth | open | high | tradevol |
+----------+------------+------+------+----------+
| 08053378 | 2008-01-01 |   10 |   40 |      222 |
| 08053378 | 2008-02-01 |   16 |   35 |      333 |
| 08053379 | 2008-01-01 |   20 |   30 |      333 |
+----------+------------+------+------+----------+
3 rows in set (0.00 sec)mysql>

Answer 15

实在抱歉，15提供的数据有错误。我把数据更在在18楼了。
我还想问一个问题，如果day表中的数据量很大，如果要提高查询效率，是不是应该这样建立索引：CREATE UNIQUE INDEX dayindex
ON day(stock_id ASC,pricedate ASC);

Answer 16

用17楼的数据，8楼的代码已经可以了
上面写错了CREATE UNIQUE INDEX dayindex
ON day(stock_id ASC,pricedate ASC);
可以

Answer 17

对于open的查询，我觉得应该写成这样更加健壮，
避免了：day表中没有当月第一天open数据时，将第二天的open作为当月第一天的openselect open from day where stock_id=t.stock_id and 
pricedate=(t.pricedate-interval (day(t.pricedate)-1) day)
对吗？