mysql - 结合两列mysql的条件

Question

我想为我的查询组合来自 2 个不同列的条件。这是我的原始查询。您可以在 sqlfiddle.com 中对其进行测试。

-- creating database first for test data
create table attendance(Id int, DateTime datetime, Door char(20));
INSERT INTO attendance VALUES
(    1,   '2016-01-01 08:00:00',  'In'),
(    2,   '2016-01-01 09:00:00',  'Out'),
(    3,   '2016-01-01 09:15:00',  'In'),
(    4,   '2016-01-01 09:30:00',  'In'),
(    5,   '2016-01-01 10:00:00',  'Out'),
(    6,   '2016-01-01 15:00:00',  'In');

SELECT * FROM attendance;
SELECT 
@id:=@id+1 Id,
MAX(IF(Door = 'In', DateTime, NULL)) `Check In`,
MAX(IF(Door = 'Out', DateTime, NULL)) `Check Out`
FROM
(SELECT 
*, 
CASE 
    WHEN
        (Door != 'Out' AND @last_door = 'Out')
    THEN @group_num:=@group_num+1
    ELSE @group_num END door_group, 
    @last_door:=Door
FROM attendance 
JOIN (SELECT @group_num:=1,@last_door := NULL) a
) t JOIN (SELECT @id:=0) b
GROUP BY t.door_group
HAVING SUM(Door = 'In') > 0 AND SUM(Door = 'Out') > 0;

//output
+------+---------------------+---------------------+
| Id   | Check In            | Check Out           |
+------+---------------------+---------------------+
|    1 | 2016-01-01 08:00:00 | 2016-01-01 09:00:00 |
|    2 | 2016-01-01 09:30:00 | 2016-01-01 10:00:00 |
+------+---------------------+---------------------+

根据上面的查询，我想再添加一列。

-- creating database first for test data
create table attendance(Id int, DateTime datetime, Door char(20), Active_door char(20));
INSERT INTO attendance VALUES
(    1,   '2016-01-01 08:00:00',  'In', ''),
(    2,   '2016-01-01 09:00:00',  'Out', ''),
(    3,   '2016-01-01 09:15:00',  'In', ''),
(    4,   '2016-01-01 09:30:00',  'In', ''),
(    5,   '2016-01-01 09:35:00',  '', 'On'),
(    6,   '2016-01-01 10:00:00',  'Out', ''),
(    7,   '2016-01-01 16:00:00',  '', 'Off');

这是我对查询所做的更改，但它不起作用。

SELECT * FROM attendance;
SELECT 
@id:=@id+1 Id,
MAX(IF(Door = 'In' OR Active_door = "On", DateTime, NULL)) `Check In`,
MAX(IF(Door = 'Out' OR Active_door = "Off", DateTime, NULL)) `Check Out`
FROM
(SELECT 
*, 
CASE 
    WHEN
        ((Door != 'Out' OR Active_door != "Off") AND (@last_door = 'Out' OR  @last_door = 'Off'))
    THEN @group_num:=@group_num+1
    ELSE @group_num END door_group, 
    @last_door:=Door
FROM attendance 
JOIN (SELECT @group_num:=1,@last_door := NULL) a
) t JOIN (SELECT @id:=0) b
GROUP BY t.door_group
HAVING SUM(Door = 'In') > 0 OR SUM(Active_door = 'On') > 0 AND SUM(Door = 'Out') > 0  OR SUM(Active_door = 'Off') > 0;

//output
+------+---------------------+---------------------+
| Id   | Check In            | Check Out           |
+------+---------------------+---------------------+
|    1 | 2016-01-01 08:00:00 | 2016-01-01 09:00:00 |
|    2 | 2016-01-01 09:35:00 | 2016-01-01 10:00:00 |
|    3 | NULL                | 2016-01-01 16:00:00 |
+------+---------------------+---------------------+

