CTE Table

ALTER PROC [dbo].[USP_BASIC_TEST_4_PROJECTTEST_LIST] 
 @COM_CODE     VARCHAR(6) = ''
, @SEARCH_KEYWORD  NVARCHAR(100)= ''   --검색어
, @CURR_PAGE     INT = 1
, @PAGESIZE      INT =15        --한페이지의 ROW수 , 0일경우는 모든자료를 리스트업함
, @SORT        VARCHAR(20) = 'CD_CODE'     --정렬컬럼
, @SORT_AD      CHAR(1)       --(A)SC/(D)ESC

AS

DECLARE  @SROW  SMALLINT
,    @EROW  SMALLINT
,    @KEYWORD VARCHAR(200)

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SET @SEARCH_KEYWORD = '%' + @SEARCH_KEYWORD + '%'

IF @PAGESIZE = 0
 BEGIN
  SET @SROW = 0
  SET @EROW = 0
 END
ELSE
 BEGIN
  IF @CURR_PAGE = 0
   SET @CURR_PAGE = 1
 
  SET @SROW = ((@CURR_PAGE -1) * @PAGESIZE) + 1
  SET @EROW = @CURR_PAGE * @PAGESIZE
 END
 
 
;
WITH CTETABLE(
      MAXCNT, ROWNUMBER, COM_CODE, CD_CODE, NM_CODE, CD_DEPT
      , NM_DEPT, AMT_AMOUNT, CD_WRITER
      , NM_WRITER, YN_USED, BIGO_REMARK1
      , BIGO_REMARK2, DT_REGYMD, DT_REGISTER
      )
AS(
 SELECT
  COUNT(*) OVER() AS MAXCNT
 ,    ROW_NUMBER() OVER( ORDER BY YN_USED desc
 , (CASE @SORT_AD WHEN  'A' THEN CASE @SORT WHEN 'CD_CODE' THEN CD_CODE ELSE NM_CODE END END)  ASC
    , (CASE @SORT_AD WHEN  'D' THEN CASE @SORT WHEN 'NM_CODE' THEN NM_CODE ELSE CD_CODE END END)  DESC
       ) AS ROWNUMBER
  , COM_CODE
  , CD_CODE
  , NM_CODE
  , CD_DEPT
  , NM_DEPT
  , AMT_AMOUNT
  , CD_WRITER
  , NM_WRITER
  , YN_USED
  , BIGO_REMARK1
  , BIGO_REMARK2
  , DT_REGYMD
  , DT_REGISTER              
  FROM  (       
      SELECT COM_CODE
  , CD_CODE
  , NM_CODE
  , CD_DEPT
  , NM_DEPT
  , AMT_AMOUNT
  , CD_WRITER
  , NM_WRITER
  , YN_USED
  , BIGO_REMARK1
  , BIGO_REMARK2
  , DT_REGYMD
  , DT_REGISTER
      FROM  BASIC_TEST_4
      WHERE  COM_CODE = @COM_CODE
      AND   ( CD_CODE LIKE @SEARCH_KEYWORD OR NM_CODE LIKE @SEARCH_KEYWORD )
     ) AS F
 )
 SELECT MAXCNT, ROWNUMBER, COM_CODE, CD_CODE
    , NM_CODE, CD_DEPT, NM_DEPT, AMT_AMOUNT
    , CD_WRITER, NM_WRITER, YN_USED, BIGO_REMARK1
    , BIGO_REMARK2, DT_REGYMD, DT_REGISTER
 FROM CTETABLE
 WHERE  CASE @SROW WHEN 0 THEN @SROW ELSE ROWNUMBER END BETWEEN @SROW AND @EROW
 ORDER BY ROWNUMBER

 

 

------------------------------------------------------------------------------------------------------------------------

declare @sort_ad nchar(1), @sort nvarchar(100), @param nvarchar(100)
set @sort_ad = 'A'
set @sort = 'foreign_flag'
set @param = '%B%';

