Coalesce SQL: incazelo, izibonelo yokusetshenziswa

Lapho ulungiselela SQL-imibuzo ngokuvamile kwenzeka lapho kubalulekile ukuze uqhathanise amanani lamakholomu amaningi futhi okukhipha kuzo lapho idatha isampula, okubandakanya idatha (hhayi lutho). Okuyixazululayo le nkinga kahle inkulumo coalesce. Kulesi sihloko uzothola incazelo egcwele yokukhuluma coalesce SQL, incazelo ukusetshenziswa, futhi izibonelo.

Lokukhipha yamanani elinotho nge coalesce. Izici

Cabanga SQL coalesce ukusetshenziswa Ikakhulukazi:

1. It ivumela ucacise noma iyiphi inombolo agumenti (ngokungafani Isnull / Nvl / Nvl2, lapho inani agumenti kukhawulwe).
2. Kungathatha njengenhlangano subqueries agumenti.
3. Ibuyisa umphumela okulingana Inani lokuqala ngaphandle Null noma Null, uma ngaphandle value Null kungatholakala.
4. SQL coalesce ingasetshenziswa ukuze ukhethe Khetha umusho nonempty amagugu, kanye ithuba lokucwengisiswa kuphi ukuthi awunalutho ikholomu isethi amagugu akuvunyelwe (/ kuvunyelwe).
5. Le nkulumo okulingana ukusetshenziswa yokukhuluma Case, ihlolwe ngokulandelana ngalunye lwempikiswano ngombandela Lapho argument1 akuyona null ke argument1. Eqinisweni, coalesce kuyinto "ukunqamulela", yenzelwe lula ukusetshenziswa, futhi abaningi semininingwane nombuzo optimizers ubhale coalesce Inkulumo Case.
6. SQL coalesce imisebenzi etholakala ngazo zonke zingubudlelwane ezinkulu ngabaphathi semininingwane izinhlelo.

-syntax coalesce

Noma ubani oke waba wasebenzisa coalesce ekulungiseleleni SQL-imibuzo, uyazi ukuthi i-syntax Inkulumo ezinzima elula. Engingakusho nje kubakaki ukhombise amaphuzu kuhlolelwe Null, ahlukaniswe ikhefana. Uma sicabanga ukuthi agumenti angamagama ARG1, ARG2, ... argN, coalesce syntax kuyoba kanje:

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

Lungiselela amatafula ambalwa sokutadisha indlela yokusebenza le nkulumo.

ithebula ukulungiselela

Ukuze uqonde kangcono incazelo SQL coalesce, ukudala database kukhona izibhebhe ezimbili idatha kuhlinzeke imininingwane ngokufinyelela kwi ingcebo.

Ithebula lokuqala liqukethe amagama Area ake izakhiwo kanye endaweni yabo. Le ndawo kungenzeka elicwengisiswe (area_yt) noma njengoba kumemezela (area_decl).

Ithebula lesibili Basic_characteristic ake iqukethe ulwazi izici eyinhloko impahla - isandiso (Extension), ukujula (Ukujula), endaweni (Area), ivolumu (Ububanzi), ukuphakama (Ukuphakama).

Kubuyekezwe coalesce incazelo lesinthekisi SQL, ukusetshenziswa kanye uqhubeke ngqo izibonelo.

izibonelo yokusetshenziswa

Syntax at coalesce Inkulumo ezinzima elula, kodwa kubalulekile ukuba singakhohlwa ukuthi umphumela umyalo kuyoba Inani lokuqala elinotho ezitholakala uhlu impikiswano. Lokhu okwashiwo yilo mlobi kubaluleke kakhulu, ngakho-agumenti ethi kufanele luhlelwe kuhleleka ngekubaluleka. Indlela elula ukuqonda isimiso isikhala etafuleni. Dala nombuzo ukukhethela igama laleyo ndawo, kanye ukubaluleka kwendawo:

Futhi uthole umphumela:

Ukuze iphrojekthi "Isakhiwo 1", "Icebo 1" futhi "Ukwakheka 2" kokubili wamanani isikwele basutha, kodwa le ndawo zazishiswe kuqala, njengoba thina wakhomba ke ohlwini i-agumenti yokuqala. Isengezo coalesce bathole enotho Inani lokuqala, bamyisa ukuyeka eminye agumenti wokubuka. Lokhu ukwakhiwa nombuzo - ilungelo, ngoba sangempela endaweni ethize ngaphandle nguwe. Uma siye esibekiwe njengoba i-agumenti yokuqala endaweni wathi kubo: uma ugcwalisa kulo mkhakha etafuleni kwakuyoba kube into eza kuqala.

Ngaphezu kwalokho ukusebenzisa Khetha, ngokuvamile coalesce nkulumo isetshenziswe ne Lapho isimo. It ivumela ukuba nginqume umphumela imigqa lapho abakuzuzile uhlu isinkambu zingenalutho (noma okuphambene nalokho, ukumfaka yi kuphela lezo zindinganiso lapho uhlu inkundla yesikhungo ingenalutho). Lesi simo zisakazeke: isibonelo, inkampani ekuklanyweni umsebenzi omusha database wenza ulwazi kuphela eziyisisekelo ngakho, futhi ukugcwalisa imininingwane kwesokunxele "ngoba kamuva". Kancane kancane, "izikhala" ziyaphuma - noma ngaphambi isheke, noma ngesiqondiso umsebenzi eholidini / ibhizinisi uhambo / esibhedlela.

Khetha kusuka etafuleni izici eziyinhloko izakhiwo kufanele noyedwa amagugu isici waphendula:

Ngu lesi sicelo ethebuleni kukhona omunye umdlalo - into "Icebo 1", nawo onke amasimu lapho izici nalutho:

Sithemba ukuthi incazelo enemininingwane yethu coalesce SQL kusize ukuba niqonde zonke izici ukusetshenziswa kwale nkulumo, kanye ziphathelene umehluko wemisindo nemiqondo yamagama ebalulekile.