mysql 选择时间范围和条件

Question

error_tbl                   success_tbl
id | creationTime           id|creationTime
1    2014-09-23 10:03:40    1212 2014-09-23 10:02:40
                            1213 2014-09-23 10:03:40
                            1214 2014-09-23 10:10:40

运行查询后我想要这个:

result table
creationTime        | fail_ids | succ_ids
2014-09-23 10:03:40, 1,          NULL
2014-09-23 10:02:40, NULL,       1212 
2014-09-23 10:03:40, NULL,       1213 

我想选择每5分钟时间范围内的所有数据(成功和失败)，前提是该时间段内error_tbl中存在任何记录。

(SELECT
    creationTime, 
    id as  fail_ids,null as succ_ids
FROM error_tbl a

 UNION
SELECT
    creationTime,  
    null as fail_ids ,id as succ_ids
FROM success_tbl b
 ) s

Answer 1

给定简化的架构，并进行一些细微的更改，使其看起来干净。

CREATE TABLE error(
  `error_id` INT, 
  `creation_time` DATETIME
);
INSERT INTO error(`error_id`, `creation_time`) VALUES   
  (1, '2014-09-23 10:03:40'),
  (2, '2014-09-23 10:04:05'),
  (3, '2014-09-24 10:08:04');

CREATE TABLE success(
  `success_id` INT, 
  `creation_time` DATETIME
);
INSERT INTO success(`success_id`, `creation_time`) VALUES
  (1212, '2014-09-23 10:02:40'),
  (1213, '2014-09-23 10:03:40'),
  (1214, '2014-09-23 10:10:40');

这应该可行，尽管我不希望它在大数据集上表现良好。

SELECT CONCAT(
    DATE_FORMAT(creation_time, '%Y-%m-%d %k:'), 
    LPAD(FLOOR(MINUTE(creation_time) / 5) * 5, 2, 0), 
    ':00'
  ) `creation_time`, 
  GROUP_CONCAT(error_id) `error_ids`, 
  GROUP_CONCAT(success_id) `success_ids`
FROM (
  (
    SELECT NULL success_id, error_id, creation_time 
    FROM error
  )
  UNION
  (
    SELECT success_id, NULL error_id, creation_time
    FROM success
  )
) un
GROUP BY FLOOR(UNIX_TIMESTAMP(creation_time) / (5 * 60))
HAVING error_ids IS NOT NULL

SQL Fiddle snippet .