#mysql
#mysql
Вопрос:
Я пытаюсь создать запрос, чтобы получить конкретный результат. Вот мое требование:
у меня есть две таблицы, одна из которых представляет собой выписку сведений о отпечатках пальцев биометрической машины:
у меня есть две таблицы с именами raw_test и calender
mysql> desc raw_test;
--------------- ---------- ------ ----- --------- -------
| Field | Type | Null | Key | Default | Extra |
--------------- ---------- ------ ----- --------- -------
| emp_id | int(4) | YES | | NULL | |
| time_loginout | datetime | YES | | NULL | |
--------------- ---------- ------ ----- --------- -------
2 rows in set (0.00 sec)
mysql> desc calender;
--------------- ---------- ------ ----- --------- -------
| Field | Type | Null | Key | Default | Extra |
--------------- ---------- ------ ----- --------- -------
| time_loginout | datetime | YES | | NULL | |
--------------- ---------- ------ ----- --------- -------
1 row in set (0.00 sec)
таблица raw_test содержит следующие данные:
mysql> select * from raw_test;
-------- ---------------------
| emp_id | time_loginout |
-------- ---------------------
| 111 | 2014-01-01 09:40:00 |
| 111 | 2014-01-01 16:00:00 |
| 222 | 2014-01-01 09:45:00 |
| 222 | 2014-01-01 17:04:00 |
| 333 | 2014-01-01 09:10:00 |
| 333 | 2014-01-01 17:10:00 |
| 444 | 2014-01-01 09:32:00 |
| 444 | 2014-01-01 17:40:00 |
| 555 | 2014-01-01 09:40:00 |
| 555 | 2014-01-01 17:50:00 |
| 111 | 2014-01-02 09:21:00 |
| 111 | 2014-01-02 17:20:00 |
| 222 | 2014-01-02 09:11:00 |
| 222 | 2014-01-02 17:30:00 |
| 333 | 2014-01-02 09:10:00 |
| 333 | 2014-01-02 17:10:00 |
| 444 | 2014-01-02 09:18:00 |
| 444 | 2014-01-02 16:54:00 |
| 555 | 2014-01-02 09:14:00 |
| 555 | 2014-01-02 16:40:00 |
| 111 | 2014-01-03 09:13:00 |
| 111 | 2014-01-03 16:30:00 |
| 222 | 2014-01-03 09:12:00 |
| 222 | 2014-01-03 17:40:00 |
| 333 | 2014-01-03 09:11:00 |
| 333 | 2014-01-03 17:10:00 |
| 444 | 2014-01-03 09:10:00 |
| 444 | 2014-01-03 17:22:00 |
| 555 | 2014-01-03 09:18:00 |
| 555 | 2014-01-03 17:21:00 |
| 111 | 2014-01-04 09:20:00 |
| 111 | 2014-01-04 17:12:00 |
| 222 | 2014-01-04 09:30:00 |
| 222 | 2014-01-04 17:11:00 |
| 333 | 2014-01-04 09:43:00 |
| 333 | 2014-01-04 17:42:00 |
| 444 | 2014-01-04 09:32:00 |
| 444 | 2014-01-04 17:23:00 |
| 555 | 2014-01-04 09:21:00 |
| 555 | 2014-01-04 17:54:00 |
-------- ---------------------
40 rows in set (0.00 sec)
и календарь хранит данные следующим образом:
mysql> select * from calender;
---------------------
| time_loginout |
---------------------
| 2014-01-01 09:40:00 |
| 2014-01-01 16:00:00 |
| 2014-01-01 09:45:00 |
| 2014-01-01 17:04:00 |
| 2014-01-01 09:10:00 |
| 2014-01-01 17:10:00 |
| 2014-01-01 09:32:00 |
| 2014-01-01 17:40:00 |
| 2014-01-01 09:40:00 |
| 2014-01-01 17:50:00 |
| 2014-01-02 09:21:00 |
| 2014-01-02 17:20:00 |
| 2014-01-02 09:11:00 |
| 2014-01-02 17:30:00 |
| 2014-01-02 09:10:00 |
| 2014-01-02 17:10:00 |
| 2014-01-02 09:18:00 |
| 2014-01-02 16:54:00 |
| 2014-01-02 09:14:00 |
| 2014-01-02 16:40:00 |
| 2014-01-03 09:13:00 |
| 2014-01-03 16:30:00 |
| 2014-01-03 09:12:00 |
| 2014-01-03 17:40:00 |
| 2014-01-03 09:11:00 |
| 2014-01-03 17:10:00 |
| 2014-01-03 09:10:00 |
| 2014-01-03 17:22:00 |
| 2014-01-03 09:18:00 |
| 2014-01-03 17:21:00 |
| 2014-01-04 09:20:00 |
| 2014-01-04 17:12:00 |
| 2014-01-04 09:30:00 |
| 2014-01-04 17:11:00 |
| 2014-01-04 09:43:00 |
| 2014-01-04 17:42:00 |
| 2014-01-04 09:32:00 |
| 2014-01-04 17:23:00 |
| 2014-01-04 09:21:00 |
| 2014-01-04 17:54:00 |
---------------------
40 rows in set (0.00 sec
)
here calender table is extract of raw_test table:
i.e., i have created calender table as follows:
create table calender as (select time_loginout from raw_test);
and if i execute following query, am not getting expected output:
select r.emp_id,
max(case
when c.time_loginout >= '2014-01-01 09:00:00' and c.time_loginout <= '2014-01-01 09:15:00' then 'P'
when c.time_loginout > '2014-01-01 09:15:00' and c.time_loginout <= '2014-01-01 09:30:00' then 'L'
when c.time_loginout > '2014-01-01 09:30:00' and c.time_loginout <= '2014-01-01 10:00:00' then '1H'
when c.time_loginout > '2014-01-01 10:00:00' and c.time_loginout <= '2014-01-01 11:00:00' then '2H'
when c.time_loginout > '2014-01-01 11:00:00' and c.time_loginout <= '2014-01-01 12:00:00' then '3H'
when c.time_loginout > '2014-01-01 12:00:00' and c.time_loginout <= '2014-01-01 13:30:00' then 'HD'
when c.time_loginout > '2014-01-01 13:30:00' and c.time_loginout <= '2014-01-01 14:00:00' then 'HD'
when c.time_loginout > '2014-01-01 14:00:00' and c.time_loginout <= '2014-01-01 17:00:00' then 'FD'
else 'EO' end)'01',
max(case
when c.time_loginout >= '2014-01-02 09:00:00' and c.time_loginout <= '2014-01-02 09:15:00' then 'P'
when c.time_loginout > '2014-01-02 09:15:00' and c.time_loginout <= '2014-01-02 09:30:00' then 'L'
when c.time_loginout > '2014-01-02 09:30:00' and c.time_loginout <= '2014-01-02 10:00:00' then '1H'
when c.time_loginout > '2014-01-02 10:00:00' and c.time_loginout <= '2014-01-02 11:00:00' then '2H'
when c.time_loginout > '2014-01-02 11:00:00' and c.time_loginout <= '2014-01-02 12:00:00' then '3H'
when c.time_loginout > '2014-01-02 12:00:00' and c.time_loginout <= '2014-01-02 13:30:00' then 'HD'
when c.time_loginout > '2014-01-02 13:30:00' and c.time_loginout <= '2014-01-02 14:00:00' then 'HD'
when c.time_loginout > '2014-01-02 14:00:00' and c.time_loginout <= '2014-01-02 17:00:00' then 'FD'
else 'EO' end)'02',
max(case
when c.time_loginout >= '2014-01-03 09:00:00' and c.time_loginout <= '2014-01-03 09:15:00' then 'P'
when c.time_loginout > '2014-01-03 09:15:00' and c.time_loginout <= '2014-01-03 09:30:00' then 'L'
when c.time_loginout > '2014-01-03 09:30:00' and c.time_loginout <= '2014-01-03 10:00:00' then '1H'
when c.time_loginout > '2014-01-03 10:00:00' and c.time_loginout <= '2014-01-03 11:00:00' then '2H'
when c.time_loginout > '2014-01-03 11:00:00' and c.time_loginout <= '2014-01-03 12:00:00' then '3H'
when c.time_loginout > '2014-01-03 12:00:00' and c.time_loginout <= '2014-01-03 13:30:00' then 'HD'
when c.time_loginout > '2014-01-03 13:30:00' and c.time_loginout <= '2014-01-03 14:00:00' then 'HD'
when c.time_loginout > '2014-01-03 14:00:00' and c.time_loginout <= '2014-01-03 17:00:00' then 'FD'
else 'EO' end)'03',
max(case
when c.time_loginout >= '2014-01-04 09:00:00' and c.time_loginout <= '2014-01-04 09:15:00' then 'P'
when c.time_loginout > '2014-01-04 09:15:00' and c.time_loginout <= '2014-01-04 09:30:00' then 'L'
when c.time_loginout > '2014-01-04 09:30:00' and c.time_loginout <= '2014-01-04 10:00:00' then '1H'
when c.time_loginout > '2014-01-04 10:00:00' and c.time_loginout <= '2014-01-04 11:00:00' then '2H'
when c.time_loginout > '2014-01-04 11:00:00' and c.time_loginout <= '2014-01-04 12:00:00' then '3H'
when c.time_loginout > '2014-01-04 12:00:00' and c.time_loginout <= '2014-01-04 13:30:00' then 'HD'
when c.time_loginout > '2014-01-04 13:30:00' and c.time_loginout <= '2014-01-04 14:00:00' then 'HD'
when c.time_loginout > '2014-01-04 14:00:00' and c.time_loginout <= '2014-01-04 17:00:00' then 'FD'
else 'EO' end)'04'
from raw_test r left join calender c on r.time_loginout=c.time_loginout group by r.emp_id;
вывод приведенного выше запроса:
-------- ------ ------ ------ ------
| emp_id | 01 | 02 | 03 | 04 |
-------- ------ ------ ------ ------
| 111 | FD | L | P | L |
| 222 | EO | P | P | L |
| 333 | P | P | P | EO |
| 444 | EO | L | P | EO |
| 555 | EO | P | L | L |
-------- ------ ------ ------ ------
5 rows in set (0.00 sec)
на самом деле я ищу, чтобы каждый emp_id мог быть одним из следующих:
P - present
L - late
EO - eary out
1H - 1 hour late
2H - 2 hour late
3H - 3 hour late
HD - half day late
FD - full day late
но emp_id 111 пробит в 9: 40, и он показывает FD, который должен показывать 1 час.
Пожалуйста, помогите мне в этом отношении. Благодарю вас.
Комментарии:
1. emp_id 111 также был пробит в 2014-01-01 16:00:00. Следовательно, оператор CASE для столбца ’01’ также вернул бы FD. Поскольку для оператора CASE выполняется МАКСИМАЛЬНОЕ значение, возвращается FD (а не 1H).
Ответ №1:
Если вы ищете только время проверки, вам нужно минимальное, а не максимальное. Также я не вижу необходимости в вашем втором табличном календаре. В этом примере, похоже, он ничего не делает. Попробуйте это:
select emp_id,
MIN(case
when time_loginout >= '2014-01-01 09:00:00' and time_loginout <= '2014-01-01 09:15:00' then 'P'
when time_loginout > '2014-01-01 09:15:00' and time_loginout <= '2014-01-01 09:30:00' then 'L'
when time_loginout > '2014-01-01 09:30:00' and time_loginout <= '2014-01-01 10:00:00' then '1H'
when time_loginout > '2014-01-01 10:00:00' and time_loginout <= '2014-01-01 11:00:00' then '2H'
when time_loginout > '2014-01-01 11:00:00' and time_loginout <= '2014-01-01 12:00:00' then '3H'
when time_loginout > '2014-01-01 12:00:00' and time_loginout <= '2014-01-01 13:30:00' then 'HD'
when time_loginout > '2014-01-01 13:30:00' and time_loginout <= '2014-01-01 14:00:00' then 'HD'
when time_loginout > '2014-01-01 14:00:00' and time_loginout <= '2014-01-01 17:00:00' then 'FD'
else 'EO' end)'01',
MIN(case
when time_loginout >= '2014-01-02 09:00:00' and time_loginout <= '2014-01-02 09:15:00' then 'P'
when time_loginout > '2014-01-02 09:15:00' and time_loginout <= '2014-01-02 09:30:00' then 'L'
when time_loginout > '2014-01-02 09:30:00' and time_loginout <= '2014-01-02 10:00:00' then '1H'
when time_loginout > '2014-01-02 10:00:00' and time_loginout <= '2014-01-02 11:00:00' then '2H'
when time_loginout > '2014-01-02 11:00:00' and time_loginout <= '2014-01-02 12:00:00' then '3H'
when time_loginout > '2014-01-02 12:00:00' and time_loginout <= '2014-01-02 13:30:00' then 'HD'
when time_loginout > '2014-01-02 13:30:00' and time_loginout <= '2014-01-02 14:00:00' then 'HD'
when time_loginout > '2014-01-02 14:00:00' and time_loginout <= '2014-01-02 17:00:00' then 'FD'
else 'EO' end)'02',
MIN(case
when time_loginout >= '2014-01-03 09:00:00' and time_loginout <= '2014-01-03 09:15:00' then 'P'
when time_loginout > '2014-01-03 09:15:00' and time_loginout <= '2014-01-03 09:30:00' then 'L'
when time_loginout > '2014-01-03 09:30:00' and time_loginout <= '2014-01-03 10:00:00' then '1H'
when time_loginout > '2014-01-03 10:00:00' and time_loginout <= '2014-01-03 11:00:00' then '2H'
when time_loginout > '2014-01-03 11:00:00' and time_loginout <= '2014-01-03 12:00:00' then '3H'
when time_loginout > '2014-01-03 12:00:00' and time_loginout <= '2014-01-03 13:30:00' then 'HD'
when time_loginout > '2014-01-03 13:30:00' and time_loginout <= '2014-01-03 14:00:00' then 'HD'
when time_loginout > '2014-01-03 14:00:00' and time_loginout <= '2014-01-03 17:00:00' then 'FD'
else 'EO' end)'03',
MIN(case
when time_loginout >= '2014-01-04 09:00:00' and time_loginout <= '2014-01-04 09:15:00' then 'P'
when time_loginout > '2014-01-04 09:15:00' and time_loginout <= '2014-01-04 09:30:00' then 'L'
when time_loginout > '2014-01-04 09:30:00' and time_loginout <= '2014-01-04 10:00:00' then '1H'
when time_loginout > '2014-01-04 10:00:00' and time_loginout <= '2014-01-04 11:00:00' then '2H'
when time_loginout > '2014-01-04 11:00:00' and time_loginout <= '2014-01-04 12:00:00' then '3H'
when time_loginout > '2014-01-04 12:00:00' and time_loginout <= '2014-01-04 13:30:00' then 'HD'
when time_loginout > '2014-01-04 13:30:00' and time_loginout <= '2014-01-04 14:00:00' then 'HD'
when time_loginout > '2014-01-04 14:00:00' and time_loginout <= '2014-01-04 17:00:00' then 'FD'
else 'EO' end)'04'
from raw_test group by emp_id;