mysql - 删除具有关联 "time_worked"记录的 SQL 结果

Question

编辑:我在底部添加了一个更清楚的例子来说明我的意思。

这里是查询、模式和结果(如果需要可以在 SQLfiddle 上查看):

SQL Fiddle

MySQL 5.6 架构设置:

CREATE TABLE IF NOT EXISTS `transactions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `effective_sn` int(11) DEFAULT NULL,
  `serial_num` int(11) DEFAULT NULL,
  `actor` varchar(100) DEFAULT NULL,
  `type` varchar(20) DEFAULT NULL,
  `trans_data` text,
  `trans_date` int(11) DEFAULT NULL,
  `trans_prev` text,
  `content` text,
  PRIMARY KEY (`id`),
  KEY `effective_sn` (`effective_sn`),
  KEY `serial_num` (`serial_num`),
  KEY `type_index` (`type`),
  KEY `trans_date_index` (`trans_date`),
  FULLTEXT KEY `content` (`content`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1093579 ;

INSERT INTO `transactions` (`id`, `effective_sn`, `serial_num`, `actor`, `type`, `trans_data`, `trans_date`, `trans_prev`, `content`) VALUES
(1091622, 100628, 100628, 'jhvisser', 'comment', '', 1435864188, NULL, 'some comment'),
(1091749, 100628, 100628, 'jhvisser', 'comment', '', 1435926407, NULL, 'some comment'),
(1092012, 100628, 100628, 'jhvisser', 'comment', '', 1436189497, NULL, 'some comment'),
(1092085, 100628, 100628, 'jhvisser', 'comment', '', 1436199139, NULL, 'some comment'),
(1092162, 100628, 100628, 'jhvisser', 'comment', '', 1436205996, NULL, 'some comment'),
(1092240, 100628, 100628, 'jhvisser', 'comment', '', 1436211675, NULL, 'some comment'),
(1092252, 100628, 100628, 'jhvisser', 'comment', '', 1436213410, NULL, 'some comment'),
(1092288, 100628, 100628, 'jhvisser', 'comment', '', 1436217645, NULL, 'some comment'),
(1092376, 100628, 100628, 'jhvisser', 'comment', '', 1436277006, NULL, 'some comment'),
(1093530, 100628, 100628, 'jhvisser', 'time_worked', '60', 1436551662, NULL, NULL),
(1093531, 100628, 100628, 'jhvisser', 'comment', '', 1436551662, NULL, 'some comment');

查询 1:

SELECT *  FROM `transactions` WHERE `serial_num` = 100628 and (type='comment' or type='time_worked')

Results :

|      id | effective_sn | serial_num |    actor |        type | trans_data | trans_date | trans_prev |      content |
|---------|--------------|------------|----------|-------------|------------|------------|------------|--------------|
| 1091622 |       100628 |     100628 | jhvisser |     comment |            | 1435864188 |     (null) | some comment |
| 1091749 |       100628 |     100628 | jhvisser |     comment |            | 1435926407 |     (null) | some comment |
| 1092012 |       100628 |     100628 | jhvisser |     comment |            | 1436189497 |     (null) | some comment |
| 1092085 |       100628 |     100628 | jhvisser |     comment |            | 1436199139 |     (null) | some comment |
| 1092162 |       100628 |     100628 | jhvisser |     comment |            | 1436205996 |     (null) | some comment |
| 1092240 |       100628 |     100628 | jhvisser |     comment |            | 1436211675 |     (null) | some comment |
| 1092252 |       100628 |     100628 | jhvisser |     comment |            | 1436213410 |     (null) | some comment |
| 1092288 |       100628 |     100628 | jhvisser |     comment |            | 1436217645 |     (null) | some comment |
| 1092376 |       100628 |     100628 | jhvisser |     comment |            | 1436277006 |     (null) | some comment |
| 1093530 |       100628 |     100628 | jhvisser | time_worked |         60 | 1436551662 |     (null) |       (null) |
| 1093531 |       100628 |     100628 | jhvisser |     comment |            | 1436551662 |     (null) | some comment |

在这个查询中，我显示了 time_worked 类型，这样您就可以明白我指的是什么。如果在创建评论记录之前或之后的 5 分钟内也创建了 time_worked 类型的记录，我不希望结果中出现 comment。

这可能令人困惑，但这个想法本身很简单，所以我将通过一个示例来解释它。我正在使用时间跟踪应用程序。很多时候人们对项目发表评论而忘记记录工作时间。我的想法是我想在没有记录时间的地方找到评论。人们可能会意识到他们忘记了并在几分钟后添加工作时间，这就是为什么我说它可以在 5 分钟之前或之后创建。

看上图中的结果，有2个不应该出现的结果，这是最后两个，因为评论时加上了时间。

是否可以在 SQL 中执行类似的操作？

编辑:

输入:与上面类似的 SQL 查询。输出:我想修改现有的查询，我必须摆脱具有关联的“time_worked”类型的“评论”类型。当我提到类型时，我指的是实际列 type，其中包含数据 comment 或 time_worked。本质上，如果有 time_worked 类型和 comment 具有相同的 trans_date(unix 时间)，它们不应出现在结果中。

所以在示例数据中我给出的最后两行将不会显示。

Answer 1

您正在做的是查找同一表中不存在某种类型的关联记录的记录。为此，使用 LEFT self JOIN:

SELECT comments.*, DATE_SUB(FROM_UNIXTIME(comments.
            trans_date), INTERVAL 5 MINUTE), 
    timeworked.id
FROM transactions AS comments
LEFT JOIN transactions AS timeworked ON comments.
    serial_num = timeworked.serial_num
    AND FROM_UNIXTIME(comments.trans_date) BETWEEN 
            DATE_SUB(FROM_UNIXTIME(timeworked.
                    trans_date), INTERVAL 5 MINUTE)
        AND DATE_ADD(FROM_UNIXTIME(timeworked.
                    trans_date), INTERVAL 5 MINUTE)
    AND timeworked.type = 'time_worked'
WHERE comments.type = 'comment'
    AND timeworked.id IS NULL