복붙노트

[SQL] (CTE)를 공통 테이블 표현식을 사용하는 경우

SQL

(CTE)를 공통 테이블 표현식을 사용하는 경우

나는 공통 테이블 표현식에 대해 읽기 시작하고 내가 그들을 사용해야합니다 사용 사례 생각할 수 없다. 그들은 같은 파생 테이블을 함께 할 수있는 중복 것으로 보인다. 뭔가 내가이 없거나 잘 이해하고 있지 않다 있습니까? 누군가가 나에게 CTE의 경우를 만들기 위해 일반 선택, 유도 또는 임시 테이블 쿼리 제한 간단한 예를 주실 수 있습니까? 모든 간단한 예는 높게 평가 될 것이다.

해결법

  1. ==============================

    1.당신이 참조해야하는 경우 한 예로, / 같은 데이터가 CTE를 정의하여 그렇게 할 수있는 여러 시간을 설정 가입 할 수 있습니다. 따라서 코드 재사용의 한 형태가 될 수 있습니다.

    당신이 참조해야하는 경우 한 예로, / 같은 데이터가 CTE를 정의하여 그렇게 할 수있는 여러 시간을 설정 가입 할 수 있습니다. 따라서 코드 재사용의 한 형태가 될 수 있습니다.

    자기 참조의 예는 재귀입니다 : CTE를 사용하여 재귀 쿼리

    흥미로운 마이크로 소프트에 대한 정의 온라인 설명서에서 발췌 :

    CTE를이 할 수 있습니다 :

  2. ==============================

    2.나는 조인 및 하위 쿼리 특히 복잡하고 복잡한 쿼리를 분쇄하는 데 사용할. 나는 나를 쿼리의 의도 주위에 내 머리를 얻을 수 있도록 '의사 뷰'로 더를 사용하고 찾을 수 있습니다.

    나는 조인 및 하위 쿼리 특히 복잡하고 복잡한 쿼리를 분쇄하는 데 사용할. 나는 나를 쿼리의 의도 주위에 내 머리를 얻을 수 있도록 '의사 뷰'로 더를 사용하고 찾을 수 있습니다.

    그들에 대한 나의 유일한 불만은 다시 사용할 수 없습니다입니다. 예를 들어, 나는 같은 CTE를 사용할 수있는 두 개의 업데이트 문 proc 디렉토리에 저장되어있을 수 있습니다. 그러나 CTE의 '범위'는 첫 번째 쿼리입니다.

    문제는 아마 정말 CTE의 필요가 없습니다, '간단한 예'입니다!

    그럼에도 불구하고, 매우 편리합니다.

  3. ==============================

    3.내가 CTE의를 사용하여 볼 두 가지 이유가 있습니다.

    내가 CTE의를 사용하여 볼 두 가지 이유가 있습니다.

    where 절에서 계산 된 값을 사용합니다. 이는 파생 테이블보다는 나에게 조금 청소기를 보인다.

    두 개의 테이블이 있습니다 가정 - Questions.ID = Answers.Question_Id에 의해 함께 결합 질문과 답변 (퀴즈 아이디)

    WITH CTE AS
(
    Select Question_Text,
           (SELECT Count(*) FROM Answers A WHERE A.Question_ID = Q.ID) AS Number_Of_Answers
    FROM Questions Q
)
SELECT * FROM CTE
WHERE Number_Of_Answers > 0

    여기에 내가 질문과 답변 목록을 얻으려면 또 다른 예입니다. 나는 답변이 결과의 질문을 그룹화하고 싶다.

    WITH cte AS
(
    SELECT [Quiz_ID] 
      ,[ID] AS Question_Id
      ,null AS Answer_Id
          ,[Question_Text]
          ,null AS Answer
          ,1 AS Is_Question
    FROM [Questions]

    UNION ALL

    SELECT Q.[Quiz_ID]
      ,[Question_ID]
      ,A.[ID] AS  Answer_Id
      ,Q.Question_Text
          ,[Answer]
          ,0 AS Is_Question
        FROM [Answers] A INNER JOIN [Questions] Q ON Q.Quiz_ID = A.Quiz_ID AND Q.Id = A.Question_Id
)
SELECT 
    Quiz_Id,
    Question_Id,
    Is_Question,
    (CASE WHEN Answer IS NULL THEN Question_Text ELSE Answer END) as Name
