Coalesce SQL: inkcazelo, imizekelo ukubala

Xa ulungiselela SQL-imibuzo rhoqo kuvela imeko xa kuyimfuneko lokuthelekisa amaxabiso lwemihlathi ezininzi kunye neziphumo kubo xa oluqokelelweyo yisampulu nje, equka data (hayi engenanto). It uyayisombulula le ngxaki ngokugqibeleleyo mazwi Coalesce. Kweli nqaku uza kufumana inkcazelo epheleleyo yokuthetha Coalesce SQL, inkcazo yokusetyenziswa kwayo, kunye nemizekelo.

Isiphumo lwamaxabiso non-ezingenanto kunye Coalesce. iimpawu

Cinga SQL Coalesce ukuzisebenzisa:

1. It ikuvumela ukuba ukhankanye naliphi na inani iingxoxo (ngokungafaniyo Isnull / Nvl / Nvl2, apho inani iimpikiswano kuphela).
2. Kunokuthatha njenge subqueries ingxabano.
3. Ibuyisela umphumela ulingana ixabiso lokuqala ngaphandle Null okanye Null, ukuba ngaphandle kokuba ixabiso Null inokufumaneka.
4. SQL Coalesce ingasetyenziswa ukhethe Khetha amaxabiso isigwebo nonempty, kwakunye ukucola Apho ukuba amaxabiso ezingenanto column iseti akuvumelekanga (/ kuvunyelwe).
5. Eli binzana kulingana ngexabiso ukusetyenziswa Case binzana, zitshekishwe nangendlela ecwangciswe kakuhle ngokulandelelana ingxoxo nganye kwi imeko Xa argument1 ayikho null ke argument1. Enyanisweni, Coalesce i "indlela emfutshane", yenzelwe usebenziso olulula, yaye database ezininzi optimizers zombuzo ubhale Coalesce case.
6. Imisebenzi SQL Coalesce ziyafumaneka kuzo zonke ezinkulu zonxulumelwano iinkqubo zolawulo ledatha.

syntax Coalesce

Nabani na okhe wasebenzisa Coalesce ekulungiseleleni SQL-imibuzo, uyazi ukuba syntax binzana elula kakhulu. Kwanele kwindawo yegama zibonisa iimpikiswano lukhangelwa Null, wodwa ngeziphumlisi. Xa sicingela ukuba iziphakamiso amagama ARG1, ARG2, ... argN, Coalesce i lwesivakalisi iza kuba ngolu hlobo lulandelayo:

Coalesce (ARG1, ARG2, ... argN).

Lungisa i tafile ezimbalwa ukwenzela ukufunda indlela yokusebenza yeli binzana.

itheyibhile kulungiselelwa

Ukuze siqonde ngcono Coalesce SQL inkcazelo, ukudala kuvimba iitafile ezimbini data zinika ingcaciso izindlu.

Le theyibhile yokuqala iqulethe amagama Area ke iimpawu kunye kwindawo yabo. Le ndawo iya kuba luvavanyo (area_yt) okanye athi (area_decl).

Le theyibhile yesibini Basic_characteristic ke iqulethe ulwazi iimpawu eziphambili wepropati - ulwandiso (Extension), ubunzulu (Ubunzulu), indawo (Area), umthamo (Scope), ubude (ubude).

Yavandlakanya Coalesce inkcazelo kwezivakalsi SQL, ukusetyenziswa uze uqhubeke ngqo kule mizekelo.

imizekelo yokusetyenziswa

Yobeko e Coalesce binzana ilula kakhulu, kodwa kubalulekile ukuba ukulibala ukuba umphumo umyalelo uya kuba ixabiso lokuqala non-engenanto ezifumaneka kuluhlu ingxabano. Lo mazwi kubaluleke kakhulu, ngoko ke iingxoxo yebinzana kufuneka abekwe ngokolandelelwano ngokubaluleka. Indlela elula ukuqonda siseko isithuba itafile. Yenza umbuzo ukuba ukhetha igama propati, kwakunye nexabiso le ndawo:

Kwaye ufumane isiphumo:

Kuba iprojekthi "Building 1", "Plot 1" kunye "Ulwakhiwo 2" amaxabiso omabini angu isikwere bahlutha, kodwa lendawo esulungekiswe ephambili, njengoko waveza oko kuluhlu lwe ingxabano kuqala. Expression Coalesce wafumana ixabiso lokuqala non-null, wamsa ukuba ayeke iingxoxo phambili ukubukela. Le ukwakhiwa yombuzo - ilungelo, ngenxa yokuba indawo ethile, yaza yahluzwa ngaphezu obhengezwe. Ukuba siye acaciswe njengoko ingxabano ukuya kwindawo kuqala, xa ngokuzalisa eli candelo etafileni bekungaba iphambili.

Ukongeza ukusebenzisa kwi Khetha, ngokufuthi Coalesce binzana lisetyenziswa kunye Apho imeko. It ivumela ukuba kunqumla umphumo imiqolo apho amaxabiso uluhlu amasimi engabhalwanga (okanye ngenye indlela, ku katsa ni ku result kuphela ezo amaxabiso apho uluhlu akananto). Le meko ufunyanwa kuyo yonke indawo, umzekelo, xa kwishishini ekuyilweni ungumqeshwa omtsha kwiziko ledatha wenza ulwazi kuphela ezingundoqo ngayo, wazalisa iinkcukacha ekhohlo 'ukuba kamva ". Ngokuthe ngcembe, i "izikhewu" ivele - okanye phambi itshekhi, okanye kolwalathiso umqeshwa ekhefini / ishishini uhambo / esibhedlele.

Khetha etafileni iimpawu eziphambili weepropati ekufuneka uphendulwe namnye yeenqobo iimpawu:

Xa esi sicelo kule theyibhile kukho omnye umdlalo - i-object "Plot 1", onke amasimi apho iimpawu kungekhonto:

Sinethemba lokuba yethu inkcazelo eneenkcukacha Coalesce SQL uncedo uqonde zonke iimpawu ukusetyenziswa kwalo eli binzana, kwakunye deal with bokuqonda ebalulekileyo.