Coalesce sql: hauj lwm, pab piv txwv

Thaum npaj sql-queries feem ntau ib tug teeb meem no tshwm sim thaum nws yog tsim nyog los sib piv qhov tseem ceeb ntawm ntau yam kab thiab tso zis los ntawm lawv thaum tus qauv ntaub ntawv, uas muaj xws li cov ntaub ntawv (tsis tas). Nws daws qhov teeb meem no txig qhia coalesce. Nyob rau hauv no tsab xov xwm koj yuav nrhiav tau ib tug tag nrho cov hauj lwm uas qhia coalesce sql, hauj lwm ntawm kev pab, thiab cov piv txwv.

Tso zis uas tsis yog-empty qhov tseem ceeb uas coalesce. nta

Xav txog Sql coalesce kev siv:

1. Nws tso cai rau kom meej tej xov tooj ntawm cov lus sib cav (tsis zoo li Isnull / Nvl / Nvl2, nyob rau hauv uas lub xov tooj ntawm cov lus sib cav no tsuas yog muaj).
2. Nws yuav siv li ib tug sib cav subqueries.
3. Rov qab los ua sib npaug zos rau ib tug thawj nqi lwm yam tshaj li thov los yog thov, yog hais tias lwm tshaj ib tug thov nqi yuav tsum pom.
4. Sql coalesce yuav siv tau los xaiv tus Xaiv kab lus nonempty qhov tseem ceeb, raws li zoo raws li cais puab nyob qhov twg uas empty kiag txheej tseem ceeb yog tsis tso cai (/ tso cai).
5. Qhov no qhia yog sib npaug rau cov kev siv cov kev qhia Case, soj ntsuam sequentially txhua sib cav nyob rau hauv kev mob Thaum argument1 yog tsis thov ces argument1. Nyob rau hauv qhov tseeb, coalesce yog ib tug "shortcut", tsim los rau yooj yim ntawm kev siv, thiab nyob rau hauv ntau database lus nug optimizers sau dua tshiab coalesce qhia Case.
6. Sql coalesce zog yog muaj nyob rau hauv tag nrho cov loj paub database tswj systems.

syntax coalesce

Leej twg uas tau puas tau siv lub coalesce nyob rau hauv kev npaj sql-queries, yeej paub tias lub syntax ntawm cov kev qhia yog tsis tshua muaj tej yam yooj yim. Suffice nws sau rau hauv qhia tau tias cov nqe lus yog soj ntsuam txog kev thov, sib cais los ntawm commas. Yog hais tias peb xav hais tias cov nqe lus no yog cov npe arg1, arg2, ... argN, coalesce lub syntax yuav raws li nram no:

Coalesce (arg1, arg2, ... argN).

Npaj ib tug ob peb lub rooj rau kev kawm cov mechanism ntawm lub lag luam ntawm no qhia.

npaj rooj

Yuav kom to taub zoo dua coalesce sql hauj lwm, ua tau ib lub database yog ob lub rooj ntawm cov ntaub ntawv uas muab cov lus qhia nyob rau hauv av.

Tus thawj cov lus muaj cov npe Cheeb Tsam cia zog thiab lawv cheeb tsam. Lub cheeb tsam tej zaum yuav ua neeg dawb huv (area_yt) los yog tshaj tawm hais tias (area_decl).

Qhov thib ob rooj Basic_characteristic cia muaj ntaub ntawv rau ntawm lub ntsiab yam ntxwv ntawm cov cuab yeej - extension (Extension), qhov tob (qhov tob), cheeb tsam (Area), ntim (Scope), qhov siab (siab).

Rov coalesce sql syntax hauj lwm, pab thiab npaj mus ncaj qha mus rau cov piv txwv.

piv txwv ntawm qhov kev siv

Syntax ntawm coalesce qhia yog tsis tshua muaj tej yam yooj yim, tab sis nws yog ib qho tseem ceeb tsis txhob hnov qab tias qhov kawg ntawm qhov hais kom ua yuav tsum yog tus thawj uas tsis yog-empty nqi nyob rau hauv lub cav daim ntawv teev. Qhov no soj yog qho tseem ceeb heev, yog li cov nqe lus nyob rau hauv cov kev qhia yuav tsum tau muab nyob rau hauv kev txiav txim ntawm qhov tseem ceeb. Qhov yooj yim txoj kev uas yuav to taub lub hauv paus ntsiab lus ntawm qhov chaw lub rooj. Tsim ib cov lus nug uas xaiv lub npe ntawm cov cuab yeej, raws li zoo raws li tus nqi ntawm cov cheeb tsam:

Thiab tau txais cov kev tshwm sim:

Rau lub project "tsev 1", "lus 1" thiab "Qauv 2" ob qhov tseem ceeb yog cov square twb tau sau, tab sis lub cheeb tsam tau ua neeg dawb huv nyob rau hauv feem, raws li peb taw tes qhia nws nyob rau hauv daim ntawv teev cov thawj sib cav. Qhia coalesce pom thawj uas tsis yog-thov nqi, thiab coj nws mus rau nres ntxiv saib cov lus. Qhov no siv ntawm cov lus nug - rau txoj cai, vim hais tias ntau kom zoo zoo txog cheeb tsam tshaj qhov tshaj tawm hais tias. Yog hais tias peb tau teev raws li cov thawj sib cav mus rau lub tshaj tawm hais tias cheeb tsam, thaum sau nyob rau hauv daim teb no ntawm lub rooj nws yuav tau ib tug tseem ceeb.

Nyob rau hauv tas li ntawd mus siv nyob rau hauv lub Xaiv, feem ntau coalesce qhia yog siv nrog cov nyob qhov twg mob. Nws tso cai rau txiav tawm los ntawm cov kev tshwm sim ntawm lub kab nyob rau hauv uas qhov tseem ceeb ntawm daim ntawv teev cov teb dawb paug (los yog vice versa, kom muaj nyob rau hauv cov kev tshwm sim tsuas yog cov tseem ceeb nyob qhov twg lub teb daim ntawv no tas). Qhov no qhov teeb meem yog pom txhua qhov txhia chaw: piv txwv li, nyob rau hauv lub enterprise nrog cov qauv ntawm ib tug tshiab neeg ua hauj lwm mus rau lub database tau tsuas yog cov ntaub ntawv hais txog nws, thiab sau cov ntsiab lus tawm "rau tom qab". Maj mam, lub "qag" mam li - los yog ua ntej ntawm daim tshev, los yog nyob rau hauv cov kev coj ntawm ib tug neeg ua hauj lwm nyob rau hauv lub caij so / ua lag ua luam mus txawv tebchaws / tsev kho mob.

Xaiv los ntawm lub rooj lub ntsiab yam ntxwv ntawm cov khoom uas yuav tsum tau teb tsis muaj leej twg ntawm cov yam ntxwv tseem ceeb:

Los ntawm qhov kev thov no nyob rau hauv lub rooj muaj ib tug match - tus kwv "zajlus 1", tag nrho cov teb nyob rau hauv uas tus yam ntxwv ntawm lub dawb paug:

Peb cia siab tias peb paub piav qhia txog coalesce sql pab koj to taub tag nrho cov nta ntawm kev siv ntawm no qhia, raws li zoo raws li deal nrog ib qho tseem ceeb nuances.