'SQL'에 해당되는 글 2건

나름대로 ANSI Sql에 대한 경험이 있어서 자신있어 하는 상황에서 착각하기 쉬운것을 발견했습니다.

부서별 MH정보를 월별로 취합하는 레포트 프로그램인데요.

조직변경이 있어서 기존조의 조코드가 변경되고 추가조가 발생되기도 했습니다. 외부업체의 의뢰건도 있고요.

월별 의뢰건에 대한 코드별로 MH정보를 취합한 다음에 물론 이 취합정보에는 사내,사외 구분코드가 들어가 있습니다.

그래서 사내조에 대한 조명을 INNER JOIN 한 다음에 외부업체도 업체명을 INNER JOIN 해서 이 둘을 UNION ALL 로

취합했습니다. 여기서 JOIN 조건으로 ON에 사내,사외 구분코드를 넣었습니다. 깔끔하게 원하는 결과물이 나오리라 기대했는데 나오더라구요.

여기서 삭제된 조에 대한 데이타도 볼수 있도록 하기 위해서 INNER JOIN 을 LEFT OUTER JOIN 으로 바꾸었습니다.

결과가 어떻게 나올까요? 중복으로 나옵니다.

JOIN 조건으로 사내,사외구분 코드를 넣는것을 아닌것 같습니다. 두 테이블 간의 조건과 무관한  조건을 WHERE 조건으로 기입하는것이 올바른 SQL문 작성이 될것 같습니다.

JOIN 조건에는 두 테이블간의 조건만 입력하는걸로 해야 겠네요.

재귀호출(Recursive Queries)이라고 하지요.

인사구조, 조직 등의 피라미드구조 나 BOM( 소요자재내역 ) 을 분석할때 필요한 알고리즘입니다.

데이타 구조상으로 보면 부모 - 자식 관계의 레코드가 원레벨로 연결되어 있습니다. 모자 관계라고도 합니다.

SQL서버 에서 알고리즘을 지원하지 않으면  WHILE 문이 FETCH 문으로 전개하는 프로시져을 작성해야 합니다.

최상위에 있는 레코드를 0 레벨로 보고 그 하위에 있는 레코드는 1레벨로 정하는 겁니다.

레벨을 증가시켜서 반복적으로 구조를 헤치고 들어가도록 루핑을 돌려면 되지요.

SQL Server 2008에서 지원하는 것 같은데 CTE( Common Table Expressions ) 을 사용해서 

재귀호출이 기능을 작성할수 있습니다.

재귀호출 뼈대

WITH cte_name ( column_name [,...n] )

AS

(

CTE_query_definition -- Anchor member is defined.

UNION ALL

CTE_query_definition -- Recursive member is defined referencing cte_name.

)

-- Statement using the CTE

SELECT *

FROM cte_name

Example

-- Create an Employee table.

CREATE TABLE dbo.MyEmployees

(

EmployeeID smallint NOT NULL,

FirstName nvarchar(30)  NOT NULL,

LastName  nvarchar(40) NOT NULL,

Title nvarchar(50) NOT NULL,

DeptID smallint NOT NULL,

ManagerID int NULL,

 CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC) 

);

-- Populate the table with values.

INSERT INTO dbo.MyEmployees VALUES 

 (1, N'Ken', N'Sanchez', N'Chief Executive Officer',16,NULL)

,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)

,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)

,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)

,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)

,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)

,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)

,(16,  N'David',N'Bradley', N'Marketing Manager', 4, 273)

,(23,  N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);

-- execution

WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)

AS (-- Anchor member definition

SELECT e.ManagerID, e.EmployeeID, e.Title, e.Deptid, 

0 AS Level

FROM dbo.MyEmployees e

WHERE e.ManagerID IS NULL

UNION ALL

-- Recursive member definition

SELECT e.ManagerID, e.EmployeeID, e.Title, e.Deptid,

Level + 1

FROM dbo.MyEmployees AS e

INNER JOIN DirectReports AS d

ON e.ManagerID = d.EmployeeID)

-- Statement that executes the CTE

SELECT ManagerID, EmployeeID, Title, Level

FROM DirectReports

OPTION (MAXRECURSION 1000) -- Default 가 100이므로 지정하지 않았을경우 에러가 발생

또다른 예제

출처) http://sqlwithmanoj.wordpress.com/2011/12/23/recursive-cte-maximum-recursion-100-has-been-exhausted/

DECLARE
	@startDate DATETIME,
	@endDate DATETIME

SET @startDate = '11/10/2011'
SET @endDate = '03/25/2012'

; WITH CTE AS (
	SELECT
		YEAR(@startDate) AS 'yr',
		MONTH(@startDate) AS 'mm',
		DATENAME(mm, @startDate) AS 'mon',
		DATEPART(d,@startDate) AS 'dd',
		@startDate 'new_date'
	UNION ALL
	SELECT
		YEAR(new_date) AS 'yr',
		MONTH(new_date) AS 'mm',
		DATENAME(mm, new_date) AS 'mon',
		DATEPART(d,@startDate) AS 'dd',
		DATEADD(d,1,new_date) 'new_date'
	FROM CTE
	WHERE new_date < @endDate
	)
SELECT yr AS 'Year', mon AS 'Month', count(dd) AS 'Days'
FROM CTE
GROUP BY mon, yr, mm
ORDER BY yr, mm
OPTION (MAXRECURSION 1000)

Output:-

Year	Month		Days
2011	November	22
2011	December	31
2012	January		31
2012	February	29
2012	March		24