*본 문제는 모 기업 코딩테스트에서 나온 문제를 제 방식대로 변형한 문제입니다.
문제. orders 테이블 안에 결제완료인 주문에 한해서 해당 멤버(member_id)의 몇 번째 주문(order_sequence)를 나타내는 쿼리를 작성하시오.
*최종결과에는 member_id, order_time, order_sequence만 나타내시오.
orders 스키마
* 해당 테이블은 임의로 값을 넣은 예시입니다.
스키마 작성 코드
* 해당 values는 연습용으로 만든 값입니다.
CREATE TABLE orders
(`order_code` int, `order_time` datetime, `pay_money` int, `pay_yn` varchar(1), `member_id` int)
;
INSERT INTO orders
(`order_code`, `order_time`, `pay_money`, `pay_yn`, `member_id`)
VALUES
(387836, '2018-04-13 10:20:57', 4500, 'y', 70955),
(345477, '2017-12-16 03:30:43', 34200, 'y', 70955),
(531013, '2019-05-16 19:22:28', 56300, 'y', 70955),
(593478, '2019-11-07 01:32:34', 4500, 'y', 218095),
(253089, '2017-04-03 04:38:06', 21400, 'y', 218095),
(418074, '2018-07-07 03:24:23', 22250, 'y', 192368),
(57114, '2015-10-06 13:03:50', 56300, 'y', 192368),
(176732, '2016-09-02 06:57:25', 82520, 'y', 192368),
(109484, '2016-02-28 20:31:45', 13150, 'y', 192368),
(412987, '2018-06-22 20:44:12', 53350, 'y', 192368),
(639937, '2020-03-15 08:04:46', 57900, 'y', 302700),
(163256, '2016-07-27 00:51:49', 28500, 'y', 216440),
(490971, '2019-01-24 21:36:25', 24070, 'y', 205394),
(570774, '2019-09-04 20:53:53', 63700, 'y', 205394),
(440350, '2018-09-07 02:50:13', 71300, 'y', 258721),
(9937, '2015-05-28 21:13:29', 14900, 'y', 108191),
(199243, '2016-11-03 14:04:46', 14350, 'y', 409520),
(132627, '2016-05-03 03:47:01', 42000, 'y', 161249),
(296216, '2017-08-01 09:28:18', 61100, 'y', 161249),
(498143, '2019-02-14 00:53:14', 46800, 'n', 219228),
(648427, '2020-04-07 23:54:34', 76580, 'y', 145132),
(251436, '2017-03-29 12:11:16', 71300, 'y', 145132),
(394545, '2018-05-02 00:40:31', 105600, 'y', 364892),
(19351, '2015-06-23 14:55:21', 104990, 'y', 364892),
(66250, '2015-10-31 17:11:22', 45610, 'y', 364892),
(93707, '2016-01-16 03:36:54', 84900, 'y', 49549),
(586361, '2019-10-18 09:35:47', 23700, 'y', 49549),
(649945, '2020-04-12 02:32:07', 22260, 'y', 49549),
(396265, '2018-05-07 00:57:25', 90000, 'y', 49549),
(171180, '2016-08-18 02:32:34', 65720, 'y', 49549),
(500201, '2019-02-19 20:23:14', 38880, 'y', 49549),
(317690, '2017-09-30 02:49:52', 29180, 'y', 49549),
(333643, '2017-11-13 00:40:42', 63500, 'y', 85945),
(425427, '2018-07-27 17:04:06', 12000, 'y', 85945),
(251630, '2017-03-30 02:05:29', 22880, 'y', 85945),
(21583, '2015-06-29 19:18:42', 47640, 'y', 85945),
(636503, '2020-03-05 20:17:25', 4500, 'y', 85945),
(414687, '2018-06-27 13:29:17', 25000, 'y', 85945),
(402242, '2018-05-23 22:20:01', 25000, 'y', 85945),
(569755, '2019-09-02 01:36:23', 36500, 'y', 85945),
(231983, '2017-02-03 14:16:29', 41800, 'y', 85945),
(569147, '2019-08-31 08:05:23', 31360, 'y', 34860),
(259941, '2017-04-22 10:49:20', 7940, 'y', 34860),
(355886, '2018-01-14 05:14:24', 46500, 'y', 201712),
(143841, '2016-06-02 23:02:42', 44380, 'y', 173537),
(44787, '2015-09-02 08:30:07', 62430, 'n', 173537),
(293278, '2017-07-24 02:30:12', 34750, 'y', 173537),
(589367, '2019-10-26 17:43:08', 115800, 'y', 173537),
(291692, '2017-07-19 12:52:50', 36800, 'y', 11890),
(3540, '2015-05-11 01:31:39', 59500, 'y', 11890),
(319982, '2017-10-06 09:46:59', 21900, 'y', 11890),
(55004, '2015-09-30 14:12:20', 64000, 'y', 11890),
(183613, '2016-09-21 01:48:23', 51080, 'y', 11890),
(625740, '2020-02-05 02:00:39', 20100, 'y', 11890),
(296971, '2017-08-03 11:14:37', 66410, 'y', 11890),
(281254, '2017-06-20 20:58:41', 113400, 'y', 11890),
(540842, '2019-06-13 02:54:58', 42680, 'y', 11890),
(413161, '2018-06-23 09:00:36', 69440, 'y', 11890),
(190602, '2016-10-10 13:36:45', 138460, 'y', 11890),
(5926, '2015-05-17 13:27:58', 37660, 'y', 285071),
(481819, '2018-12-30 10:09:21', 91700, 'y', 240824),
(47275, '2015-09-09 02:54:06', 165500, 'y', 240824),
(673674, '2020-06-16 20:53:11', 88000, 'y', 240824),
(284597, '2017-06-30 01:06:58', 30000, 'y', 240824),
(473538, '2018-12-07 19:29:05', 26550, 'y', 240824),
(380514, '2018-03-24 02:40:03', 76100, 'y', 337863),
(318290, '2017-10-01 18:42:59', 64800, 'y', 337863),
(307910, '2017-09-02 21:08:38', 4500, 'y', 48202),
(121282, '2016-04-01 16:02:23', 21200, 'y', 48202),
(268403, '2017-05-15 19:54:55', 55400, 'y', 268525),
(331310, '2017-11-06 13:15:17', 83100, 'y', 268525),
(590169, '2019-10-28 21:40:21', 42680, 'y', 268525),
(413349, '2018-06-23 20:38:46', 26400, 'y', 98775),
(137197, '2016-05-15 15:44:07', 27100, 'n', 98775),
(636402, '2020-03-05 13:41:24', 47200, 'y', 98775),
(497549, '2019-02-12 09:01:17', 33500, 'y', 98775),
(447147, '2018-09-25 21:21:59', 14900, 'n', 66846),
(230231, '2017-01-29 14:55:29', 22750, 'y', 179643),
(361878, '2018-01-31 03:35:37', 49300, 'y', 179643),
(129277, '2016-04-23 21:15:34', 20900, 'y', 179643),
(196195, '2016-10-25 23:33:20', 20550, 'y', 179643)
;
나의 풀이 in MySQL
SELECT member_id, order_time, order_sequence
FROM (
SELECT A.*,
(CASE @m WHEN A.member_id THEN @rownum := @rownum+1 ELSE @rownum := 1 END) order_sequence,
(@m :=A.member_id) M
FROM orders A, (SELECT @m:='', @rownum :=0 FROM DUAL) B
ORDER BY A.member_id, A.order_time
) C;
풀이 설명
1. 서브쿼리를 이용해 orders 테이블에서 CASE WHEN으로 member_id 별 order_sequence를 만들어줍니다.
2. 듀얼 테이블을 활용해 @m의 변수에 member_id, @rownum 변수에 0값을 넣어줍니다.
3. member_id 별로 order해주고 날짜의 오름차순에 따른 order_sequence를 설정해줍니다.
코드 결과
* 해당 문제는 제가 여러 SQL 관련 코딩테스트를 치르며 문제와 테이블 모두 만든 문제입니다. 저처럼 SQL 관련 코딩테스트를 준비하시는 사람들에게 조금의 도움이라도 될까 생각하며 만들었습니다.
다른 문제들 보러가기
반응형
'Programming > SQL' 카테고리의 다른 글
[MySQL] 생년월일 데이터 다루기 > 연령대별 변수 생성 (1) | 2021.05.24 |
---|---|
[MySQL] 그룹별 누적 합계 구하기 (3) | 2021.04.11 |
[MySQL] 코딩테스트 연습 예제 2 (1) | 2020.11.07 |
[MySQL] 코딩테스트 연습 예제 1 (1) | 2020.09.07 |
[MySQL] 그룹별 순위 매기기 구현하기 (5) | 2020.08.25 |
댓글