mysql - 带 GROUP BY 和条件的 SQL 查询

Question

我有一个包含contract 值的“股票市场”数据表。我想获取交易量(每天执行的交易数量)和收盘价，收盘价是该交易日最后记录的合约价格。

CREATE TABLE IF NOT EXISTS `contracts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `price` decimal(5,2) NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1502 ;

我可以通过如下查询获得我想要的数据(主要是!):

SELECT count(id) as volume, price, DATE(created_at) FROM `contracts` 
GROUP BY DATE(created_at)

但是，我想要收盘价——返回的价格是 ID 恰好位于最后的任何记录的价格。有没有办法获取 created_at 列设置的最后一个 price 值？

Answer 1

SELECT id,
   count(id) AS volume,
   DATE(created_at),
   (SELECT price
      FROM `contracts` as innerContracts
      WHERE innerContracts.id = outerContracts.id
      ORDER BY created_at DESC LIMIT 1)
FROM `contracts` AS outerContracts
GROUP BY DATE(created_at)