동일 순위 처리하기
- 요구사항 : TOP N 추출 시 순위가 동일한 값들을 모두 포함하고 싶은 경우
- 해결방안 : WITH TIES 구문 사용
-- 주문 정보를 저장하는 Order 테이블 생성
CREATE TABLE OrderTable (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE
);
-- 고객 정보를 저장하는 Customer 테이블 생성
CREATE TABLE Customer (
CustomerID INT PRIMARY KEY,
CustomerName NVARCHAR(50)
);
-- 데이터 삽입
INSERT INTO Customer (CustomerID, CustomerName) VALUES (1, 'CustomerA');
INSERT INTO Customer (CustomerID, CustomerName) VALUES (2, 'CustomerB');
INSERT INTO Customer (CustomerID, CustomerName) VALUES (3, 'CustomerC');
INSERT INTO OrderTable (OrderID, CustomerID, OrderDate) VALUES (101, 1, '2022-01-01');
INSERT INTO OrderTable (OrderID, CustomerID, OrderDate) VALUES (102, 2, '2022-01-02');
INSERT INTO OrderTable (OrderID, CustomerID, OrderDate) VALUES (103, 1, '2022-01-03');
INSERT INTO OrderTable (OrderID, CustomerID, OrderDate) VALUES (104, 3, '2022-01-04');
INSERT INTO OrderTable (OrderID, CustomerID, OrderDate) VALUES (105, 1, '2022-01-05');
INSERT INTO OrderTable (OrderID, CustomerID, OrderDate) VALUES (106, 2, '2022-01-06');
INSERT INTO OrderTable (OrderID, CustomerID, OrderDate) VALUES (107, 1, '2022-01-07');
INSERT INTO OrderTable (OrderID, CustomerID, OrderDate) VALUES (108, 3, '2022-01-08');
INSERT INTO OrderTable (OrderID, CustomerID, OrderDate) VALUES (109, 1, '2022-01-09');
INSERT INTO OrderTable (OrderID, CustomerID, OrderDate) VALUES (110, 2, '2022-01-10');
INSERT INTO OrderTable (OrderID, CustomerID, OrderDate) VALUES (999, 2, '2023-01-10');
INSERT INTO OrderTable (OrderID, CustomerID, OrderDate) VALUES (989, 2, '2024-01-10');
/* -- 샘플 데이터 확인
SELECT *
FROM OrderTable a
JOIN Customer b
ON a.CustomerID = b.CustomerID
*/
SELECT TOP 1 WITH TIES b.CustomerName AS [고객]
, COUNT(a.OrderID) AS [주문건수합]
FROM OrderTable a
JOIN Customer b
ON a.CustomerID = b.CustomerID
GROUP BY a.CustomerID, b.CustomerName
ORDER BY [주문건수합] DESC;