데이터베이스 질문과 답변

쿼리문 재질문드립니다 0 4 449

by NNine [SQL Query] mssql [2020.07.01 17:56:32]

20200701164922.png (60,650Bytes)

안녕하세요 http://gurubee.net/article/83171 이질문에 설명이 잘못된것 같아 재질문드립니다 

 

by 마농 [2020.07.01 18:23:18] 
WITH bus_t AS
(
SELECT 1 bus_id, 41 seating_capacity
)
, schedule_t AS
(
SELECT 87 schedule_id, 1 bus_id
)
, passenger_t AS
(
SELECT 1 passenger_id, 87 schedule_id, 1 seat_number
UNION ALL SELECT 2, 87, 3
)
, donation_t AS
(
SELECT 1 donation_id, 1 bus_id
)
, bus_tmp AS
(
SELECT a.donation_id
     , b.bus_id
     , b.seating_capacity
     , 1 seat_number
  FROM donation_t a
 INNER JOIN bus_t b
    ON a.bus_id = b.bus_id
 WHERE a.donation_id = 1
 UNION ALL
SELECT donation_id
     , bus_id
     , seating_capacity
     , seat_number + 1
  FROM bus_tmp
 WHERE seat_number < seating_capacity
)
SELECT a.*
  FROM bus_tmp a
  LEFT OUTER JOIN schedule_t b
    ON a.bus_id = b.bus_id
  LEFT OUTER JOIN passenger_t c
    ON b.schedule_id = c.schedule_id
   AND a.seat_number = c.seat_number
 WHERE c.seat_number IS NULL
;

 

by NNine [2020.07.01 20:41:57]

답변 감사합니다!

declare @Seat_capacity int
set @Seat_capacity = 20;

WITH numbers AS (
SELECT 1 AS num, @Seat_capacity AS maxnum
UNION ALL
SELECT num + 1, maxnum FROM numbers WHERE num < maxnum
)
SELECT n.num AS 남은번호
FROM numbers n
LEFT OUTER JOIN Passenger ON Passenger.Seat_Number = n.num
WHERE Passenger.Seat_Number IS NULL

이런 느낌으로 만들어봤는데 문제가 이렇게 쓰면 Schedule_ID에 상관없이 겹쳐버려서 여기에 Schedule_ID에따라 결과값이 나오게 가능할까요? 예를 들어 선택한 Schedule_ID값이 87일경우 그것에 해당하는 승객들만 선택되게요

by 마농 [2020.07.02 01:29:02]

위의 예는 특정 donation_id 를 입력 받아 처리하는 예입니다.
아래 예는 특정 schedule_id 를 입력 받아 처리하는 예입니다.
 

WITH bus_t AS
(
SELECT 1 bus_id, 41 seating_capacity
)
, schedule_t AS
(
SELECT 87 schedule_id, 1 bus_id
)
, passenger_t AS
(
SELECT 1 passenger_id, 87 schedule_id, 1 seat_number
UNION ALL SELECT 2, 87, 3
)
, donation_t AS
(
SELECT 1 donation_id, 1 bus_id
)
, bus_tmp AS
(
SELECT a.schedule_id
     , b.bus_id
     , b.seating_capacity
     , 1 seat_number
  FROM schedule_t a
 INNER JOIN bus_t b
    ON a.bus_id = b.bus_id
 WHERE a.schedule_id = 87
 UNION ALL
SELECT schedule_id
     , bus_id
     , seating_capacity
     , seat_number + 1
  FROM bus_tmp
 WHERE seat_number < seating_capacity
)
SELECT a.*
  FROM bus_tmp a
  LEFT OUTER JOIN passenger_t b
    ON a.schedule_id = b.schedule_id
   AND a.seat_number = b.seat_number
 WHERE b.seat_number IS NULL
;

 

by NNine [2020.07.02 15:19:17]

감사합니다 덕분에 해결했습니다! 설명도 많이 부족했는데 답변해주셔서 감사합니다

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입