|
밑에 조합에 관한 sql을 알아보려 했던건 다음의 sql의 내용이 좀 난해해서 다른조합 sql로 대체해보러고 했뎐건데요.. 문제는 불러들이는 csv 파일의 인원수가 64명으로 제한되어 있어서요..power 함수로 2의 63승으로 flag값을 정해서 64으로 제한되어 있습니다.. 이걸 해결할 능력이 없어서 ㅜㅜ
csv 파일의 내용은 다음처럼 되어 있습니다. 사실 한줄로 김태영,05,27,36 이런식으로 하고싶지만 아직 초보라.. 파일을 2개로 각각 저장해서 나중에 실행파일만 인수값 변경해서 mssql에서 파일열기로 불러들여 실행만 하면 되는 겁니다. 주석은 제가 공부하면서 달은거라 틀릴수도 있습니다;;;
김태영,05
김태영,27
김태영,36
오성식,12
오성식,24
오성식,42
최민영,06
최민영,35
최민영,41
‐------‐-------------------(db 및 프로시저 생성 파잏내용)
USE [master]
GO
ALTER DATABASE SCOPED CONFIGURATION
SET LEGACY_CARDINALITY_ESTIMATION = OFF;
GO
ALTER DATABASE SCOPED CONFIGURATION
SET MAXDOP = 0;
GO
ALTER DATABASE SCOPED CONFIGURATION
SET PARAMETER_SNIFFING = ON;
GO
ALTER DATABASE SCOPED CONFIGURATION
SET QUERY_OPTIMIZER_HOTFIXES = OFF;
GO
/* ################################################################################################## */
/* AnalyzeData 파일 크기, 경로 및 기타 설정 */
/* ################################################################################################## */
/****** Object: Database [AnalyzeData] Script Date: 2019-12-28 오후 8:50:16 ******/
CREATE DATABASE [AnalyzeData] CONTAINMENT = NONE ON PRIMARY
( NAME = N'AnalyzeData',
FILENAME = N'C:\MSSQL\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AnalyzeData.mdf',
SIZE = 10485760KB, MAXSIZE = UNLIMITED, FILEGROWTH = 1048576KB
),
FILEGROUP [AnalyzeData_InMemory] CONTAINS MEMORY_OPTIMIZED_DATA DEFAULT
( NAME = N'AnalyzeData_InMemory',
FILENAME = N'C:\MSSQL\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AnalyzeData_InMemory',
MAXSIZE = UNLIMITED
)
LOG ON
( NAME = N'AnalyzeData_log',
FILENAME = N'C:\MSSQL\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AnalyzeData_log.ldf',
SIZE = 1048576KB, MAXSIZE = 2048GB, FILEGROWTH = 1048576KB
)
GO
IF ( 1 = FULLTEXTSERVICEPROPERTY ( 'IsFullTextInstalled' ) )
BEGIN
EXEC [AnalyzeData].[dbo].[sp_fulltext_database] @action = 'enable'
END
GO
ALTER DATABASE [AnalyzeData]
SET COMPATIBILITY_LEVEL = 130 /* SQL Server 2016(13.x) 버전의 SQL 엔진과 호환되도록 Transact-SQL 및 쿼리 처리 동작을 설정 */
GO
ALTER DATABASE [AnalyzeData]
SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [AnalyzeData]
SET ANSI_NULLS OFF
GO
ALTER DATABASE [AnalyzeData]
SET ANSI_PADDING OFF
GO
ALTER DATABASE [AnalyzeData]
SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [AnalyzeData]
SET ARITHABORT OFF
GO
ALTER DATABASE [AnalyzeData]
SET AUTO_CLOSE OFF
GO
ALTER DATABASE [AnalyzeData]
SET AUTO_SHRINK OFF
GO
ALTER DATABASE [AnalyzeData]
SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [AnalyzeData]
SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [AnalyzeData]
SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [AnalyzeData]
SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [AnalyzeData]
SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [AnalyzeData]
SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [AnalyzeData]
SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [AnalyzeData]
SET DISABLE_BROKER
GO
ALTER DATABASE [AnalyzeData]
SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [AnalyzeData]
SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [AnalyzeData]
SET TRUSTWORTHY OFF
GO
ALTER DATABASE [AnalyzeData]
SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [AnalyzeData]
SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [AnalyzeData]
SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [AnalyzeData]
SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [AnalyzeData]
SET RECOVERY FULL
GO
ALTER DATABASE [AnalyzeData]
SET MULTI_USER
GO
ALTER DATABASE [AnalyzeData]
SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [AnalyzeData]
SET DB_CHAINING OFF
GO
ALTER DATABASE [AnalyzeData]
SET FILESTREAM ( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [AnalyzeData]
SET TARGET_RECOVERY_TIME = 60 SECONDS
GO
ALTER DATABASE [AnalyzeData]
SET DELAYED_DURABILITY = DISABLED
GO
ALTER DATABASE [AnalyzeData]
SET QUERY_STORE = OFF
GO
ALTER DATABASE [AnalyzeData]
SET READ_WRITE
GO
EXEC sys.sp_db_vardecimal_storage_format N'AnalyzeData', N'ON'
GO
USE [AnalyzeData]
GO
/* □□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□ */
/* □□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□ */
/* □□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□ */
/* □□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□ */
/* □□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□ */
/* □□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□ */
/* ################################################################################################## */
/* TB_00_RAW TABLE 생성 1 - 데이터 파일(csv)을 데이터베이스에 일괄 입력 처리할 테이블 및 인덱스 생성 */
/* ################################################################################################## */
/****** Object: Table [dbo].[TB_00_RAW] Script Date: 2019-12-28 오후 8:50:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TB_00_RAW] ( [NAME] [nvarchar](1000) NOT NULL,
[DATA] [int] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Index [IX_TB_00_RAW] Script Date: 2019-12-28 오후 8:50:16 ******/
CREATE CLUSTERED COLUMNSTORE
INDEX [IX_TB_00_RAW]
ON [dbo].[TB_00_RAW]
WITH ( DROP_EXISTING = OFF,
COMPRESSION_DELAY = 0
) ON [PRIMARY]
GO
/* ################################################################################################## */
/* ################################################################################################## */
/* SP_GENERATE_TB_00_RAW 프로시저 생성 1 - 데이터 파일을 데이터베이스에 일괄 입력 처리 */
/* ################################################################################################## */
/* ################################################################################################## */
/****** Object: StoredProcedure [dbo].[SP_GENERATE_TB_00_RAW] Script Date: 2019-12-28 오후 8:50:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_GENERATE_TB_00_RAW] @PATH NVARCHAR(1000)
AS BEGIN
TRUNCATE TABLE [dbo].[TB_00_RAW] /* 테이블 초기화 */
DECLARE @CSV NVARCHAR(1000) = N'BULK INSERT [dbo].[TB_00_RAW] /* csv 파일 자료 입력 */
FROM ''' + @PATH + N'''
WITH ( CODEPAGE = ''65001'', /* UTF-8 유니코드 */
FIRSTROW = 1, /* 첫번째 줄부터 적용 */
MAXERRORS = 0,
FIELDTERMINATOR = '','', /* 필드 구분자 */
ROWTERMINATOR = ''\n'', /* 가로줄 행(row) 구분자 */
TABLOCK
)'
EXECUTE sp_executesql @CSV
END
GO
/* □□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□ */
/* □□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□ */
/* □□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□ */
/* ################################################################################################## */
/* TB_10_FILE TABLE 생성 2 - 일괄 입력 처리된 데이터를 순서에 맞춰 SEQ 정보 입력할 테이블 및 인덱스 생성 */
/* ################################################################################################## */
/****** Object: Table [dbo].[TB_10_FILE] Script Date: 2019-12-28 오후 8:50:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TB_10_FILE] ( [SEQ] [bigint] IDENTITY(1,1) NOT NULL,
[NAME] [nvarchar](1000) NOT NULL,
[DATA] [int] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Index [IX_TB_10_FILE] Script Date: 2019-12-28 오후 8:50:16 ******/
CREATE CLUSTERED COLUMNSTORE
INDEX [IX_TB_10_FILE]
ON [dbo].[TB_10_FILE]
WITH ( DROP_EXISTING = OFF,
COMPRESSION_DELAY = 0
) ON [PRIMARY]
GO
/* ################################################################################################## */
/* ################################################################################################## */
/* SP_GENERATE_TB_10_FILE 프로시저 생성 2 - 일괄 입력 처리된 데이터를 순서에 맞춰 SEQ 정보 입력 */
/* ################################################################################################## */
/* ################################################################################################## */
/****** Object: StoredProcedure [dbo].[SP_GENERATE_TB_10_FILE] Script Date: 2019-12-28 오후 8:50:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_GENERATE_TB_10_FILE]
AS BEGIN
TRUNCATE TABLE [dbo].[TB_10_FILE] /* 테이블 초기화 */
INSERT INTO [dbo].[TB_10_FILE] ( [NAME], [DATA] )
SELECT [NAME], [DATA]
FROM [dbo].[TB_00_RAW]
ORDER BY [NAME]
END
GO
/* □□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□ */
/* □□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□ */
/* □□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□ */
/* ################################################################################################## */
/* TB_20_NAME TABLE 생성 3 - 이름 리스트 추출 및 FLAG 자동 입력 처리할 테이블 및 인덱스 생성 */
/* ################################################################################################## */
/****** Object: Table [dbo].[TB_20_NAME] Script Date: 2019-12-28 오후 8:50:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TB_20_NAME] ( [SEQ] [bigint] IDENTITY(1,1) NOT NULL,
[NAME] [nvarchar](1000) NOT NULL,
[FLAG] [bigint] NULL
) ON [PRIMARY]
GO
/****** Object: Index [IX_TB_20_NAME] Script Date: 2019-12-28 오후 8:50:16 ******/
CREATE CLUSTERED COLUMNSTORE
INDEX [IX_TB_20_NAME]
ON [dbo].[TB_20_NAME]
WITH ( DROP_EXISTING = OFF,
COMPRESSION_DELAY = 0
) ON [PRIMARY]
GO
/* ################################################################################################## */
/* ################################################################################################## */
/* SP_GENERATE_TB_20_NAME 프로시저 생성 3 - 이름 리스트 추출 및 FLAG 자동 입력 처리 */
/* ################################################################################################## */
/* ################################################################################################## */
/****** Object: StoredProcedure [dbo].[SP_GENERATE_TB_20_NAME] Script Date: 2019-12-28 오후 8:50:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_GENERATE_TB_20_NAME]
AS BEGIN
TRUNCATE TABLE [dbo].[TB_20_NAME] /* 테이블 초기화 */
INSERT INTO [dbo].[TB_20_NAME] ( [NAME] )
SELECT DISTINCT [NAME] /* 세로(열) 중복된 이름 제거 */
FROM [dbo].[TB_10_FILE]
ORDER BY [NAME]
UPDATE [dbo].[TB_20_NAME]
SET [FLAG] = CAST( POWER( 2.0, [SEQ] - 1 ) AS BIGINT ) /* [FLAG] 값을 2의 0승인 1로 지정 */
END
GO
/* □□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□ */
/* □□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□ */
/* □□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□ */
/* □□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□ */
/* □□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□ */
/* □□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□ */
/* ================================================================================================== */
/* ================================================================================================== */
/* FN_COMBINE 함수 생성 - 중복된 번호를 제외하고 남은 번호를 콤마로 구분해서 일렬로 나열 */
/* SP_GENERATE_TB_30_COMBINATION 프로시저 4에서 사용됨 */
/* ================================================================================================== */
/* ================================================================================================== */
/****** Object: UserDefinedFunction [dbo].[FN_COMBINE] Script Date: 2019-12-28 오후 8:50:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[FN_COMBINE] ( @LC_FLAG BIGINT ) /* 인수값을 LC_FLAG 지역 매개변수로 받음 */
RETURNS VARCHAR(1000)
-- =============================================
-- FN_COUNT 함수와 동일한 부분 시작
-- =============================================
AS BEGIN
DECLARE @BIT INT = 0 /* BIT 지역 변수 선언 0으로 값지정 */
DECLARE @COUNTER INT /* COUNTER 지역 변수 선언 */
DECLARE @RESULT TABLE ( [FLAG] BIGINT NOT NULL, /* RESULT 지역 테이블 선언하고, 테이블 자료를 [FLAG] 값으로 함 */
PRIMARY KEY ( [FLAG] )
)
DECLARE @LC_DATA TABLE ( [DATA] INT NOT NULL, /* LC_DATA 지역 테이블 선언하고, 테이블 자료를 [DATA] 값으로 함 */
PRIMARY KEY ( [DATA] )
)
WHILE @LC_FLAG > 0 /* LC_FLAG 변수값이 0 이상일 경우 반복 */
BEGIN
IF @LC_FLAG % 2 <> 0 /* LC_FLAG 값을 2로 나눈 값(정수, 소숫점값 버림)이 0이 아닐경우 RESULT 테이블에 [FLAG] 값을 2의 0승인 1로 지정 */
BEGIN
INSERT INTO @RESULT ( [FLAG] )
VALUES ( CAST( POWER( 2.0, @BIT ) AS BIGINT ) )
END
-- SET @LC_FLAG = @LC_FLAG / 2 /* LC_FLAG 변수값을 2로 나눈 값으로 설정 */
SET @LC_FLAG = ROUND(@LC_FLAG / 2, 0, 1)
SET @BIT = @BIT + 1 /* BIT 변수값을 1씩 증가 */
END /* WHILE 반복문 종료 */
INSERT INTO @LC_DATA ( [DATA] ) /* [dbo].[TB_10_FILE] 테이블을 [F]로 하고, [DATA] 자료를 LC_DATA 테이블에 입력 */
SELECT [F].[DATA]
FROM [dbo].[TB_10_FILE] AS [F]
JOIN [dbo].[TB_20_NAME] AS [N] /* [dbo].[TB_20_NAME] 테이블을 [N]로 함 */
ON [F].[NAME] = [N].[NAME]
JOIN @RESULT AS [R] /* RESULT 테이블을 [R]로 함 */
ON [N].[FLAG] = [R].[FLAG]
GROUP BY [F].[DATA]
ORDER BY [DATA]
SELECT @COUNTER = COUNT ( [DATA] ) /* LC_DATA 테이블의 [DATA] 세로 행(column)의 갯수를 세어서 COUNTER 변수에 값지정 */
FROM @LC_DATA
-- =============================================
-- FN_COUNT 함수와 동일한 부분 끝
-- =============================================
DECLARE @COMBINED VARCHAR(1000)
/* LC_DATA 테이블로 부터 [DATA] 자료를 COMBINED 변수에 NULL이 아닌값을 반환후 콤마로 구분하고 데이터형을 NVARCHAR로 한다 */
SELECT @COMBINED = COALESCE ( @COMBINED + ', ', '' ) + CONVERT ( NVARCHAR(1000), [DATA] ) /* 번호 나열 부분 */
FROM @LC_DATA
RETURN @COMBINED
END
GO
/* ================================================================================================== */
/* ================================================================================================== */
/* FN_COUNT 함수 생성 - 중복된 번호 제외하고 남은 숫자가 특정 범위의 갯수로 되어있는지를 확인 */
/* SP_GENERATE_TB_30_COMBINATION 프로시저 4에서 사용됨 */
/* ================================================================================================== */
/* ================================================================================================== */
/****** Object: UserDefinedFunction [dbo].[FN_COUNT] Script Date: 2019-12-28 오후 8:50:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[FN_COUNT] ( @LC_FLAG BIGINT ) /* 인수값을 LC_FLAG 지역 매개변수로 받음 */
RETURNS INT
-- =============================================
-- FN_COMBINE 함수와 동일한 부분 시작
-- =============================================
AS BEGIN
DECLARE @BIT INT = 0 /* BIT 지역 변수 선언 0으로 값지정 */
DECLARE @COUNTER INT /* COUNTER 지역 변수 선언 */
DECLARE @RESULT TABLE ( [FLAG] BIGINT NOT NULL, /* RESULT 지역 테이블 선언하고, 테이블 자료를 [FLAG] 값으로 함 */
PRIMARY KEY ( [FLAG] )
)
DECLARE @LC_DATA TABLE ( [DATA] INT NOT NULL, /* LC_DATA 지역 테이블 선언하고, 테이블 자료를 [DATA] 값으로 함 */
PRIMARY KEY ( [DATA] )
)
WHILE @LC_FLAG > 0 /* LC_FLAG 변수값이 0 이상일 경우 반복 */
BEGIN
IF @LC_FLAG % 2 <> 0 /* LC_FLAG 값을 2로 나눈 값(정수, 소숫점값 버림)이 0이 아닐경우 RESULT 테이블에 [FLAG] 값을 2의 0승인 1로 지정 */
BEGIN
INSERT INTO @RESULT ( [FLAG] )
VALUES ( CAST( POWER( 2.0, @BIT ) AS BIGINT ) )
END
-- SET @LC_FLAG = @LC_FLAG / 2 /* LC_FLAG 변수값을 2로 나눈 값으로 설정 */
SET @LC_FLAG = ROUND(@LC_FLAG / 2, 0, 1)
SET @BIT = @BIT + 1 /* BIT 변수값을 1씩 증가 */
END /* WHILE 반복문 종료 */
INSERT INTO @LC_DATA ( [DATA] ) /* [dbo].[TB_10_FILE] 테이블을 [F]로 하고, [DATA] 자료를 LC_DATA 테이블에 입력 */
SELECT [F].[DATA]
FROM [dbo].[TB_10_FILE] AS [F]
JOIN [dbo].[TB_20_NAME] AS [N] /* [dbo].[TB_20_NAME] 테이블을 [N]로 함 */
ON [F].[NAME] = [N].[NAME]
JOIN @RESULT AS [R] /* RESULT 테이블을 [R]로 함 */
ON [N].[FLAG] = [R].[FLAG]
GROUP BY [F].[DATA]
ORDER BY [DATA]
SELECT @COUNTER = COUNT ( [DATA] ) /* LC_DATA 테이블의 [DATA] 세로 행(column)의 갯수를 세어서 COUNTER 변수에 값지정 */
FROM @LC_DATA
-- =============================================
-- FN_COMBINE 함수와 동일한 부분 끝
-- =============================================
RETURN @COUNTER
END
GO
/* □□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□ */
/* □□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□ */
/* □□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□ */
/* ################################################################################################## */
/* TB_30_COMBINATION TABLE 생성 4 - 추출된 이름 리스트를 기반으로 순열 생성할 테이블 및 인덱스 생성 */
/* ################################################################################################## */
/****** Object: Table [dbo].[TB_30_COMBINATION] Script Date: 2019-12-28 오후 8:50:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TB_30_COMBINATION] ( [SEQ] [bigint] IDENTITY(1,1) NOT NULL,
[NAME] [nvarchar](1000) NOT NULL,
[ID_NUMS] [bigint] NOT NULL,
[GROUPS] [int] NOT NULL,
[FROM_TO] [int] NULL,
[DATAS] [nvarchar](1000) NULL
) ON [PRIMARY]
GO
/****** Object: Index [IX_TB_30_COMBINATION] Script Date: 2019-12-28 오후 8:50:16 ******/
CREATE CLUSTERED COLUMNSTORE
INDEX [IX_TB_30_COMBINATION]
ON [dbo].[TB_30_COMBINATION]
WITH ( DROP_EXISTING = OFF,
COMPRESSION_DELAY = 0
) ON [PRIMARY]
GO
/* ################################################################################################## */
/* ################################################################################################## */
/* SP_GENERATE_TB_30_COMBINATION 프로시저 생성 4 - 추출된 이름 리스트를 기반으로 순열 생성 */
/* ################################################################################################## */
/* ################################################################################################## */
/****** Object: StoredProcedure [dbo].[SP_GENERATE_TB_30_COMBINATION] Script Date: 2019-12-28 오후 8:50:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_GENERATE_TB_30_COMBINATION]
-- @DUPLICATION_YN BIT = 0,
@COMBINATION INT = NULL
AS BEGIN
IF OBJECT_ID('tempdb..#temp') IS NOT NULL /* 임시 테이블 여부 확인 */
BEGIN
DROP TABLE #temp /* 임시 테이블 삭제 */
END
;WITH [T] ( [NAME], /* WITH 문을 프로시저에서 사용할때 공통 테이블일 경우 앞에 세미콜론을 붙인다 */
[FLAG],
[GROUPS]
)
AS ( SELECT [NAME] = CONVERT ( NVARCHAR(1000), [NAME] ),
[FLAG],
[GROUPS] = 1
FROM [dbo].[TB_20_NAME]
UNION ALL
SELECT [NAME] = CONVERT ( NVARCHAR(1000), [dbo].[TB_20_NAME].[NAME] + ', ' + [T].[NAME] ), /* 이름(닉네임) 나열 부분 */
[ID_NUMS] = [dbo].[TB_20_NAME].[FLAG] + [T].[FLAG],
[GROUPS] = [GROUPS] + 1
FROM [dbo].[TB_20_NAME],
[T]
WHERE [GROUPS] < @COMBINATION AND CHARINDEX ( [dbo].[TB_20_NAME].[NAME], [T].[NAME] ) = 0
)
SELECT [NAME],
[ID_NUMS] = [FLAG],
[GROUPS]
INTO #temp
FROM [T]
-- WHERE [GROUPS] = @COMBINATION
WHERE ( (@COMBINATION IS NULL) OR [GROUPS] = @COMBINATION )
ORDER BY [NAME]
TRUNCATE TABLE [dbo].[TB_30_COMBINATION] /* 테이블 초기화 */
-- IF @DUPLICATION_YN = 0 /* 이름 중복허용 여부 항목 주석처리 */
-- BEGIN
INSERT INTO [dbo].[TB_30_COMBINATION] ( [NAME],
[ID_NUMS],
[GROUPS]
-- [FROM_TO],
-- [DATAS]
)
SELECT [NAME] = MIN ( [NAME] ),
[ID_NUMS] = MIN ( [ID_NUMS] ),
[GROUPS] = MIN ( [GROUPS] )
-- [FROM_TO] = [dbo].[FN_COUNT] ( MIN ( [ID_NUMS] ) ),
-- [DATAS] = [dbo].[FN_COMBINE] ( MIN ( [ID_NUMS] ) )
FROM #temp
GROUP BY [ID_NUMS]
ORDER BY [NAME]
/*
-- 이름 중복허용 항목 주석처리
END
ELSE
BEGIN
INSERT INTO [dbo].[TB_30_COMBINATION] ( [NAME],
[ID_NUMS],
[GROUPS],
[FROM_TO],
[DATAS]
)
SELECT [NAME],
[ID_NUMS],
[GROUPS],
[FROM_TO] = [dbo].[FN_COUNT] ( [ID_NUMS] ),
[DATAS] = [dbo].[FN_COMBINE] ( [ID_NUMS] )
FROM #temp
ORDER BY[NAME]
END
*/
DROP TABLE #temp /* 임시 테이블 삭제 */
END
GO
/* ################################################################################################## */
/* SP_FILL_COUNT_DATAS_TB_COMBINATION 프로시저 생성 4.5 - 조합 리스트를 기반으로 DATA 수 마킹 */
/* ################################################################################################## */
/****** Object: StoredProcedure [dbo].[SP_FILL_COUNT_DATAS_TB_COMBINATION] Script Date: 2020-04-08 오전 12:38:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_FILL_COUNT_DATAS_TB_COMBINATION]
AS BEGIN
UPDATE [dbo].[TB_30_COMBINATION]
SET [FROM_TO] = [dbo].[FN_COUNT] ([ID_NUMS]),
[DATAS] = [dbo].[FN_COMBINE] ([ID_NUMS])
END
GO
/* □□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□ */
/* □□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□ */
/* □□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□□ */
/* ################################################################################################## */
/* SP_SELECT_TB_30_COMBINATION 프로시저 생성 5 - 생성된 순열 데이터에서 필요한 자료 조회 출력 */
/* ################################################################################################## */
/****** Object: StoredProcedure [dbo].[SP_SELECT_TB_30_COMBINATION] Script Date: 2019-12-28 오후 8:50:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_SELECT_TB_30_COMBINATION]
@FROM INT = NULL,
@TO INT = NULL
AS BEGIN
-- SELECT [ID_NUMS],
SELECT [FROM_TO],
[GROUPS],
[NAME],
[DATAS]
FROM [dbo].[TB_30_COMBINATION]
WHERE [FROM_TO] BETWEEN @FROM AND @TO
ORDER BY [FROM_TO]
END
GO
/* [dbo].[TB_20_NAME] 테이블 내용 확인용
CREATE PROCEDURE [dbo].[SP_SELECT_TB_30_COMBINATION]
AS BEGIN
SELECT [SEQ],
[NAME],
[FLAG]
FROM [dbo].[TB_20_NAME]
ORDER BY [SEQ]
END
GO
*/
-----------‐--------------------(실행 파일 내용)
|
