CTE yog dab tsi hauv SQL Server nrog piv txwv?
CTE yog dab tsi hauv SQL Server nrog piv txwv?

Video: CTE yog dab tsi hauv SQL Server nrog piv txwv?

Video: CTE yog dab tsi hauv SQL Server nrog piv txwv?
Video: Leng Yang - Yog Vim Dab Tsi (Official Full Song | Nkauj Tawm Tshiab) 2020/05/24 2024, Lub peb hlis ntuj
Anonim

A CTE ( Common Table Expression ) yog ib qho kev tshwm sim ib ntus uas koj tuaj yeem xa mus rau lwm qhov SELECT, INSERT, UPDATE, lossis DELETE nqe lus. Lawv tau qhia hauv SQL Server version 2005. Nco tseg: Tag nrho cov piv txwv rau zaj lus qhia no yog ua raws li Microsoft SQL Server Management Studio thiab AdventureWorks2012 database.

Dhau li ntawd, vim li cas peb thiaj siv CTE hauv SQL Server?

Vim li cas rau siv a CTE Hauv SQL ntawv , peb yuav siv sub-queries los koom nrog cov ntaub ntawv los yog lim cov ntaub ntawv los ntawm cov lus nug sub. Thaum twg peb xa tib cov ntaub ntawv los yog koom nrog tib cov ntaub ntawv teev tseg siv sub-query, cov cai tswj kev ruaj ntseg yuav nyuaj. A CTE ua kom zoo nyeem ntawv thiab kev saib xyuas yooj yim dua.

Qhov thib ob, Kuv yuav siv ob CTE hauv SQL li cas? Rau siv ntau CTE's nyob rau hauv ib qho lus nug koj tsuas yog yuav tsum ua kom tiav thawj zaug CTE , ntxiv ib qho comma, tshaj tawm lub npe thiab xaiv kab rau lwm qhov CTE , qhib CTE query nrog ib tug comma, sau cov lus nug, thiab nkag tau los ntawm a CTE cov lus nug tom qab hauv tib cov lus nug lossis los ntawm cov lus nug zaum kawg sab nraum CTEs.

Ua raws li qhov no, peb puas tuaj yeem siv CTE hauv kev saib hauv SQL Server?

SQL Server CTE Cov hauv paus. Koj ua tau kuj siv a CTE hauv CREATE SAIB nqe lus, raws li ib feem ntawm lub saib SELECT cov lus nug. Tsis tas li ntawd, raws li ntawm SQL Server 2008, koj ua tau add ib CTE mus rau MERGE cov lus qhia tshiab. SQL Server txhawb nqa ob hom CTEs-recursive thiab nonrecursive.

Puas yog CTEs sai dua subqueries?

Kev ua haujlwm ntawm CTEs thiab subqueries yuav tsum, nyob rau hauv txoj kev xav, yuav zoo ib yam vim hais tias ob leeg muab tib cov ntaub ntawv rau cov lus nug optimizer. Ib qhov txawv yog qhov a CTE siv ntau dua tshaj ib zaug tuaj yeem txheeb xyuas tau yooj yim thiab suav ib zaug. Nyob rau hauv lub ntiaj teb zoo tagnrho, cov lus nug optimizer yuav pom txoj hauv kev ua tiav zoo.

Pom zoo: