我想为我的查询合并来自两个不同列的条件。这是我最初的问题。你可以在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 |
+------+---------------------+---------------------+
请帮我，伙计们，我怎样才能得到想要的结果。我想从这两个栏中取最后一个和最后一个。提前谢谢你。
最佳答案
这将使解决方案易于维护，而不必一次性完成最终的查询，这将使它的大小几乎翻倍（在我看来）。这是因为结果需要匹配，并用匹配的in和out事件表示在一行上。最后，我使用了一些工作台。它是在存储过程中实现的。
存储过程使用多个变量，这些变量是用cross join带进来的。把交叉连接看作是初始化变量的一种机制。变量是安全维护的，所以我相信，本着这种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输出
关于mysql - 结合两列mysql的条件，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/37940132/