by 무시기 [SQL Query] [2021.03.06 12:30:45]
어떻게 설명을 해야 될지 참 난감하지만 질문 드립니다.
A와 B테이블이 있습니다.
ID로 검색 해서 나중의 데이터가 있는 테이블의 값을 구하려고 하는데 도저희 모르겠어서 여쭤봅니다.
예를 들어
A Table
머니 | 날짜 | 아이디 |
10000 | 2021-03-06 12:00:00 | aaa |
10000 | 2021-03-06 11:00:00 | bbb |
10000 | 2021-03-06 09:00:00 | aaa |
10000 | 2021-03-06 08:00:00 | bbb |
B Table
Money | dateTime | 아이디 |
5000 | 2021-03-06 11:00:00 | aaa |
5000 | 2021-03-06 11:50:00 | bbb |
5000 | 2021-03-06 10:00:00 | aaa |
5000 | 2021-03-06 07:00:00 | bbb |
와 같은 경우 조건 아이디를 aaa로 했을 경우 값은 A테이블의 맨 상단머니
bbb의 값은 B테이블의 두번째행 을 구하려고 합니다.
이럴 때는 쿼리를 어떻게 짜야 가능할까요?
알려주시면 정말 감사드리겠습니다. ㅠㅠ
요런 결과를 의미하시는건지?? WITH A_table AS ( SELECT 10000 AS tmoney, '2021-03-06 12:00:00' AS tdate, 'aaa' AS tid UNION ALL SELECT 10000 AS tmoney, '2021-03-06 11:00:00' AS tdate, 'bbb' AS tid UNION ALL SELECT 10000 AS tmoney, '2021-03-06 09:00:00' AS tdate, 'aaa' AS tid UNION ALL SELECT 10000 AS tmoney, '2021-03-06 08:00:00' AS tdate, 'bbb' AS tid ), B_table AS ( SELECT 5000 AS tmoney, '2021-03-06 11:00:00' AS tdate, 'aaa' AS tid UNION ALL SELECT 5000 AS tmoney, '2021-03-06 11:50:00' AS tdate, 'bbb' AS tid UNION ALL SELECT 5000 AS tmoney, '2021-03-06 10:00:00' AS tdate, 'aaa' AS tid UNION ALL SELECT 5000 AS tmoney, '2021-03-06 07:00:00' AS tdate, 'bbb' AS tid ) SELECT tbl, tmoney, tdate, tid FROM ( SELECT * , ROW_NUMBER() OVER (PARTITION BY tid ORDER BY tdate DESC ) AS RankNo FROM ( SELECT tmoney, tdate, tid , 'A table' AS tbl FROM A_table UNION ALL SELECT tmoney, tdate, tid , 'B table' AS tbl FROM B_table ) CCC ) DDD WHERE RankNo = 1
칼럼 타입이 같으면, 칼럼명은 별칭으로 처리하셔도 됩니다.
WITH A_table AS ( SELECT 10000 AS a_money, '2021-03-06 12:00:00' AS a_date, 'aaa' AS tid UNION ALL SELECT 10000 , '2021-03-06 11:00:00' , 'bbb' UNION ALL SELECT 10000 , '2021-03-06 09:00:00' , 'aaa' UNION ALL SELECT 10000 , '2021-03-06 08:00:00' , 'bbb' ), B_table AS ( SELECT 5000 AS b_col1, '2021-03-06 11:00:00' AS b_col2, 'aaa' AS b_col3 UNION ALL SELECT 5000 , '2021-03-06 11:50:00' , 'bbb' UNION ALL SELECT 5000 , '2021-03-06 10:00:00' , 'aaa' UNION ALL SELECT 5000 , '2021-03-06 07:00:00' , 'bbb' ) SELECT tbl, col_money, col_date, col_id FROM ( SELECT * , ROW_NUMBER() OVER (PARTITION BY col_id ORDER BY col_date DESC ) AS RankNo FROM ( SELECT a_money as col_money, a_date as col_date, tid as col_id , 'A table' AS tbl FROM A_table UNION ALL SELECT b_col1 as col_money, b_col2 as col_date, b_col3 as col_id , 'B table' AS tbl FROM B_table ) CCC ) DDD WHERE RankNo = 1