É para um sistema que calcula como os usuários digitalizam suas impressões digitais quando entram / saem do local de trabalho. Não sei como é chamado em inglês. Preciso determinar se o usuário está atrasado pela manhã e se ele sai do trabalho mais cedo.

estetb_scan A tabela contém a data e a hora em que um usuário digitaliza uma impressão digital.

CREATE TABLE `tb_scan` (
  `scpercode` varchar(6) DEFAULT NULL,
  `scyear` varchar(4) DEFAULT NULL,
  `scmonth` varchar(2) DEFAULT NULL,
  `scday` varchar(2) DEFAULT NULL,
  `scscantime` datetime,
  KEY `all` (`scyear`,`scmonth`,`scday`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Possui mais de 100.000 linhas, algo como isto

scpercode scyear scmonth scday     scdateandtime
000001    2010      10     10      2016-01-10 08:02:00
000001    2010      10     10      2016-01-02 17:33:00
000001    2010      10     11      2016-01-11 07:48:00
000001    2010      10     11      2016-01-11 17:29:00
000002    2010      10     10      2016-01-10 17:31:00
000002    2010      10     10      2016-01-02 17:28:00
000002    2010      10     11      2016-01-11 05:35:00
000002    2010      10     11      2016-01-11 05:29:00

E istotb_workday tabela contém cada data

CREATE TABLE `tb_workday` (
  `wdpercode` varchar(6) DEFAULT NULL,
  `wdshift` varchar(1) DEFAULT NULL,
  `wddate` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Possui linhas com sequência de datas como esta:

wdpercode  wdshift wddate
000001     1       2010-10-10
000001     1       2010-10-11
000001     1       2010-10-12
000001     1       2010-10-13
000002     2       2010-10-10
000002     2       2010-10-11
000002     2       2010-10-12
000002     2       2010-10-13

Tem outrotb_shift tabela contendo o horário do turno

CREATE TABLE `tb_shift` (
  `shiftcode` varchar(1) DEFAULT NULL,
  `shiftbegin2` varchar(4) DEFAULT NULL,
  `shiftbegin` varchar(4) DEFAULT NULL,
  `shiftmid` varchar(4) DEFAULT NULL,
  `shiftend` varchar(4) DEFAULT NULL,
  `shiftend2` varchar(4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

shiftcode   shiftbegin2  shiftbegin  shiftmid  shiftend  shiftend2
        1     04:00:00     08:00:00  12:00:00  17:30:00  21:30:00 
        2     12:00:00     17:30:00  21:00:00  05:30:00  09:30:00

Quero determinar que, a cada dia, o funcionário chega tarde ou sai mais cedo e a que horas.

SELECT wdpercode,wddate,shiftbegin,shiftend,time(tlate.scscantime) wdlate,time(tearly.scscantime) wdearly
FROM tb_workday
LEFT JOIN tb_shift
  ON wdshift=shiftcode
LEFT JOIN tb_scan tlate 
  ON wdpercode=tlate.scpercode
  AND tlate.scyear=year(wddate)
  AND tlate.scmonth=month(wddate)
  AND (tlate.scday=day(wddate)
    OR tlate.scday=day(wddate)+1)
  AND tlate.scscantime>=ADDDATE(CONCAT(wddate,' ',shiftbegin),INTERVAL IF(shiftbegin2>shiftbegin,1,0) DAY)
  AND tlate.scscantime<=ADDDATE(CONCAT(wddate,' ',shiftmid),INTERVAL IF(shiftbegin2>shiftmid,1,0) DAY)
LEFT JOIN tb_scan tearly 
  ON wdpercode=tearly.scpercode
  AND tearly.scyear=year(wddate)
  AND tearly.scmonth=month(wddate)
  AND (tearly.scday=day(wddate)
    OR tearly.scday=day(wddate)+1)
  AND tearly.scscantime>ADDDATE(CONCAT(wddate,' ',shiftmid),INTERVAL IF(shiftbegin2>shiftmid,1,0) DAY)
  AND tearly.scscantime<ADDDATE(CONCAT(wddate,' ',shiftend),INTERVAL IF(shiftbegin2>shiftend,1,0) DAY)

Aqui está o exemplo de uma saída:

wdpercode wddate      shiftbegin  shiftend  wdlate    wdearly
000001    2016-01-10  08:00:00    17:30:00  08:02:00  (null)
000001    2016-01-11  08:00:00    17:30:00  (null)    17:29:00
000002    2016-01-11  17:30:00    05:30:00  17:31:00  (null)
000002    2016-01-11  17:30:00    05:30:00  (null)    05:29:00

estaADDDATE(CONCAT(wddate,' ',shiftbegin),INTERVAL IF(shiftbegin2>shiftbegin,1,0) DAY) é para funcionários que trabalham no turno da noite, portanto, é necessário adicionar 1 dia no horário do turno

O problema é se eu criar um índice parascscantime, O MySQL se recusa a usá-lo para comparação (>=,<=,>,<) Por favor, veja este tópicoPor que o MySQL não usa um índice para uma comparação maior que a?

Por isso, criei oscyear, scmonthescday campos e combiná-los em um índice junto comscpercode. E eu tenho que garantir que calcule para os trabalhadores que trabalham no turno da noite também, então eu tenho que adicioná-lo comOR scday=day(wddate)+1 condição.

Antes de adicionar a condição OR, oEXPLAIN resultado foi de 52 linhas. Mas quando eu adicionei oOR scday=day(wddate)+1 condição, oEXPLAIN O resultado se tornou 364 linhas, o que significa que o MySQL não usou parte do scday do índice. Existe alguma maneira de usar todo o índice, então oEXPLAIN resultado se torna mais eficiente como 52 linhas? Eu também tentei remover o+1 parte e o resultado também é 52.