with ctetable(rownumber, com_code, est_date, est_no, foreign_flag, pjt_cd, cust,  prod_des,  exchange_rate )
as
(
 select ROW_NUMBER() over(order by est_date desc --정렬
             , (case  @sort_ad when 'A' then case @sort when 'foreign_flag' then foreign_flag  else pjt_cd end end) asc
             , (case  @sort_ad when 'D' then case @sort when 'foreign_flag' then foreign_flag  else pjt_cd end end) desc
            ) as rownumber
 , com_code, est_date, est_no, foreign_flag, pjt_cd, cust ,  prod_des,  exchange_rate
 from SALE020
 where com_code = '99997' --조건
 and (case @param when '' then @param else prod_des end like @param) --검색
)
select rownumber, com_code, est_date, est_no, foreign_flag, pjt_cd, cust  ,  prod_des,  exchange_rate
from ctetable
where rownumber between 0 * 15 and  (0 +1) * 15  --페이징

 

----------------------------------------------------------------------------------------------------------------

with cte
as
(
 select
  row_number() over (order by test1) as num
 , test1
 , test2
 , test3
 from tbl_test
 )
, tbl1 as (select * from cte where num between @start and @end)
, tbl2 as (select count(*) cnt from cte)
 
 select *, (select cnt from tbl2)
 from tbl1

 

 

 

--------------------------------------------------------------------------------------------------------------------

자기 자신을 조회하는 재귀 CTE

WITH MENU_CTE AS (                                                                                                                                                                          
  SELECT
   MenuName
   , UpMenuCd
   , MenuCd
   , 1 AS LVL                          
  FROM  TB_COM_Menu                                                                                                                                                                    
  WHERE UpMenuCd = 'M00' 
   AND  UseYN = 'Y'                                                                                                                                                             
  UNION ALL                                                                                                                                                                               
  SELECT A.MenuName
   , A.UpMenuCd
   , A.MenuCd   
   , LVL + 1 
  FROM TB_COM_Menu A
   INNER JOIN MENU_CTE B
    ON A.UpMenuCd = B.MenuCd             
  WHERE A.UseYN = 'Y'                                                                                                           
 )                                                                                                                                                                                           
                                                                                                                                                                                                              
 SELECT
  *                                                                                                                                                                         
 FROM MENU_CTE  A                                                                                                                                                                             
 ORDER BY LVL
  , Ord  

 

 

 

 

--------------------------------------------------------------------------------------------------------------------

여러개 사용하기 

 

WITH AcRelease 

AS 

(

SELECT SpecID, ModelCd

FROM TB_ACModelPart

WHERE SpecID = 'MA13060085' AND SpecStatus = 'R'

)

, AcWriting

AS

(

SELECT SpecID, ModelCd

FROM [dbo].[TB_ACModelPart]

WHERE SpecID = 'MA13060085'  AND SpecStatus = 'W'

)

, UPart

AS

(

SELECT A.SpecID,A.ModelCd,'U' AS Status

FROM AcRelease AS A 

INNER JOIN AcWriting AS B

ON A.SpecID = B.SpecID

AND A.ModelCd = B.ModelCd

)

,DPart

AS

(

SELECT  A.SpecID,A.ModelCd,'D' AS STATUS

FROM

AcRelease AS A

LEFT OUTER JOIN AcWriting AS B

ON A.SpecID = B.SpecID 

AND A.ModelCd = B.ModelCd

WHERE

B.SpecID IS NULL

)

,CPart

AS

(

SELECT

A.SpecID,A.ModelCd ,'C' AS STATUS

FROM

AcWriting AS A

LEFT OUTER JOIN AcRelease AS B

ON A.SpecID = B.SpecID

AND A.ModelCd = B.ModelCd

WHERE

B.SpecID IS NULL

)

, BasicPart

AS

(

SELECT * FROM UPart

UNION ALL

SELECT * FROM DPart

UNION ALL

SELECT * FROM CPart

)

SELECT

ROW_NUMBER() OVER(ORDER BY SpecID) AS No

,SpecID

,ModelCd

,[Status] AS ChagneStatus

FROM

BasicPart

;