Es ist für ein System vorgesehen, das berechnet, wie die Benutzer ihre Fingerabdrücke scannen, wenn sie den Arbeitsplatz betreten oder verlassen. Ich weiß nicht, wie es auf Englisch heißt. Ich muss feststellen, ob der Benutzer zu spät am Morgen ist und ob er die Arbeit vorzeitig verlässt.

Diestb_scan Tabelle enthält Datum und Uhrzeit, wann ein Benutzer einen Fingerabdruck scannt.

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

s hat mehr als 100.000 Zeilen, so etwas wie dieses

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

Und dastb_workday Tabelle enthält jedes Datum

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

s hat Zeilen mit der folgenden Datumsreihenfolge:

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

Es gibt einen anderentb_shift Tabelle mit Schichtzeiten

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

Ich möchte feststellen, dass der Mitarbeiter an jedem Tag zu spät zur Arbeit kommt oder die Arbeit vorzeitig verlässt und zu welcher Zeit.

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)

Hier ist das Beispiel einer Ausgabe:

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

DiesADDDATE(CONCAT(wddate,' ',shiftbegin),INTERVAL IF(shiftbegin2>shiftbegin,1,0) DAY) ist für Mitarbeiter, die in der Nachtschicht arbeiten, daher muss 1 Tag zur Schichtzeit hinzugefügt werden

Das Problem ist, wenn ich einen Index für @ erstellscscantime, MySQL weigert sich, es zum Vergleich zu verwenden >=,<=,>,<). Bitte sehen Sie diesen ThreadWarum verwendet MySQL keinen Index, der größer als der Vergleich ist?

Aus diesem Grund habe ich das @ erstelscyear, scmonth, undscday Felder und kombinieren sie in einem Index zusammen mitscpercode. Und ich muss sicherstellen, dass es auch für Arbeitnehmer berechnet wird, die in der Nachtschicht arbeiten, also muss ich es mit @ hinzufügeOR scday=day(wddate)+1 Bedingung

Bevor ich die ODER-Bedingung hinzufügte, wurde dasEXPLAIN Ergebnis war 52 Zeilen. Aber als ich das @ hinzufügOR scday=day(wddate)+1 Bedingung, dieEXPLAINas @ -Ergebnis wurde zu 364 Zeilen, was bedeutet, dass MySQL den scday-Teil des Index nicht verwendet hat. Gibt es eine Möglichkeit, den gesamten Index zu verwenden, so dass dasEXPLAIN Ergebnis wird effizienter wie 52 Zeilen? Ich habe auch versucht, das @ zu entfern+1 Teil und das Ergebnis ist auch 52.