从同一表中选择特定的SQL

Juraj Kubala 发表于 Dev

朱拉·库巴拉（Juraj Kubala）

我有一张表main_table，其中包含输入和输出的记录。

id | type | time                | joiner
 1   out    2014-10-15 12:00:00   1
 2   in     2014-10-15 13:00:00   1
 3   out    2014-10-15 14:00:00   1
 4   out    2014-10-15 15:00:00   1
 5   in     2014-10-15 16:00:00   1
 6   out    2014-10-15 17:00:00   1

当我加入时，结果是：

SELECT 
   t1.id as id_out, t1.time as time_out, t2.id as id_in, t2.time as time_in
FROM
   (SELECT * FROM main_table WHERE type = "out") as t1
   LEFT JOIN
   (SELECT * FROM main_table WHERE type = "in") as t2 
   ON t1.joiner = t2.joiner AND t1.id < t2.id
GROUP BY 
   t1.id

id_out | time_out            | id_in | time_in             | ....
1        2014-10-15 12:00:00   2       2014-10-15 13:00:00
3        2014-10-15 13:00:00   5       2014-10-15 16:00:00
4        2014-10-15 15:00:00   5       2014-10-15 16:00:00
6        2014-10-15 17:00:00   NULL    NULL

我需要跳过第二行，因为记录id = 5必须仅存在一次。

PS：“ joiner”是必需的，因为在这里它只有一个值，但是会有更多值。

Here is SQL for create table and insert records

CREATE TABLE IF NOT EXISTS `main_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` enum('in','out') NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `joiner` int(2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

INSERT INTO `main_table` (`id`, `type`, `time`, `joiner`) VALUES
(1, 'out', '2014-10-15 10:00:00', 1),
(2, 'in', '2014-10-15 11:00:00', 1),
(3, 'out', '2014-10-15 12:00:00', 1),
(4, 'out', '2014-10-15 13:00:00', 1),
(5, 'in', '2014-10-15 14:00:00', 1),
(6, 'out', '2014-10-15 15:00:00', 1);

加雷斯

您需要更改分组（这可能很直观），以分组id_in，而不是id_out：

SELECT  MAX(o.time) AS time_out,
        i.id AS id_in,
        i.time AS time_in
FROM    main_table AS o
        LEFT JOIN main_table AS i
            ON i.joiner = o.joiner
            AND i.time > o.time
            AND i.type = 'in'
WHERE   o.type = 'out'
GROUP BY i.id, i.time
ORDER BY time_out;

这给出：

TIME_OUT                ID_IN   TIME_IN
2014-15-10 10:00:00     2       2014-15-10 11:00:00+0000
2014-15-10 13:00:00     5       2014-15-10 14:00:00+0000
2014-15-10 15:00:00     NULL    NULL

我已使用time而不是id用于连接，以防万一您的记录插入顺序不正确。不幸的是，这意味着在同一条语句中获取id_out是不确定的。为了做到这一点，您要么需要将上面的查询作为子查询，然后再次联接回表：

SELECT  o.id AS id_out,
        t.time_out,
        t.id_in,
        t.time_in
FROM    (   SELECT  MAX(o.time) AS time_out,
                    i.id AS id_in,
                    i.time AS time_in,
                    o.joiner
            FROM    main_table AS o
                    LEFT JOIN main_table AS i
                        ON i.joiner = o.joiner
                        AND i.time > o.time
                        AND i.type = 'in'
            WHERE   o.type = 'out'
            GROUP BY i.id, i.time, o.joiner
        ) AS t
        INNER JOIN Main_table AS o
            ON o.joiner = t.joiner
            AND o.time = t.time_out
            AND o.type = 'out';

或使用相关子查询：

SELECT  (   SELECT  id
            FROM    main_table AS o2
            WHERE   o2.joiner = o.joiner
            AND     o2.time = MAX(o.time)
            AND     o2.type = 'out'
            LIMIT 1
        ) AS id_out,
        MAX(o.time) AS time_out,
        i.id AS id_in,
        i.time AS time_in
FROM    main_table AS o
        LEFT JOIN main_table AS i
            ON i.joiner = o.joiner
            AND i.time > o.time
            AND i.type = 'in'
WHERE   o.type = 'out'
GROUP BY i.id, i.time
ORDER BY time_out;