//my desire output
+------+---------------------+---------------------+
| Id   | Check In            | Check Out           |
+------+---------------------+---------------------+
|    1 | 2016-01-01 08:00:00 | 2016-01-01 09:00:00 |
|    2 | 2016-01-01 09:35:00 | 2016-01-01 16:00:00 |
+------+---------------------+---------------------+

请帮帮我，我怎样才能得到想要的输出。我想从两列中获得最后一个和最后一个。提前谢谢你。

Answer 1

这努力使解决方案易于维护，而无需一次性完成最终查询，这几乎会使它的大小增加一倍(在我看来)。这是因为结果需要与匹配的 In 和 Out 事件匹配并在一行中表示。所以最后，我使用了一些工作台。它在存储过程中实现。

存储过程使用了多个通过交叉连接 引入的变量。将交叉连接视为一种初始化变量的机制。变量是安全维护的，所以我相信，本着 document 的精神通常在变量查询中引用。引用的重要部分是行上变量的安全处理，强制在使用它们的其他列之前设置它们。这是通过 greatest() 和 least() 函数实现的，它们比不使用这些函数设置的变量具有更高的优先级。另请注意，coalesce() 通常用于相同目的。如果它们的使用看起来很奇怪，例如取已知大于 0 或 0 的最大数，那么这是故意的。故意强制设置变量的优先顺序。

查询中名为 dummy2 等的列是未使用输出的列，但它们用于在内部设置变量，例如 greatest() 或另一个。这在上面已经提到了。像 7777 这样的输出是第三个插槽中的占位符，因为使用的 if() 需要一些值。所以请忽略所有这些。

我包含了代码的几个屏幕截图，因为它逐层进行以帮助您可视化输出。以及这些开发迭代如何慢慢融入下一阶段以扩展之前的阶段。

我相信我的同行可以在一次查询中改进这一点。我本可以那样完成它。但我相信这会导致一团困惑，一碰就会破。

架构:

create table attendance2(Id int, DateTime datetime, Door char(20), Active_door char(20));
INSERT INTO attendance2 VALUES
(    1,   '2016-01-01 08:00:00',  'In', ''),
(    2,   '2016-01-01 09:00:00',  'Out', ''),
(    3,   '2016-01-01 09:15:00',  'In', ''),
(    4,   '2016-01-01 09:30:00',  'In', ''),
(    5,   '2016-01-01 09:35:00',  '', 'On'),
(    6,   '2016-01-01 10:00:00',  'Out', ''),
(    7,   '2016-01-01 16:00:00',  '', 'Off');

drop table if exists oneLinersDetail;
create table oneLinersDetail
(   -- architect this depending on multi-user concurrency
    id int not null,
    dt datetime not null,
    door int not null,
    grpIn int not null,
    grpInSeq int not null,
    grpOut int not null,
    grpOutSeq int not null
);

drop table if exists oneLinersSummary;
create table oneLinersSummary
(   -- architect this depending on multi-user concurrency
    id int not null,
    grpInSeq int null,
    grpOutSeq int null,
    checkIn datetime null, -- we are hoping in the end it is not null
    checkOut datetime null -- ditto
);

存储过程:

