다음과 같은 사용자의 체크인 및 체크 아웃 시간 테이블 ( “lms_attendance”)이 있습니다.
id user time io (enum)
1 9 1370931202 out
2 9 1370931664 out
3 6 1370932128 out
4 12 1370932128 out
5 12 1370933037 in“in”또는 “out”값을 제공하면서 사용자 ID 당 가장 최근 레코드 만 출력하는이 테이블의보기를 만들려고합니다.
id user time io
2 9 1370931664 out
3 6 1370932128 out
5 12 1370933037 in지금까지는 꽤 가깝지만 뷰가 하위 쿼리를 허용하지 않아 훨씬 더 어려워진다는 것을 깨달았습니다. 내가 얻은 가장 가까운 쿼리는 다음과 같습니다.
select
`lms_attendance`.`id` AS `id`,
`lms_attendance`.`user` AS `user`,
max(`lms_attendance`.`time`) AS `time`,
`lms_attendance`.`io` AS `io`
from `lms_attendance`
group by
`lms_attendance`.`user`,
`lms_attendance`.`io`그러나 내가 얻는 것은 :
id user time io
3 6 1370932128 out
1 9 1370931664 out
5 12 1370933037 in
4 12 1370932128 out가깝지만 완벽하지는 않습니다. 마지막 그룹이 거기에 있으면 안된다는 것을 알고 있지만 그것이 없으면 가장 최근 시간을 반환하지만 상대 IO 값은 반환하지 않습니다.
어떤 아이디어? 감사!
답변
질문:
SELECT t1.*
FROM lms_attendance t1
WHERE t1.time = (SELECT MAX(t2.time)
FROM lms_attendance t2
WHERE t2.user = t1.user)결과:
| ID | USER | TIME | IO |
--------------------------------
| 2 | 9 | 1370931664 | out |
| 3 | 6 | 1370932128 | out |
| 5 | 12 | 1370933037 | in |매번 작동하는 솔루션 :
SELECT t1.*
FROM lms_attendance t1
WHERE t1.id = (SELECT t2.id
FROM lms_attendance t2
WHERE t2.user = t1.user
ORDER BY t2.id DESC
LIMIT 1)답변
이것은 그룹당 가장 큰 문제 이기 때문에 바퀴를 재발 명 할 필요가 없습니다 . 아주 좋은 해결책이 제시 됩니다.
하위 쿼리가없는 가장 단순한 솔루션 ( SQLFiddle, 업데이트 된 Justin ‘s 참조)을 선호합니다 (따라서 뷰에서 사용하기 쉽습니다).
SELECT t1.*
FROM lms_attendance AS t1
LEFT OUTER JOIN lms_attendance AS t2
ON t1.user = t2.user
AND (t1.time < t2.time
OR (t1.time = t2.time AND t1.Id < t2.Id))
WHERE t2.user IS NULL이것은 또한 동일한 그룹 내에서 동일한 가장 큰 값을 가진 두 개의 다른 레코드가있는 경우에도 작동합니다 (t1.time = t2.time AND t1.Id < t2.Id). 내가 여기서하는 일은 동일한 사용자의 두 레코드가 같은 시간을 가질 때 하나만 선택되도록하는 것입니다. 기준이 Id다른 것인지 여부는 실제로 중요하지 않습니다. 기본적으로 고유하다고 보장되는 모든 기준이 여기서 작동합니다.
답변
@TMS 답변을 기반으로 하위 쿼리가 필요하지 않기 때문에 좋아하지만 'OR'부분을 생략하면 이해하고 읽기가 훨씬 간단하고 충분할 것이라고 생각합니다 .
SELECT t1.*
FROM lms_attendance AS t1
LEFT JOIN lms_attendance AS t2
ON t1.user = t2.user
AND t1.time < t2.time
WHERE t2.user IS NULLnull 시간이있는 행에 관심이없는 경우 WHERE절 에서 필터링 할 수 있습니다 .
SELECT t1.*
FROM lms_attendance AS t1
LEFT JOIN lms_attendance AS t2
ON t1.user = t2.user
AND t1.time < t2.time
WHERE t2.user IS NULL and t1.time IS NOT NULL답변
이미 해결되었지만 기록을 위해 또 다른 접근 방식은 두 개의 뷰를 만드는 것입니다.
CREATE TABLE lms_attendance
(id int, user int, time int, io varchar(3));
CREATE VIEW latest_all AS
SELECT la.user, max(la.time) time
FROM lms_attendance la
GROUP BY la.user;
CREATE VIEW latest_io AS
SELECT la.*
FROM lms_attendance la
JOIN latest_all lall
ON lall.user = la.user
AND lall.time = la.time;
INSERT INTO lms_attendance
VALUES
(1, 9, 1370931202, 'out'),
(2, 9, 1370931664, 'out'),
(3, 6, 1370932128, 'out'),
(4, 12, 1370932128, 'out'),
(5, 12, 1370933037, 'in');
SELECT * FROM latest_io;답변
select b.* from
(select
`lms_attendance`.`user` AS `user`,
max(`lms_attendance`.`time`) AS `time`
from `lms_attendance`
group by
`lms_attendance`.`user`) a
join
(select *
from `lms_attendance` ) b
on a.user = b.user
and a.time = b.time답변
select result from (
select vorsteuerid as result, count(*) as anzahl from kreditorenrechnung where kundeid = 7148
group by vorsteuerid
) a order by anzahl desc limit 0,1답변
MySQL 8.0 이상에서는 Window 함수를 사용할 수 있습니다 .
질문:
SELECT DISTINCT
FIRST_VALUE(ID) OVER (PARTITION BY lms_attendance.USER ORDER BY lms_attendance.TIME DESC) AS ID,
FIRST_VALUE(USER) OVER (PARTITION BY lms_attendance.USER ORDER BY lms_attendance.TIME DESC) AS USER,
FIRST_VALUE(TIME) OVER (PARTITION BY lms_attendance.USER ORDER BY lms_attendance.TIME DESC) AS TIME,
FIRST_VALUE(IO) OVER (PARTITION BY lms_attendance.USER ORDER BY lms_attendance.TIME DESC) AS IO
FROM lms_attendance;결과:
| ID | USER | TIME | IO |
--------------------------------
| 2 | 9 | 1370931664 | out |
| 3 | 6 | 1370932128 | out |
| 5 | 12 | 1370933037 | in |Justin 이 제안한 솔루션 을 사용하는 것 보다 내가 보는 이점 은 중간 뷰나 테이블 없이도 하위 쿼리에서도 사용자 별 (또는 ID 별 또는 기타별로) 최신 데이터가있는 행을 선택할 수 있다는 것입니다.
그리고 HANA를 실행하는 경우에도 ~ 7 배 더 빠릅니다 : D
