Makombiyuta, Software
Coalesce SQL: tsananguro, kushandiswa mienzaniso
Paunogadzirira SQL-queries kazhinji mamiriro ezvinhu inomuka kana zvakakodzera kuenzanisa tsika siyana mbiru uye goho kwavari kana mharidzo data, izvo zvinosanganisira mashoko (kwete chinhu). Anogadziriswa dambudziko iri zvakakwana mashoko Coalesce. Munyaya ino uchawana tsanangudzo okuti Coalesce SQL, tsananguro kushandiswa, uye mienzaniso azere.
Output vasiri chiro tsika pamwe Coalesce. Features
Funga SQL Coalesce chaiyo kushandiswa:
- Kunoitawo kuti tsanangurai chero nhamba kukakavadzana (kusiyana Isnull / Nvl / Nvl2, umo nhamba kupopotedzana shoma).
- Zvinogona kutora ive nharo subqueries.
- Returns kuguma akaenzana ukoshi wokutanga asiri null kana null, kana mumwe pane null ukoshi anogona kuwanikwa.
- SQL Coalesce anogona kushandiswa kusarudza Select mutongo nonempty tsika, uyewo kunatsiridza kupi kuti chinhu Mbiru yakatarwa tsika hazvibvumirwi (/ vakabvumira).
- Mashoko aya ari yakaenzana kushandisa mashoko Case, ndakaongorora sequentially nharo nomumwe pamusoro ezvinhu Kana argument1 hakusi null ipapo argument1. Kutaura zvazviri, Coalesce ari "yokudimbudzira", rakagadzirirwa nyore, uye vakawanda Database mubvunzo optimizers nyorazve Coalesce mashoko Case.
- SQL Coalesce mabasa zvinowanikwa zvose zvikuru ndeyeukama Database utariri enyika.
nemarongerwo Coalesce
Munhu wose akambomira vaishandisa Coalesce mukugadzirira SQL-queries, anoziva kuti nemarongerwo rokuti zvikuru nyore. Zvakakwana muzvikomberedzo anoratidza nharo dziri zvakaongororwa null, akaparadzaniswa nevanhu nezvitura befu. Kana taifunga kuti mibvunzo ndiwo mazita arg1, arg2, ... argN, Coalesce kuti nemarongerwo achava sezvinotevera:
Coalesce (arg1, arg2, ... argN).
Gadzirirai tafura shoma nokuda kudzidza mashandiro kushanda kwemashoko aya.
gadziriro tafura
Kuti tinzwisise Coalesce SQL tsananguro nani, kusika Database vari matafura maviri mashoko kuti anopa mashoko pamusoro Real Estate.
Tafura yokutanga ine mazita Area regai zvivako uye avo munharaunda. Nzvimbo anogona Munatswe (area_yt) kana akataura (area_decl).
ID | object_name | area_yt | area_decl |
1 | chivakwa 1 | 116,2 | 114 |
2 | Konekwa kuvakwa 1 | 568 | |
3 | kamuri 1 | 64,7 | |
4 | The kamuri 2 | 34,2 | |
5 | Plot 1 | 112 | 111,6 |
6 | kuvakwa 1 | ||
7 | Rooms 3 | 27.9 | |
8 | kuvakwa 2 | 37,2 | 36.4 |
9 | chivakwa 2 |
The tafura wechipiri Basic_characteristic kutendera rine mashoko pamusoro mukuru zvechadenga pfuma - chiwedzerwa (Extension), udzamu (Depth), nharaunda (Area), Volume (Makuriro), nekukwirira (Height).
ID | object_name | ekisitenjeni | udzamu | nharaunda | kukura | urefu |
1 | chivakwa 1 | 892,4 | 30 | |||
2 | chivakwa 2 | 48 | ||||
3 | kuvakwa 1 | 164,7 | ||||
4 | Plot 1 | |||||
5 | kamuri 1 | 23.6 | ||||
6 | The kamuri 2 | 34.7 | ||||
7 | Rooms 3 | 19.8 |
Aongorora Coalesce SQL nemarongerwo description, kushandiswa uye mberi zvakananga mienzaniso.
mienzaniso kushandiswa
Nemarongerwo pana Coalesce okuti zvikuru nyore, asi zvakakosha Haazombokanganwi kuti mugumisiro murayiro uchava wokutanga non-chinhu ukoshi dziri nharo mazita. Maonero Izvi zvinokosha zvikuru, saka nharo okuti zvinofanira muhurongwa kukosha. Nyore nzira kunzwisisa musimboti nzvimbo patafura. Itai imwe mubvunzo kuti anosarudza zita pfuma, uyewo kukosha nzvimbo:
Sarudza Area.id, Area.object_name, coalesce (Area.area_yt, Area.area_decl) FROM Area |
Uye kuwana mugumisiro:
ID | object_name | Coalesce |
1 | chivakwa 1 | 116,2 |
2 | Konekwa kuvakwa 1 | 568 |
3 | kamuri 1 | 64,7 |
4 | The kamuri 2 | 34,2 |
5 | Plot 1 | 112 |
6 | kuvakwa 1 | |
7 | Rooms 3 | 27.9 |
8 | kuvakwa 2 | 37,2 |
9 | chivakwa 2 |
Nokuti chirongwa "Building 1", "Plot 1" uye "Structure 2" vose tsika ndidzo sikweya vakazadzwa, asi nzvimbo yacho yakaidzwa chokutanga, sezvo isu akataura nawo mazita wokutanga nharo. Mashoko Coalesce akawana yokutanga vasiri null akakoshesa, ndokumuisa kurega zvimwe kuona nenharo. kuvakwa ichi mubvunzo - rworudyi, nekuti zvakawanda yakanatswa dzakananga nharaunda pane rakaziviswa. Kana isu raanotaura sezvo wokutanga nharo kusvika akataura nzvimbo, apo uchinyora munda iyi patafura zvaizova chinhu chokutanga.
Kuwedzera kushandisa iri Sarudza, kazhinji Coalesce mashoko rinoshandiswa pamwe Where ezvinhu. Kunoitawo kuti ndiparadzemo mugumisiro mechidziro apo tsika pamazita minda varara (kana ndiEzekieri, kuti zvinyorwe mugumisiro chete tsika avo apo munda mazita ari chinhu). Izvi zvinowanikwa kwese-kwese, somuenzaniso, ari zvebhizimisi pamwe magadzirirwe itsva mushandi kuna Database akaita mashoko anokosha chete nezvazvo, uye achizadza mashoko akasiya "kuti gare gare." Zvishoma nezvishoma, "Makwanza" buda - kana pamberi cheki, kana panguva mirayiridzo mushandi pazororo / bhizimisi rwendo / kuchipatara.
Sarudza kubva patafura chikuru unhu zvivako zvine kupindurwa hapana mamirire tsika:
Sarudza ID, object_name FROM Basic_characteristic Apo coalesce (Extension, Depth, Area, Makuriro, Height) ndiyo null |
By chikumbiro ichi mukati tafura pane mumwe mutambo - chinhu "Plot 1", zvose neminda umo zvinoitwa kunyorwa:
ID | object_name |
4 | Plot 1 |
Tinovimba kuti yedu rondedzero Coalesce SQL rubatsiro iwe kunzwisisa zvose zviri kushandiswa aya, uyewo kuitira nuances anokosha.
Similar articles
Trending Now