SQL:如何每天选择一条记录，假设每天包含多于1个值的MySQL [英] SQL: How to select one record per day, assuming that each day contain more than 1 value MySQL

问题描述

我想从'2013-04-01 00:00:00'到'today'中选择记录，但是每天都有很多价值，因为它们每15分钟保存一个值，所以我只希望每天有第一个或最后一个值.

I want to select records from '2013-04-01 00:00:00' to 'today' but, each day has lot of value, because they are saving each 15 minutes a value, so I want only the first or last value from each day.

表架构:

CREATE TABLE IF NOT EXISTS `value_magnitudes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` float DEFAULT NULL,
  `magnitude_id` int(11) DEFAULT NULL,
  `sdi_belongs_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `reading_date` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1118402 ;

错误的SQL:

SELECT value FROM `value_magnitudes` WHERE `value_magnitudes`.`reading_date` BETWEEN '2013-04-01 00:00:00' AND '2013-04-02 00:00:00' AND (`value_magnitudes`.magnitude_id = 234) LIMIT 1
SELECT value FROM `value_magnitudes` WHERE `value_magnitudes`.`reading_date` BETWEEN '2013-04-02 00:00:00' AND '2013-04-03 00:00:00' AND (`value_magnitudes`.magnitude_id = 234) LIMIT 1
SELECT value FROM `value_magnitudes` WHERE `value_magnitudes`.`reading_date` BETWEEN '2013-04-03 00:00:00' AND '2013-04-04 00:00:00' AND (`value_magnitudes`.magnitude_id = 234) LIMIT 1
SELECT value FROM `value_magnitudes` WHERE `value_magnitudes`.`reading_date` BETWEEN '2013-04-04 00:00:00' AND '2013-04-05 00:00:00' AND (`value_magnitudes`.magnitude_id = 234) LIMIT 1
SELECT value FROM `value_magnitudes` WHERE `value_magnitudes`.`reading_date` BETWEEN '2013-04-05 00:00:00' AND '2013-04-06 00:00:00' AND (`value_magnitudes`.magnitude_id = 234) LIMIT 1
etc ...

如果可能的话，我希望一劳永逸...

I want all in one if possible...

非常感谢您.

我的意思是，我每天都有一个查询，但是我只想从reading_date >= '2013-04-01 00:00:00' c

I mean, I have a query per day, but I just want to make a single query from reading_date >= '2013-04-01 00:00:00'c

我在table.value_magnitudes中有 64,260 条记录，执行并响应该查询需要很长时间，有时甚至超时.

I have 64,260 records in that table.value_magnitudes and it takes sooooooooo long to excecute and response that query, and sometimes timeout conection.

推荐答案

要获取每个日期的第一条记录

To get the first entry for every date you can do

select * from value_magnitudes
where id in 
(
    SELECT min(id)
    FROM value_magnitudes
    WHERE magnitude_id = 234
    and date(reading_date) >= '2013-04-01'
    group by date(reading_date)
)

这篇关于SQL:如何每天选择一条记录，假设每天包含多于1个值的MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！