DROP PROCEDURE IF EXISTS fetchOneLiners;
DELIMITER $$
CREATE PROCEDURE fetchOneLiners()
BEGIN
    truncate table oneLinersDetail; -- architect this depending on multi-user concurrency

    insert oneLinersDetail(id,dt,door,grpIn,grpInSeq,grpOut,grpOutSeq)
    select id,dt,door,grpIn,grpInSeq,grpOut,grpOutSeq  
    from 
    (   select id,dt,door, 
        if(@lastEvt!=door and door=1, 
            greatest(@grpIn:=@grpIn+1,0), 
            7777) as dummy2, -- this output column we don't care about (we care about the variable being set)
        if(@lastEvt!=door and door=2, 
            greatest(@grpOut:=@grpOut+1,0), 
            7777) as dummy3, -- this output column we don't care about (we care about the variable being set)
        if (@lastEvt!=door,greatest(@flip:=1,0),least(@flip:=0,1)) as flip, 
        if (door=1 and @flip=1,least(@grpOutSeq:=0,1),7777) as dummy4, 
        if (door=1 and @flip=1,greatest(@grpInSeq:=1,0),7777) as dummy5, 
        if (door=1 and @flip!=1,greatest(@grpInSeq:=@grpInSeq+1,0),7777) as dummy6, 
        if (door=2 and @flip=1,least(@grpInSeq:=0,1),7777) as dummy7, 
        if (door=2 and @flip=1,greatest(@grpOutSeq:=1,0),7777) as dummy8, 
        if (door=2 and @flip!=1,greatest(@grpOutSeq:=@grpOutSeq+1,0),7777) as dummy9, 
        @grpIn as grpIn, 
        @grpInSeq as grpInSeq, 
        @grpOut as grpOut, 
        @grpOutSeq as grpOutSeq, 
        @lastEvt:=door as lastEvt 
        from 
        (   select id,`datetime` as dt, 
            CASE   
                WHEN Door='in' or Active_door='on' THEN 1 
                ELSE 2 
            END as door 
            from attendance2 
            order by id 
        ) xD1 -- derived table #1
        cross join (select @grpIn:=0,@grpInSeq:=0,@grpOut:=0,@grpOutSeq:=0,@lastEvt:=-1,@flip:=0) xParams 
        order by id 
    ) xD2 -- derived table #2
    order by id;
    -- select * from oneLinersDetail;

    truncate table oneLinersSummary;    -- architect this depending on multi-user concurrency

    insert oneLinersSummary (id,grpInSeq,grpOutSeq,checkIn,checkOut)
    select distinct grpIn,null,null,null,null
    from oneLinersDetail
    order by grpIn;

    -- select * from oneLinersSummary;

    update oneLinersSummary ols
    join
    (   select grpIn,max(grpInSeq) m
        from oneLinersDetail
        where door=1
        group by grpIn
    ) d1
    on d1.grpIn=ols.id
    set ols.grpInSeq=d1.m;

    -- select * from oneLinersSummary;

    update oneLinersSummary ols
    join
    (   select grpOut,max(grpOutSeq) m
        from oneLinersDetail
        where door=2
        group by grpOut
    ) d1
    on d1.grpOut=ols.id
    set ols.grpOutSeq=d1.m;

    -- select * from oneLinersSummary;

    update oneLinersSummary ols
    join oneLinersDetail old
    on old.door=1 and old.grpIn=ols.id and old.grpInSeq=ols.grpInSeq
    set ols.checkIn=old.dt;

    -- select * from oneLinersSummary;

    update oneLinersSummary ols
    join oneLinersDetail old
    on old.door=2 and old.grpOut=ols.id and old.grpOutSeq=ols.grpOutSeq
    set ols.checkOut=old.dt;

    -- select * from oneLinersSummary;

    -- dump out the results
    select id,checkIn,checkOut
    from oneLinersSummary
    order by id;
    -- rows are left in those two tables (oneLinersDetail,oneLinersSummary)
END$$
DELIMITER ;

测试:

call fetchOneLiners();
+----+---------------------+---------------------+
| id | checkIn             | checkOut            |
+----+---------------------+---------------------+
|  1 | 2016-01-01 08:00:00 | 2016-01-01 09:00:00 |
|  2 | 2016-01-01 09:35:00 | 2016-01-01 16:00:00 |
+----+---------------------+---------------------+

答案到此结束。下面是开发人员对导致完成存储过程的步骤的可视化。

一直到最后的开发版本。希望这有助于可视化，而不是仅仅丢弃中等大小的困惑代码块。

步骤A

步骤 B

B 步输出

步骤 C

步骤C输出