원본 데이터
더보기
| history_id | car_id | start_date | end_date |
| 506 | 15 | 2022-08-01 00:00:00 | 2022-08-02 00:00:00 |
| 507 | 26 | 2022-08-01 00:00:00 | 2022-11-09 00:00:00 |
| 508 | 27 | 2022-08-01 00:00:00 | 2022-08-02 00:00:00 |
| 510 | 29 | 2022-08-02 00:00:00 | 2022-08-05 00:00:00 |
| 511 | 13 | 2022-08-03 00:00:00 | 2022-08-07 00:00:00 |
| 512 | 27 | 2022-08-03 00:00:00 | 2022-08-04 00:00:00 |
| 513 | 28 | 2022-08-03 00:00:00 | 2022-08-03 00:00:00 |
| 517 | 2 | 2022-08-05 00:00:00 | 2022-08-07 00:00:00 |
| 518 | 5 | 2022-08-05 00:00:00 | 2022-08-08 00:00:00 |
| 519 | 23 | 2022-08-05 00:00:00 | 2022-08-06 00:00:00 |
| 520 | 25 | 2022-08-05 00:00:00 | 2022-08-07 00:00:00 |
| 521 | 27 | 2022-08-05 00:00:00 | 2022-08-06 00:00:00 |
| 523 | 7 | 2022-08-06 00:00:00 | 2022-08-08 00:00:00 |
| 524 | 8 | 2022-08-06 00:00:00 | 2022-08-06 00:00:00 |
| 527 | 8 | 2022-08-07 00:00:00 | 2022-08-11 00:00:00 |
| 528 | 19 | 2022-08-07 00:00:00 | 2022-09-16 00:00:00 |
| 529 | 28 | 2022-08-07 00:00:00 | 2022-08-21 00:00:00 |
| 530 | 29 | 2022-08-07 00:00:00 | 2022-08-11 00:00:00 |
| 531 | 23 | 2022-08-08 00:00:00 | 2022-08-21 00:00:00 |
| 532 | 25 | 2022-08-08 00:00:00 | 2022-08-09 00:00:00 |
| 534 | 2 | 2022-08-09 00:00:00 | 2022-08-09 00:00:00 |
| 535 | 5 | 2022-08-09 00:00:00 | 2022-08-09 00:00:00 |
| 536 | 27 | 2022-08-09 00:00:00 | 2022-08-16 00:00:00 |
| 537 | 2 | 2022-08-10 00:00:00 | 2022-08-20 00:00:00 |
| 538 | 5 | 2022-08-10 00:00:00 | 2022-08-10 00:00:00 |
| 539 | 9 | 2022-08-10 00:00:00 | 2022-08-10 00:00:00 |
| 540 | 15 | 2022-08-10 00:00:00 | 2022-09-19 00:00:00 |
| 542 | 25 | 2022-08-11 00:00:00 | 2022-08-12 00:00:00 |
| 545 | 5 | 2022-08-12 00:00:00 | 2022-09-21 00:00:00 |
| 546 | 8 | 2022-08-12 00:00:00 | 2022-08-13 00:00:00 |
| 547 | 13 | 2022-08-12 00:00:00 | 2022-08-14 00:00:00 |
| 548 | 16 | 2022-08-13 00:00:00 | 2022-08-13 00:00:00 |
| 549 | 29 | 2022-08-13 00:00:00 | 2022-08-13 00:00:00 |
| 551 | 7 | 2022-08-14 00:00:00 | 2022-08-15 00:00:00 |
| 552 | 25 | 2022-08-14 00:00:00 | 2022-08-14 00:00:00 |
| 556 | 8 | 2022-08-16 00:00:00 | 2022-08-23 00:00:00 |
| 558 | 6 | 2022-08-17 00:00:00 | 2022-09-21 00:00:00 |
| 559 | 13 | 2022-08-17 00:00:00 | 2022-08-19 00:00:00 |
| 560 | 16 | 2022-08-17 00:00:00 | 2022-11-15 00:00:00 |
| 561 | 25 | 2022-08-17 00:00:00 | 2022-08-27 00:00:00 |
| 562 | 9 | 2022-08-18 00:00:00 | 2022-08-20 00:00:00 |
| 564 | 7 | 2022-08-19 00:00:00 | 2022-08-22 00:00:00 |
| 567 | 10 | 2022-08-20 00:00:00 | 2022-08-21 00:00:00 |
| 568 | 2 | 2022-08-21 00:00:00 | 2022-08-23 00:00:00 |
| 569 | 29 | 2022-08-21 00:00:00 | 2022-11-29 00:00:00 |
| 571 | 9 | 2022-08-22 00:00:00 | 2022-10-01 00:00:00 |
| 573 | 7 | 2022-08-23 00:00:00 | 2022-09-22 00:00:00 |
| 574 | 13 | 2022-08-23 00:00:00 | 2022-08-24 00:00:00 |
| 575 | 2 | 2022-08-24 00:00:00 | 2022-08-28 00:00:00 |
| 576 | 27 | 2022-08-24 00:00:00 | 2022-09-23 00:00:00 |
| 577 | 10 | 2022-08-25 00:00:00 | 2022-08-26 00:00:00 |
| 578 | 23 | 2022-08-25 00:00:00 | 2022-09-01 00:00:00 |
| 579 | 28 | 2022-08-25 00:00:00 | 2022-09-08 00:00:00 |
| 581 | 8 | 2022-08-26 00:00:00 | 2022-08-27 00:00:00 |
| 582 | 10 | 2022-08-27 00:00:00 | 2022-08-29 00:00:00 |
| 583 | 13 | 2022-08-27 00:00:00 | 2022-09-06 00:00:00 |
| 586 | 8 | 2022-08-29 00:00:00 | 2022-08-30 00:00:00 |
| 587 | 11 | 2022-08-29 00:00:00 | 2022-08-29 00:00:00 |
| 591 | 8 | 2022-09-01 00:00:00 | 2022-09-11 00:00:00 |
| 592 | 10 | 2022-09-01 00:00:00 | 2022-09-02 00:00:00 |
| 593 | 25 | 2022-09-01 00:00:00 | 2022-09-03 00:00:00 |
| 594 | 12 | 2022-09-02 00:00:00 | 2022-09-15 00:00:00 |
| 595 | 10 | 2022-09-03 00:00:00 | 2022-09-18 00:00:00 |
| 596 | 11 | 2022-09-04 00:00:00 | 2022-09-04 00:00:00 |
| 597 | 23 | 2022-09-04 00:00:00 | 2022-09-07 00:00:00 |
| 601 | 2 | 2022-09-05 00:00:00 | 2022-10-05 00:00:00 |
| 602 | 20 | 2022-09-05 00:00:00 | 2022-09-06 00:00:00 |
| 603 | 18 | 2022-09-06 00:00:00 | 2022-09-07 00:00:00 |
| 604 | 22 | 2022-09-06 00:00:00 | 2022-09-06 00:00:00 |
| 605 | 25 | 2022-09-06 00:00:00 | 2022-09-09 00:00:00 |
| 606 | 22 | 2022-09-07 00:00:00 | 2022-12-06 00:00:00 |
| 607 | 24 | 2022-09-08 00:00:00 | 2022-09-11 00:00:00 |
| 609 | 18 | 2022-09-09 00:00:00 | 2022-09-12 00:00:00 |
| 610 | 20 | 2022-09-09 00:00:00 | 2022-09-12 00:00:00 |
| 611 | 13 | 2022-09-10 00:00:00 | 2022-09-11 00:00:00 |
| 613 | 4 | 2022-09-11 00:00:00 | 2022-10-21 00:00:00 |
| 614 | 28 | 2022-09-11 00:00:00 | 2022-09-12 00:00:00 |
| 615 | 11 | 2022-09-12 00:00:00 | 2022-09-13 00:00:00 |
| 616 | 25 | 2022-09-12 00:00:00 | 2022-09-14 00:00:00 |
| 618 | 8 | 2022-09-13 00:00:00 | 2022-09-14 00:00:00 |
| 619 | 18 | 2022-09-13 00:00:00 | 2022-09-15 00:00:00 |
| 620 | 28 | 2022-09-13 00:00:00 | 2022-12-22 00:00:00 |
| 621 | 11 | 2022-09-14 00:00:00 | 2022-09-16 00:00:00 |
| 622 | 13 | 2022-09-14 00:00:00 | 2022-09-21 00:00:00 |
| 623 | 20 | 2022-09-14 00:00:00 | 2022-09-15 00:00:00 |
| 624 | 24 | 2022-09-14 00:00:00 | 2022-09-14 00:00:00 |
| 626 | 23 | 2022-09-15 00:00:00 | 2022-09-16 00:00:00 |
| 627 | 8 | 2022-09-16 00:00:00 | 2022-09-16 00:00:00 |
| 628 | 12 | 2022-09-16 00:00:00 | 2022-09-18 00:00:00 |
| 629 | 18 | 2022-09-16 00:00:00 | 2022-10-16 00:00:00 |
| 630 | 20 | 2022-09-16 00:00:00 | 2022-10-16 00:00:00 |
| 631 | 8 | 2022-09-17 00:00:00 | 2022-09-19 00:00:00 |
| 632 | 11 | 2022-09-17 00:00:00 | 2022-09-18 00:00:00 |
| 634 | 19 | 2022-09-18 00:00:00 | 2022-09-20 00:00:00 |
| 635 | 24 | 2022-09-18 00:00:00 | 2022-10-28 00:00:00 |
| 637 | 11 | 2022-09-19 00:00:00 | 2022-09-19 00:00:00 |
| 638 | 15 | 2022-09-20 00:00:00 | 2022-09-23 00:00:00 |
| 639 | 25 | 2022-09-20 00:00:00 | 2022-09-21 00:00:00 |
| 640 | 8 | 2022-09-21 00:00:00 | 2022-09-24 00:00:00 |
| 641 | 12 | 2022-09-21 00:00:00 | 2022-09-23 00:00:00 |
| 642 | 19 | 2022-09-21 00:00:00 | 2022-09-23 00:00:00 |
| 645 | 10 | 2022-09-22 00:00:00 | 2022-09-23 00:00:00 |
| 646 | 23 | 2022-09-22 00:00:00 | 2022-09-24 00:00:00 |
| 648 | 5 | 2022-09-23 00:00:00 | 2022-11-02 00:00:00 |
| 649 | 7 | 2022-09-23 00:00:00 | 2022-10-06 00:00:00 |
| 650 | 13 | 2022-09-23 00:00:00 | 2022-09-24 00:00:00 |
| 653 | 6 | 2022-09-24 00:00:00 | 2022-10-24 00:00:00 |
| 654 | 12 | 2022-09-24 00:00:00 | 2022-09-25 00:00:00 |
| 656 | 10 | 2022-09-25 00:00:00 | 2022-09-25 00:00:00 |
| 657 | 11 | 2022-09-25 00:00:00 | 2023-01-03 00:00:00 |
| 658 | 21 | 2022-09-25 00:00:00 | 2022-09-26 00:00:00 |
| 659 | 25 | 2022-09-25 00:00:00 | 2022-09-27 00:00:00 |
| 660 | 27 | 2022-09-25 00:00:00 | 2022-12-24 00:00:00 |
| 663 | 15 | 2022-09-26 00:00:00 | 2022-09-26 00:00:00 |
| 664 | 19 | 2022-09-26 00:00:00 | 2022-10-03 00:00:00 |
| 665 | 23 | 2022-09-26 00:00:00 | 2022-10-06 00:00:00 |
| 667 | 10 | 2022-09-28 00:00:00 | 2022-10-12 00:00:00 |
| 668 | 13 | 2022-09-28 00:00:00 | 2022-09-28 00:00:00 |
| 669 | 25 | 2022-09-29 00:00:00 | 2022-10-28 00:00:00 |
| 671 | 15 | 2022-10-01 00:00:00 | 2022-10-14 00:00:00 |
| 672 | 21 | 2022-10-01 00:00:00 | 2022-10-01 00:00:00 |
| 673 | 8 | 2022-10-02 00:00:00 | 2022-10-04 00:00:00 |
| 674 | 17 | 2022-10-02 00:00:00 | 2022-11-06 00:00:00 |
| 675 | 9 | 2022-10-03 00:00:00 | 2023-01-04 00:00:00 |
| 676 | 12 | 2022-10-03 00:00:00 | 2022-10-06 00:00:00 |
| 678 | 19 | 2022-10-05 00:00:00 | 2022-11-14 00:00:00 |
| 679 | 13 | 2022-10-06 00:00:00 | 2022-10-06 00:00:00 |
| 680 | 8 | 2022-10-07 00:00:00 | 2022-10-21 00:00:00 |
| 681 | 12 | 2022-10-07 00:00:00 | 2022-11-16 00:00:00 |
| 682 | 21 | 2022-10-07 00:00:00 | 2022-10-09 00:00:00 |
| 685 | 2 | 2022-10-10 00:00:00 | 2023-01-11 00:00:00 |
| 687 | 7 | 2022-10-14 00:00:00 | 2022-11-23 00:00:00 |
| 688 | 13 | 2022-10-14 00:00:00 | 2022-10-15 00:00:00 |
| 689 | 23 | 2022-10-14 00:00:00 | 2022-10-27 00:00:00 |
| 691 | 21 | 2022-10-17 00:00:00 | 2022-10-17 00:00:00 |
| 692 | 10 | 2022-10-18 00:00:00 | 2022-10-18 00:00:00 |
| 694 | 18 | 2022-10-19 00:00:00 | 2022-10-19 00:00:00 |
| 695 | 21 | 2022-10-19 00:00:00 | 2022-10-26 00:00:00 |
| 697 | 10 | 2022-10-20 00:00:00 | 2022-10-23 00:00:00 |
| 699 | 3 | 2022-10-21 00:00:00 | 2022-10-24 00:00:00 |
| 700 | 18 | 2022-10-21 00:00:00 | 2022-10-22 00:00:00 |
| 701 | 20 | 2022-10-21 00:00:00 | 2022-10-23 00:00:00 |
| 702 | 15 | 2022-10-22 00:00:00 | 2022-11-06 00:00:00 |
| 704 | 4 | 2022-10-23 00:00:00 | 2022-10-26 00:00:00 |
| 705 | 8 | 2022-10-23 00:00:00 | 2022-10-23 00:00:00 |
| 706 | 13 | 2022-10-23 00:00:00 | 2022-10-26 00:00:00 |
| 707 | 10 | 2022-10-24 00:00:00 | 2023-01-25 00:00:00 |
| 710 | 6 | 2022-10-25 00:00:00 | 2022-10-28 00:00:00 |
| 711 | 8 | 2022-10-25 00:00:00 | 2022-10-25 00:00:00 |
| 712 | 18 | 2022-10-25 00:00:00 | 2023-01-26 00:00:00 |
| 713 | 3 | 2022-10-27 00:00:00 | 2022-10-28 00:00:00 |
| 714 | 8 | 2022-10-27 00:00:00 | 2022-11-06 00:00:00 |
| 715 | 13 | 2022-10-27 00:00:00 | 2022-10-27 00:00:00 |
| 716 | 30 | 2022-10-27 00:00:00 | 2022-10-27 00:00:00 |
| 718 | 13 | 2022-10-28 00:00:00 | 2022-10-30 00:00:00 |
| 720 | 6 | 2022-10-30 00:00:00 | 2022-11-02 00:00:00 |
| 721 | 21 | 2022-10-30 00:00:00 | 2022-11-02 00:00:00 |
| 722 | 1 | 2022-10-31 00:00:00 | 2022-11-30 00:00:00 |
| 723 | 4 | 2022-10-31 00:00:00 | 2022-11-01 00:00:00 |
| 724 | 20 | 2022-10-31 00:00:00 | 2022-12-10 00:00:00 |
[문제] CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 2022년 10월 16일에 대여 중인 자동차인 경우 '대여중' 이라고 표시하고, 대여 중이지 않은 자동차인 경우 '대여 가능'을 표시하는 컬럼(컬럼명: AVAILABILITY)을 추가하여 자동차 ID와 AVAILABILITY 리스트를 출력하는 SQL문을 작성해주세요. 이때 반납 날짜가 2022년 10월 16일인 경우에도 '대여중'으로 표시해주시고 결과는 자동차 ID를 기준으로 내림차순 정렬해주세요.
select t1.car_id,
if(t2.car_id is null, '대여 가능', '대여중') as AVAILABILITY
from CAR_RENTAL_COMPANY_RENTAL_HISTORY t1
left join
(select car_id from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where left(start_date, 10) <= '2022-10-16' and left(end_date, 10) >= '2022-10-16') as t2
on t1.car_id = t2.car_id
group by t1.car_id
order by t1.car_id desc
레프트 조인이 모든 것을 해결해 줄 지어니...처음에는 뭐야 그냥 대여중인지 아닌지만 표시해달라는건가ㅋ 했는데 잘 읽어보니 차량별로 현재(문제 기준 2022년 10월 16일) 대여가 가능한 상태인지 조회해 달라는 얘기. 여기서 '별로'라는 조사가 들어가면 group by가 자동으로 따라와야 한다.
일단 지금 대여중인 차량을 조회해보자. order by는 본인 편의상 넣은 것이다.
select car_id from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where left(start_date, 10) <= '2022-10-16' and left(end_date, 10) >= '2022-10-16'
order by car_id, start_date
위와 같이 조회하면 현재 대여중인 차량이 조회된다.
여기서부터는 얘기가 쉬워진다. 그럼 위 테이블을 레프트 조인으로 붙인 담에 car_id가 빈값인 차량은 대여 가능 상태겠군?
물론 그냥 붙이면 안되고 원본 테이블을 한 번 걸러준다. 어떻게? '차량별로'이므로 차량이 종류대로 나올 수 있도록 group by 로 묶는다.
select * from CAR_RENTAL_COMPANY_RENTAL_HISTORY
group by car_id