FROM cte    
GROUP BY Quiz_Id, Question_Id, Answer_id, Question_Text, Answer, Is_Question 
order by Quiz_Id, Question_Id, Is_Question Desc, Name
  4. ==============================

    4.하나 이상의 열을 기반으로 데이터의 DISTINCT 행을 얻을 수 있지만, 테이블의 모든 열을 반환 할 때이 CTE를 사용하는 것이 유용하다고 시나리오 중 하나입니다. 표준 쿼리로 먼저 임시 테이블에 고유 값을 덤프 한 후 나머지 열을 검색하기 위해 원래 테이블에 다시 가입을 시도해야 할 수도 있습니다 또는 당신은에 결과를 반환 할 수있는 매우 복잡한 파티션 쿼리를 작성할 수 있습니다 하나는 실행되지만 대부분의 가능성, 그것은 읽을 수 및 원인 성능 문제가 될 것입니다.

    하나 이상의 열을 기반으로 데이터의 DISTINCT 행을 얻을 수 있지만, 테이블의 모든 열을 반환 할 때이 CTE를 사용하는 것이 유용하다고 시나리오 중 하나입니다. 표준 쿼리로 먼저 임시 테이블에 고유 값을 덤프 한 후 나머지 열을 검색하기 위해 원래 테이블에 다시 가입을 시도해야 할 수도 있습니다 또는 당신은에 결과를 반환 할 수있는 매우 복잡한 파티션 쿼리를 작성할 수 있습니다 하나는 실행되지만 대부분의 가능성, 그것은 읽을 수 및 원인 성능 문제가 될 것입니다.

    그러나 CTE를 사용하여 (선택에 팀 Schmelter하여 레코드의 첫 번째 인스턴스를 대답으로)

    WITH CTE AS(
    SELECT myTable.*
    , RN = ROW_NUMBER()OVER(PARTITION BY patientID ORDER BY ID)
    FROM myTable 
)
SELECT * FROM CTE
WHERE RN = 1

    당신이 볼 수 있듯이,이 읽기 및 유지 관리가 훨씬 쉽습니다. 그리고 다른 쿼리에 비해 성능이 훨씬 낫다.

  5. ==============================

    5.아마도 그것보다 의미는 단일 쿼리에 사용되는 뷰의 대용으로 CTE 생각합니다. 그러나 공식적인보기의 오버 헤드, 메타 데이터, 또는 지속성을 필요로하지 않습니다. 당신은 매우 유용 할 필요가있을 때 :

    아마도 그것보다 의미는 단일 쿼리에 사용되는 뷰의 대용으로 CTE 생각합니다. 그러나 공식적인보기의 오버 헤드, 메타 데이터, 또는 지속성을 필요로하지 않습니다. 당신은 매우 유용 할 필요가있을 때 :

    여기에 함께 플레이 할 수있는 잘라 내기 및 붙여 넣기의 예는 다음과 같습니다

    WITH [cte_example] AS (
SELECT 1 AS [myNum], 'a num' as [label]
UNION ALL
SELECT [myNum]+1,[label]
FROM [cte_example]
WHERE [myNum] <=  10
)
SELECT * FROM [cte_example]
UNION
SELECT SUM([myNum]), 'sum_all' FROM [cte_example]
UNION
SELECT SUM([myNum]), 'sum_odd' FROM [cte_example] WHERE [myNum] % 2 = 1
UNION
SELECT SUM([myNum]), 'sum_even' FROM [cte_example] WHERE [myNum] % 2 = 0;

    즐겨

  6. ==============================

    6.오늘 우리는뿐만 아니라 SQL 서버 2005 년 도입 이후 버전에서 사용할 된 새로운 기능입니다 공통 테이블 식에 대해 배울 것입니다.

    오늘 우리는뿐만 아니라 SQL 서버 2005 년 도입 이후 버전에서 사용할 된 새로운 기능입니다 공통 테이블 식에 대해 배울 것입니다.

    공통 테이블 표현식 : - 공통 테이블 표현식은 SQL Server의 뷰의 대체 임시 결과 세트 또는 다른 말로 정의 할 수 있습니다. 공통 테이블 표현식이 정의하고 다른 세션에서 사용할 수 없습니다 문을 배치에서만 유효합니다.

    CTE (공통 테이블 식)를 선언하는 구문 : -

    with [Name of CTE]
as
(
Body of common table expression
)

    예를 취할 수 있습니다 : -

    CREATE TABLE Employee([EID] [int] IDENTITY(10,5) NOT NULL,[Name] [varchar](50) NULL)

insert into Employee(Name) values('Neeraj')
insert into Employee(Name) values('dheeraj')
insert into Employee(Name) values('shayam')
insert into Employee(Name) values('vikas')
insert into Employee(Name) values('raj')

CREATE TABLE DEPT(EID INT,DEPTNAME VARCHAR(100))
insert into dept values(10,'IT')
insert into dept values(15,'Finance')
insert into dept values(20,'Admin')
insert into dept values(25,'HR')
insert into dept values(10,'Payroll')

    나는 두 개의 테이블을 직원 및 부서를 만들고 각 테이블에서 5 행을 삽입했습니다. 지금은이 테이블에 가입하고 더 사용하기 위해 임시 결과 세트를 작성하고 싶습니다.

    With CTE_Example(EID,Name,DeptName)
as
(
select Employee.EID,Name,DeptName from Employee 
inner join DEPT on Employee.EID =DEPT.EID
)
select * from CTE_Example

    하나의 말씀 하나의 각 라인을 이해할 수 있습니다.

    우리가 여기에 내가 "CTE_Example"로 이름을 준 다음, 우리는 테이블 식에 이름을주고, 절 "와"쓰기 CTE를 정의하려면

    그리고 우리가 "로"를 작성하고 두 브래킷 (---) 우리의 코드를 동봉 해, 우리는 동봉 된 브라켓에 여러 테이블을 조인 할 수 있습니다.

    우리는 우리가 정의하고 하나의 뷰를 사용하는 뷰와 같은 자사의 말할 수 그래서 마지막 줄에, 나는 "CTE_Example에서 선택 *"우리는 코드의 마지막 줄에 공통 테이블 식을 언급하는 사용했다 배치 및 CTE는 고정 된 물체로 데이터베이스에 저장되지 않습니다. 그러나 뷰처럼 동작합니다. 우리는 CTE에 삭제 및 업데이트 문을 수행 할 수 있습니다 그리고 그 CTE에 사용되는 참조 테이블에 직접 영향을 미칠 것입니다. 이 사실을 이해하기 위해 예를 취할 수 있습니다.

    With CTE_Example(EID,DeptName)
as
(
select EID,DeptName from DEPT 
)
delete from CTE_Example where EID=10 and DeptName ='Payroll'

    위의 문장에서 우리는 CTE_Example에서 행을 삭제하고 그것은 CTE에 사용되는 참조 테이블 "DEPT"에서 데이터를 삭제합니다.

  7. ==============================

    7.당신이 "주문 갱신"을 수행 할 때 매우 유용합니다.

    당신이 "주문 갱신"을 수행 할 때 매우 유용합니다.

    MS SQL은 UPDATE와 ORDER BY를 사용하는 것을 허용하지 않지만, CTE의 도움으로 당신은 그런 식으로 작업을 수행 할 수 있습니다

    WITH cte AS
(
    SELECT TOP(5000) message_compressed, message, exception_compressed, exception
    FROM logs
    WHERE Id >= 5519694 
    ORDER BY Id
)
UPDATE  cte
SET     message_compressed = COMPRESS(message), exception_compressed = COMPRESS(exception)

    추가 정보를 원하시면 여기를 봐 어떻게 갱신 및 주문에 MS의 SQL을 사용하여

  8. ==============================

    8.한 점은 아직 지적하지, 속도입니다. 나는 오래된 대답 질문 알지만, 나는이 직접 코멘트 / 답변을받을 권리가 생각 :

    한 점은 아직 지적하지, 속도입니다. 나는 오래된 대답 질문 알지만, 나는이 직접 코멘트 / 답변을받을 권리가 생각 :

    내가 CTE를 사용하는 경우 I이었다 처음으로 절대적으로 그것의 속도에 의해 기절. 그것은 CTE에 매우 적합 교과서에서 같은 사건이었다,하지만 내가 CTE를 사용하는 모든 ocurences에 상당한 속도 증가가 있었다. 내 첫 번째 쿼리가 실행 긴 분 복용, 파생 테이블이 복잡했다. CTE로 그것은 초 분수를 가져다가 나를 그것도 가능하다는 것을, 충격을 떠났다.

  9. ==============================

    9.

     ;with cte as
  (
  Select Department, Max(salary) as MaxSalary
  from test
  group by department
  )  
  select t.* from test t join cte c on c.department=t.department 
  where t.salary=c.MaxSalary;

    이 시도

    10. from https://stackoverflow.com/questions/4740748/when-to-use-common-table-expression-cte by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 유연한 추상적 인 테이블 대 다수의 열이 하나의 고정 된 테이블  (0) 2020.03.31
[SQL] 기존 테이블에 SQL 서버 SELECT  (0) 2020.03.31
[SQL] 검색 필드에 텍스트를 대체 MYSQL  (0) 2020.03.31
[SQL] 개정을위한 데이터베이스 설계?  (0) 2020.03.31
[SQL] INSERT INTO으로 SQL 서버에서 데이터 내보내기  (0) 2020.03.31
,

티스토리툴바