상품권 연번 생성 및 REPLICATE 와 TOP절

안녕하세요!

오늘은 가벼운 쿼리 하나 소개해드릴까 합니다.

상품권 연번을 통해 만드는 방법입니다. REPLICATE라는 함수를 이용했습니다.

REPLICATE(Transact-SQL): http://msdn.microsoft.com/ko-kr/library/ms174383(v=sql.105).aspx

 테이블 형식

   1) 권종 (char 1)  1

   2) 발행일자 (char 8)  20120804

   3) 연번 (char 4) 0000

   4) 상품권 번호 (char 13) = 권종+발행일자+연번 = 120120804000

SQL Server 2005 이상 방법

DECLARE @GiftCardType CHAR(1)
,		@GiftCardCreateDate CHAR(8)
,		@GiftCardCount SMALLINT

SET @GiftCardType = 1
SET @GiftCardCreateDate = (SELECT CONVERT(CHAR(8),GETDATE(),112))
SET @GiftCardCount = 5001 --//총 5001개를 생성(0000 ~ 5000)

SELECT
	@GiftCardType	AS '권종'
,	@GiftCardCreateDate AS '발행일자'
,	REPLICATE('0',4-LEN(ROW_NUM)) + ROW_NUM AS '연번'
,	@GiftCardType + @GiftCardCreateDate + REPLICATE('0',4-LEN(ROW_NUM)) + ROW_NUM AS '상품권번호'
FROM
(

	SELECT
		TOP (@GiftCardCount)
		CONVERT(VARCHAR,ROW_NUMBER() OVER(ORDER BY a.object_id)-1)	AS ROW_NUM
	FROM			sys.objects a
	CROSS JOIN		sys.objects b
	CROSS JOIN		sys.objects c
	CROSS JOIN		sys.objects d
	CROSS JOIN		sys.objects e
) CRD

위 쿼리의 키 포인트는 REPLICATE와 TOP 절입니다.

TOP 다음에는 숫자만 와야된다고 생각하시는데.. 괄호도 가능합니다. ^^

물론 괄호가 가능하기 때문에.. 서브쿼리도 가능합니다.

아래 처럼 말이죠~

SELECT TOP (SELECT COUNT(*) FROM AAA) * FROM BBB

SQL Server 200 방법

임시 테이블을 통해 ROW_NUM을 생성합니다.

DECLARE @GiftCardType CHAR(1)
,	 @GiftCardCreateDate CHAR(8)
,	 @GiftCardCount SMALLINT

SET @GiftCardType = '1'
SET @GiftCardCreateDate = (SELECT CONVERT(CHAR(8),GETDATE(),112))
SET @GiftCardCount = 5001

SET ROWCOUNT @GiftCardCount
SELECT identity(int, 0, 1) as ROW_NUM
into #tbl
FROM	 sysobjects a
CROSS JOIN	 sysobjects b
CROSS JOIN	 sysobjects c
CROSS JOIN	 sysobjects d
CROSS JOIN	 sysobjects e

SET ROWCOUNT 0
SELECT
	@GiftCardType	AS '권종'
,	@GiftCardCreateDate AS '발행일자'
,	REPLICATE('0',4-LEN(ROW_NUM)) + ROW_NUM AS '연번'
,	@GiftCardType + @GiftCardCreateDate + REPLICATE('0',4-LEN(ROW_NUM)) + CONVERT(VARCHAR,ROW_NUM) AS '상품권번호'
FROM #tbl

drop table #tbl

이 글의 원본 질문은 아래와 같습니다.

http://www.sqlworld.pe.kr/mboard/mboard/mboard.asp?board_id=qna&group_name=board&idx_num=18825&page=1&category=&search=&b_cat=0&order_c=idx_num&order_da=desc

감사합니다. ^^

작성일: 2012.08.09

작성자: 강동운