Lik'homphieutha, Software
Coalesce SQL: tlhaloso, mehlala tšebeliso
Ka ho atamela ho fihlela ho SQL-dipotso atisa ho ba le boemo boo ha boitsebiso sampole lokela ho bapisa melemo ea litšiea tse 'maloa,' me le tlise ba bang ba bona, e le hore e na le ya data (se na letho). Ho rarolla ka bothata bona ka tsela e phethahetseng ho hlahisa maikutlo Coalesce. Sehloohong sena u tla fumana tlhaloso e feletseng ba ho hlahisa maikutlo Coalesce SQL, tlhaloso ea tšebeliso, 'me mehlala e.
Khumo litekanyetso bao e seng letho le Coalesce. Features
Nahana SQL Coalesce sebelisoa ho khetheha:
- E lumella ho bolela ka ho toba palo e 'ngoe ea likhang (ho fapana le Isnull / Nvl / Nvl2, eo ho eona palo ea likhang tse se feela).
- E ka nka e le subqueries khang.
- Returns fella lekana le boleng ea pele ka ntle ho Null kapa Null, haeba tse ling ho feta ka boleng ba Null ka fumanoa.
- SQL Coalesce ka sebelisoa ho khetha Khetha makgabane polelongpalo nonempty, hammoho le ho hloekisa hokae hore na letho lenaneng la sete makgabane e sa lumelloa (/ lumelloa).
- Poleloana ena ke ho lekana le ho sebelisoa ha polelo e reng Case, lekolwang sequentially mong le khang ka boemo Ha argument1 hase hore null ebe argument1. Ha e le hantle, Coalesce ke "e khaoletsang", tse reretsoeng ho phehelleng boiketlo tsa tšebeliso, 'me ka polokelongtshedimosetso ngata potso optimizers ngwaloleswa Coalesce poleloana e reng Case.
- SQL Coalesce mesebetsi ya bona e fumaneha ka tse khōlō relational tsohle tsamaiso ea taolo ya polokelongtshedimosetso.
hokahanngoang Coalesce
Mang kapa mang ea kileng a sebelisa Coalesce lokisetsa SQL-lipotso, oa tseba hore hokahanngoang ba ho hlahisa maikutlo e bonolo ka ho fetisisa. Lekane ho ka parentheses bontša likhang li hlahloba bakeng Null, arohantsoe ke phegelwana. Haeba re nka hore mabaka a ho ke mabitso arg1, arg2, ... argN, Coalesce hokahanngoang e tla ba ka mokgwa o latelang:
Coalesce (arg1, arg2, ... argN).
Hlophisa litafole tse seng kae bakeng sa ho ithuta mochine oa tšebetso ea polelo ena.
ho lokisetsa tafole
Ho utloisisa hantle hore na Coalesce SQL tlhaloso, bopa polokelongtshedimosetso ba litafole tse peli ya data e fana ka tlhahisoleseding e mabapi le thekiso ea matlo.
Tafole ea pele e na le mabitso a Area re thepa le sebakeng tsa bona. sebaka seo e ka e hloekisitsoeng (area_yt) kapa o ile a re (area_decl).
ID | object_name | area_yt | area_decl |
1 | mohaho ona o 1 | 116,2 | 114 |
2 | Felang kaho 1 | ka 568 | |
3 | kamoreng 1 | 64,7 | |
4 | Kamore 2 | 34,2 | |
5 | Plot 1 | 112 | 111,6 |
6 | kaho ea 1 | ||
7 | likamore tse 3 | 27,9 | |
8 | kaho ea 2 | 37,2 | 36,4 |
9 | mohaho ona o 2 |
Tafole ea bobeli Basic_characteristic re na le tlhahisoleseding e mabapi le litšobotsi tsa sehlooho tsa thepa e - katoloso (Extension), botebo (Botebo), sebakeng (Area), bophahamo ba modumo (Boholo), bophahamo ba (Height).
ID | object_name | Extension | botebo | Area | Boholo | bophahamo |
1 | mohaho ona o 1 | 892,4 | 30 | |||
2 | mohaho ona o 2 | 48 | ||||
3 | kaho ea 1 | 164,7 | ||||
4 | Plot 1 | |||||
5 | kamoreng 1 | 23,6 | ||||
6 | Kamore 2 | 34,7 | ||||
7 | likamore tse 3 | 19,8 |
Hlahlojoa Coalesce SQL hokahanngoang tlhaloso, tšebeliso le tsoela pele ka ho toba ho mehlala ena.
mehlala tsa tšebeliso
Hokahanngoang ka Coalesce poleloana ee e bonolo ka ho fetisisa, empa ke habohlokoa hore ba se ke lebala hore ka lebaka la taelo ea tla ba ea pele bao e seng letho boleng fumanoa ka lethathamo khang. lemohileng Sena ke sa bohlokoa haholo, e le mabaka polelong e reng tlameha ho behiloe ka tlhophiso ho ya bohlokwa. Ho bobebe tsela ea ho utloisisa molao-motheo oa sebaka tafole. Bopa potso ee e khetha lebitso la thepa e, hammoho le boleng ba sebaka seo:
Khetha Area.id, Area.object_name, coalesce (Area.area_yt, Area.area_decl) HO TLOHA Area |
Le fumana sephetho:
ID | object_name | Coalesce |
1 | mohaho ona o 1 | 116,2 |
2 | Felang kaho 1 | ka 568 |
3 | kamoreng 1 | 64,7 |
4 | Kamore 2 | 34,2 |
5 | Plot 1 | 112 |
6 | kaho ea 1 | |
7 | likamore tse 3 | 27,9 |
8 | kaho ea 2 | 37,2 |
9 | mohaho ona o 2 |
Bakeng sa morero ona o "Building 1", "Plot 1" le "Sebopeho 2" makgabane ka bobeli ba lisekoere ho li ne li tletse, empa sebaka seo e ile e hloekisitsoeng ka etelletsa pele, ha re ntse re ile a bua ka lenane la khang ea pele. Poleloana e reng Coalesce fumana pele bao e seng null boleng, 'me ba mo tlisa ho emisa ho shebella likhang ka ho eketsehileng. Ena kahong ea potso ee - le tokelo ya, hobane ho feta e hloekisitsoeng sebakeng itseng ho feta a phatlalatsa. Haeba re boletse ka ho hlaka e le khang ea pele ho sebaka o ile a re, ha ho tlatsa tšimong ena ea tafole e ka be ho bile ea bohlokoa.
Ho phaella tabeng ea ho sebelisa ka Khetha, hangata Coalesce poleloana ee e sebediswa le hokae boemo. E lumella ho felisoa ka lebaka la ho mela ka tseo melemo ea lethathamo la masimo a letho (kapa sekgoeng, ho kenyeletsa ka lebaka tseo feela melao ea boitšoaro moo lethathamo tšimo e feela). Boemo bona bo fumanoa hohle: ka mohlala, ka kgwebo le moralo wa tsa mosebeletsi le lecha ho polokelongtshedimosetso tse entsoeng feela lesedi mantlha ka eona, 'me tlatsa le dintlha setseng "bakeng sa hamorao". Butle-butle, e leng "likheo" hlaha - kapa pele cheke, kapa tataiso ea mosebeletsi leetong phomolong / khoebo / sepetlele.
Khetha ho tloha tafoleng litšobotsi tsa sehlooho tsa thepa e lokela ho arajoa le ea mong oa melao ea boitšoaro e khethollang:
Khetha ID, object_name HO TLOHA Basic_characteristic Moo coalesce (Extension, Botebo, Area, Boholo, Height) ke null |
Ke kopo ena ka tafoleng ho na ke e 'ngoe papali - ntho "Plot 1", masimo' ohle a eo litšobotsi tsa letho:
ID | object_name |
4 | Plot 1 |
Re tšepo ea hore rona qaqileng tlhaloso Coalesce SQL thuso u utloisisa tsohle tse hlahang ho sebelisoa ha poleloana ena, hammoho le ho sebetsana le bohlokoa makolopetso.
Similar articles
Trending Now