mysql与子查询连接

Question

这是我的模式：

mysql> describe stocks;
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| id        | int(11)     | NO   | PRI | NULL    | auto_increment |
| symbol    | varchar(32) | NO   |     | NULL    |                |
| date      | datetime    | NO   |     | NULL    |                |
| value     | float(10,3) | NO   |     | NULL    |                |
| contracts | int(8)      | NO   |     | NULL    |                |
| open      | float(10,3) | NO   |     | NULL    |                |
| close     | float(10,3) | NO   |     | NULL    |                |
| high      | float(10,3) | NO   |     | NULL    |                |
| low       | float(10,3) | NO   |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
9 rows in set (0.03 sec)

我增加了列的开和低，我想填充表中的数据。

这些值打开/关闭将引用每一天。(因此，每一天的相对max/min id应该给出正确的值)。因此，我的第一个洞察力是获取日期列表，然后离开与表连接：

SELECT DISTINCT(DATE(date)) as date FROM stocks

但是我被困住了，因为我无法得到最大/分钟ID或第一个/最后一个值。谢谢

Answer 1

您将从下面的查询中获得日间最小和最大ids。

 SELECT DATE_FORMAT(date, "%d/%m/%Y"),min(id) as min_id,max(id) as max_id  FROM stocks group by DATE_FORMAT(date, "%d/%m/%Y")

但其他要求尚不明确。

Answer 2

解决了！

mysql> UPDATE stocks s JOIN
    -> (SELECT k.date, k.value as v1, y.value as v2 FROM (SELECT x.date, x.min_id, x.max_id, stocks.value FROM (SELECT DATE(date) as date,min(id) as min_id,max(id) as max_id  FROM stocks group by DATE(date)) AS x LEFT JOIN stocks ON x.min_id = stocks.id) AS k LEFT JOIN stocks y ON k.max_id = y.id) sd
    -> ON DATE(s.date)  = sd.date
    -> SET s.open = sd.v1, s.close = sd.v2;
Query OK, 995872 rows affected (1 min 50.38 sec)
Rows matched: 995872  Changed: 995872  Warnings: 0