两列结合条件mysql
我想结合2个不同列的条件来查询。这是我的原始查询。你可以在sqlfiddle.com中测试它。两列结合条件mysql
-- 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 |
+------+---------------------+---------------------+
请帮助我们如何获得所需的输出。我想从两栏中获得最后一笔,最后一笔。先谢谢你。
这努力保持解决方案易于维护,没有完成最终查询所有在一个镜头,这将几乎加倍它的大小(在我心中)。这是因为结果需要匹配,并在匹配的In和Out事件的一行中表示。最后,我使用了几张工作表。它在存储过程中实现。
存储过程使用几个带有cross join
的变量。将交叉连接想象为初始化变量的机制。变量保持安全,所以我相信,这个document的精神经常在变量查询中引用。引用的重要部分是安全处理一行中的变量,强制它们在使用它们的其他列之前被设置。这是通过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 |
+----+---------------------+---------------------+
这是答案的结尾。以下内容是开发人员对导致完成存储过程的步骤的可视化。
开发的版本,直到最后。希望这有助于可视化,而不是仅仅丢弃中等大小的混淆代码块。
步骤A
步骤B
步骤B输出
步骤C
步骤C输出
我upvoting将此作为一个例子每mysql的问题应该如何被写入保存帮助手动执行创建和数据加载。不这样做(无论是在问题中还是在sqlfiddle中)都会导致很多人通过你的问题。谢谢。 – Drew
从第6行到第7行,添加第3个'door-group'(导致您的第3行),因为last_state值是out(从第6行开始),实际的门值是empty '(在第7行),所以'Door!='Out OR ...'是真的。这取决于你的逻辑(何时创建一个新组)如何改变这种状况 - 我假设你需要检查'!='''或类似的东西。你可能想在'OR @last_door ='Off''检查你的代码,因为据我所知,这不会是真的(你可能打算使用另一个变量,比如'@ last_active_door'或者设置'@last_door: ='也取决于'active_door' – Solarflare
我忘了添加:为你的内部查询添加一个命令,例如'order by id',或'order by DateTime'使它可靠。对你而言,你可能是幸运的您的数据已经由DateTime订购(尽管这可能对您没有任何问题) – Solarflare