Объединение условий из двух столбцов mysql

Я хотел бы объединить условия из 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:[email protected]+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:[email protected]_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:[email protected]+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:[email protected]_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 |
+------+---------------------+---------------------+

Пожалуйста, помогите мне, ребята, как я могу получить желаемый результат. Я хотел бы получить последний и последний из обеих колонок. Заранее спасибо.

 Drew21 июн. 2016 г., 14:02
Я высказываю это как пример того, как каждый вопрос mysql должен быть написан, чтобы избавить помощника от ручного выполнения создания и загрузки данных. Неспособность сделать это (либо в вопросе, либо с помощью sqlfiddle) заставляет многих проходить мимо вашего вопроса. Благодарю.
 Solarflare21 июн. 2016 г., 14:19
И я забыл добавить: добавить заказ к вашему внутреннему запросу, например,order by id, или жеorder by DateTime сделать это надежным. В вашем случае вам может повезти, что ваши данные уже упорядочены DateTime (хотя это, вероятно, может работать для вас без проблем).
 Solarflare21 июн. 2016 г., 14:09
Из строки 6-7 вы добавляете третийdoor-group (что приводит к вашей 3-й строке), так какlast door-значение "out" (из строки 6) и фактическое значение двериempty (в строке 7), такDoor != 'Out OR... правда. Это зависит от вашей логики (когда создавать новую группу), как изменить это условие - я предполагаю, что вам нужно проверить!= '' или что-то типа того. И вы можете проверить свой код наOR @last_door = 'Off', поскольку, насколько я понимаю, это не будет правдой (возможно, вы хотели использовать другую переменную, такую ​​как@last_active_door или установить@last_door := в зависимости отactive_door тоже.

Ответы на вопрос(1)

не выполняя окончательный запрос за один раз, что почти удвоило бы его размер (на мой взгляд). Это связано с тем, что результаты должны совпадать и отображаться в одной строке с совпадающими событиями In и Out. В конце я использую несколько рабочих столов. Это реализовано в хранимой процедуре.

Хранимая процедура использует несколько переменных, которые вводятся сcross join, Думайте о перекрестном соединении как о механизме инициализации переменных. Переменные поддерживаются безопасно, так что я считаю, в духе этогодокумент часто упоминается в переменных запросах. Важными частями справочника являются безопасная обработка переменных в строке, заставляющая их устанавливаться до того, как их используют другие столбцы. Это достигается за счетgreatest() а такжеleast() функции, которые имеют более высокий приоритет, чем переменные, устанавливаемые без использования этих функций. Обратите внимание, чтоcoalesce() часто используется для той же цели. Если их использование кажется странным, например, взятие наибольшего числа, о котором известно, что оно больше 0 или 0, то это намеренно. Умышленно устанавливайте порядок приоритетов устанавливаемых переменных.

Столбцы в запросе названы такими вещами, какdummy2 и т.д. - это столбцы, выходные данные которых не использовались, но они использовались для установки переменных внутри, скажем,greatest() или другой. Это было упомянуто выше. Вывод наподобие 7777 был заполнителем в 3-м слоте, так как для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:[email protected]+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:[email protected]+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:[email protected]+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:[email protected]+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 |
+----+---------------------+---------------------+

Это конец ответа. Ниже приведена информация для разработчика о шагах, которые привели к завершению хранимой процедуры.

Версии развития, доведенные до конца. Надеемся, что это помогает в визуализации, а не просто сбивает с толку кусочек кода среднего размера.

Шаг А

Шаг Б

Шаг B вывод

Шаг С

Шаг C вывод

 Drew22 июн. 2016 г., 04:22
Вероятно, лучше всего объяснить это мне в чате, мы можем обобщить это как редактирование. Я в этой комнате называетсяКампании
 EDDY22 июн. 2016 г., 04:11
Я пытаюсь «оставить достаточно хорошо в одиночестве», но, похоже, я не смог получить ожидаемый результат. Спасибо за идею, но я все еще не могу получить желаемый результат. Почему я не могу получить значение из столбца "Active_door", который имеет значение "2016-01-01 16:00:00"? Проверьте мой желаемый результат еще раз.
 EDDY22 июн. 2016 г., 10:25
вау .. это было много работ от вас @Drew. Я все еще копаюсь в твоих кодах, чтобы заставить себя понять весь процесс. Большое спасибо за все ваши усилия по этому делу! Дадим вам знать еще раз, если я не понимаю, какой термин вы используете в запросе. :)

Ваш ответ на вопрос