[DATABASE/SQLSERVER] 시간대별로 1건씩 추출하기

■ 시간대별로 1건씩 추출하는 방법을 보여준다.

▶ 테스트 테이블 생성하기 (SQL)


CREATE TABLE Test
(
    HHMM VARCHAR(4)
);

INSERT INTO Test VALUES ('1212');
INSERT INTO Test VALUES ('1235');
INSERT INTO Test VALUES ('1305');
INSERT INTO Test VALUES ('1322');
INSERT INTO Test VALUES ('1328');

CREATE TABLE Test

(

HHMM VARCHAR(4)

);

INSERT INTO Test VALUES ('1212');

INSERT INTO Test VALUES ('1235');

INSERT INTO Test VALUES ('1305');

INSERT INTO Test VALUES ('1322');

INSERT INTO Test VALUES ('1328');

▶ 시간대별 임의로 1건 추출하기 (SQL)


SELECT
    A.HHMM
FROM
(
    SELECT
        RANK() OVER (PARTITION BY LEFT(A.HHMM, 2) ORDER BY NEWID()) AS Sequence
       ,A.HHMM                                                      AS HHMM
    FROM Test AS A
) A
WHERE A.Sequence = 1;

SELECT

A.HHMM

FROM

(

SELECT

RANK() OVER (PARTITION BY LEFT(A.HHMM, 2) ORDER BY NEWID()) AS Sequence

,A.HHMM AS HHMM

FROM Test AS A

) A

WHERE A.Sequence = 1;