jó barátom, Aaron Bertrand inspirált, hogy írjam ezt a cikket. Emlékeztetett arra, hogy néha természetesnek vesszük a dolgokat, amikor nyilvánvalónak tűnnek számunkra, és nem mindig foglalkozunk azzal, hogy ellenőrizzük a teljes történetet mögöttük. A T-SQL relevanciája az, hogy néha azt feltételezzük, hogy mindent tudunk bizonyos T-SQL funkciókról, és nem mindig foglalkozunk a dokumentáció ellenőrzésével, hogy lássuk, van-e még több. Ebben a cikkben számos olyan T-SQL funkciót ismertetek, amelyeket vagy gyakran teljesen figyelmen kívül hagynak, vagy amelyek támogatják a gyakran figyelmen kívül hagyott paramétereket vagy képességeket. Ha vannak saját példái a T-SQL drágakövekről, amelyeket gyakran figyelmen kívül hagynak, kérjük, ossza meg ezeket a cikk megjegyzés szakaszában.
mielőtt elkezdené olvasni ezt a cikket, kérdezd meg magadtól, mit tudsz a következő T-SQL funkciókról: EOMONTH, TRANSLATE, TRIM, CONCAT és CONCAT_WS, LOG, kurzor változók és egyesítés a kimenettel.
a példáimban egy TSQLV5 nevű mintadatbázist fogok használni. Az adatbázist létrehozó és feltöltő szkriptet itt, az ER diagramot pedig itt találja.
az EOMONTH-nak van egy második paramétere
az EOMONTH függvényt az SQL Server 2012-ben vezették be. Sokan úgy gondolják, hogy csak egy paramétert támogat, amely egy beviteli dátumot tartalmaz, és egyszerűen visszaadja a beviteli dátumnak megfelelő hónap végi dátumot.
tekintsünk egy kicsit kifinomultabb igényt az előző hónap végének kiszámítására. Tegyük fel például, hogy meg kell kérdeznie az értékesítést.Rendelések táblázat, valamint az előző hónap végén leadott visszaküldési megbízások.
az Egyik módja annak, hogy elérjék ezt kell alkalmazni a EOMONTH funkció SYSDATETIME, hogy a hónap végi dátum az aktuális hónapban, majd alkalmazza a DATEADD funkció fogyni egy hónap az eredmény, így:
USE TSQLV5; SELECT orderid, orderdateFROM Sales.OrdersWHERE orderdate = EOMONTH(DATEADD(month, -1, SYSDATETIME()));
Megjegyezzük, hogy ha valóban futtassa a lekérdezést a TSQLV5 minta adatbázis kapsz egy üres eredménye, mivel az utolsó, sorrendben a felvétel dátuma a táblázat Május 6., 2019. Ha azonban a táblázatban olyan megrendelések voltak, amelyek rendelési dátuma az előző hónap utolsó napjára esik, akkor a lekérdezés ezeket visszaküldte volna.
sokan nem veszik észre, hogy az EOMONTH támogat egy második paramétert, ahol jelzi, hogy hány hónapot kell hozzáadni vagy kivonni. Itt van a függvény szintaxisa:
EOMONTH ( start_date )
feladatunk könnyebben és természetesen megvalósítható, ha egyszerűen megadjuk a -1-et a függvény második paramétereként, így:
A fordítás néha egyszerűbb, mint a csere
sokan ismerik a csere funkciót és annak működését. Akkor használja, ha az egyik részstring összes előfordulását le szeretné cserélni egy bemeneti karakterláncban. Néha azonban, ha több csere van, amelyet alkalmazni kell, a csere használata kissé trükkös, és összetett kifejezéseket eredményez.
például tegyük fel, hogy egy @s bemeneti karakterláncot kap, amely spanyol formázású számot tartalmaz. Spanyolországban egy pontot használnak elválasztóként az ezres csoportok számára, vesszőt pedig tizedes elválasztóként. A bemenetet át kell alakítania amerikai formázásra, ahol vesszőt használnak elválasztóként ezres csoportok számára, tizedes elválasztóként pedig egy pontot.
A REPLACE funkció egyik hívásával csak az egyik karakter vagy alprogram összes előfordulását cserélheti le egy másikra. Két csere alkalmazásához (periódusok vesszőkre és vesszők periódusokra) be kell fészkelnie a függvényhívásokat. A trükkös rész az, hogy ha a REPLACE once-t használja a periódusok vesszőre váltására, majd másodszor az eredmény ellen a vesszők periódusokra változtatására, akkor csak periódusok lesznek. Próbálja ki:
a következő kimenetet kapja:
123.456.789.00
ha ragaszkodni szeretne a csere funkció használatához, három funkcióhívásra van szüksége. Az egyik az időszakok helyettesítésére egy semleges karakterrel, amelyről tudod, hogy általában nem jelenhet meg az adatokban (mondjuk ~). Egy másik az eredmény ellen, hogy az összes vesszőt időszakokkal helyettesítse. Egy másik az eredmény ellen, hogy az ideiglenes karakter összes előfordulását (~ a példánkban) vesszővel helyettesítsük. Itt van a teljes kifejezés:
ezúttal a megfelelő kimenetet kapja:
123,456,789.00
Ez a fajta megvalósítható, de ez azt eredményezi, hogy egy hosszú, tekervényes kifejezés. Mi lenne, ha több pótlást kellene alkalmazni?
sokan nincsenek tisztában azzal, hogy az SQL Server 2017 bevezetett egy új funkciót, A TRANSLATE nevet, amely nagymértékben leegyszerűsíti az ilyen helyettesítéseket. Itt van a függvény szintaxisa:
TRANSLATE ( inputString, characters, translations )
a második bemenet (karakterek) egy karakterlánc, amely tartalmazza a cserélni kívánt egyes karakterek listáját, a harmadik bemenet (fordítások) pedig egy karakterlánc, amely tartalmazza a megfelelő karakterek listáját, amelyekkel le szeretné cserélni a forrás karaktereket. Ez természetesen azt jelenti, hogy a második és a harmadik paraméternek azonos számú karakterrel kell rendelkeznie. A funkció szempontjából az a fontos, hogy nem hajt végre külön lépéseket az egyes helyettesítésekhez. Ha igen, akkor potenciálisan ugyanazt a hibát eredményezte volna, mint az első példában, amelyet a csere funkció két hívásával mutattam be. Következésképpen a feladatunk kezelése nem-agy:
ez a kód generálja a kívánt kimenetet:
123,456,789.00
Ez elég ügyes!
TRIM több, mint LTRIM(RTRIM())
az SQL Server 2017 bemutatta a TRIM funkció támogatását. Sokan, köztük én is, kezdetben csak azt feltételezik, hogy ez nem több, mint egy egyszerű parancsikon az LTRIM-hez(RTRIM(bemenet)). Ha azonban megnézed a dokumentációt, rájössz, hogy valójában erősebb ennél.
mielőtt belemennék a részletekbe, fontolja meg a következő feladatot: adott bemeneti karakterlánc @s, távolítsa el a vezető és a záró perjeleket (előre és hátra). Tegyük fel például, hogy a @s a következő karakterláncot tartalmazza:
//\\ remove leading and trailing backward (\) and forward (/) slashes \\//
a kívánt kimenet:
remove leading and trailing backward (\) and forward (/) slashes
vegye figyelembe, hogy a kimenetnek meg kell őriznie a vezető és a záró szóközöket.
Ha nem tudtál a TRIM teljes képességeiről, a következő módon oldhattad meg a feladatot:
serélje ki az összes szóközt semleges karakterrel ( ~ ), és az előre mutató perjeleket szóközökkel, majd a trim segítségével vágja le a vezető és a záró szóközöket az eredményből. Ez a lépés lényegében levágja a vezető és a hátsó perjeleket, ideiglenesen a ~ – t használva az eredeti terek helyett. Itt van ennek a lépésnek az eredménye:
\\~remove~leading~and~trailing~backward~(\)~and~forward~( )~slashes~\\
a második lépés Ezután a TRANSLATE segítségével az összes szóközt egy másik semleges karakterrel (^) helyettesíti, a fordított perjeleket pedig szóközökkel, majd a TRIM segítségével vágja le az eredményből a vezető és a záró szóközöket. Ez a lépés lényegében levágja a vezető és a hátsó perjeleket, ideiglenesen ^ – t használva a közbenső terek helyett. Itt van ennek a lépésnek az eredménye:
~remove~leading~and~trailing~backward~( )~and~forward~(^)~slashes~
az utolsó lépés A TRANSLATE használatával helyettesíti a szóközöket visszafelé, ^ előre perjelekkel és ~ szóközökkel, létrehozva a kívánt kimenetet:
remove leading and trailing backward (\) and forward (/) slashes
gyakorlatként próbálja meg megoldani ezt a feladatot egy pre-SQL Server 2017 kompatibilis megoldással, ahol nem használhatja a TRIM és a TRANSLATE alkalmazást.
vissza az SQL Server 2017 vagy újabb verziójához, ha nem zavarja a dokumentáció ellenőrzését, akkor felfedezte volna, hogy a TRIM kifinomultabb, mint amit eredetileg gondoltál. Itt van a függvény szintaxisa:
TRIM ( string )
a rész opcionális karakterei lehetővé teszik egy vagy több karakter megadását, amelyeket a bemeneti karakterlánc elejétől a végéig vágni szeretne. Esetünkben csak annyit kell tennie, hogy megadja a ‘/ \ ‘ – t ennek a résznek, így:
Ez elég jelentős javulás az előző megoldáshoz képest!
CONCAT és CONCAT_WS
Ha már egy ideje dolgozol a T-SQL-lel, tudod, milyen kínos a nullákkal foglalkozni, amikor húrokat kell összefűzni. Vegyük például a HR.Employees táblázatban az alkalmazottak számára rögzített helyadatokat:
Ez a lekérdezés a következő kimenetet generálja:
empid country region city----------- --------------- --------------- ---------------1 USA WA Seattle2 USA WA Tacoma3 USA WA Kirkland4 USA WA Redmond5 UK NULL London6 UK NULL London7 UK NULL London8 USA WA Seattle9 UK NULL London
figyeljük meg, hogy egyes alkalmazottak számára a régió része irreleváns, az irreleváns régiót pedig null jelöli. Tegyük fel, hogy össze kell kapcsolnia a helyrészeket (ország, régió és város), vesszővel elválasztva, de figyelmen kívül hagyva a NULL régiókat. Ha a régió releváns, akkor azt szeretné, hogy az eredmény <coutry>,<region>,<city> legyen, ha pedig a régió nem releváns, akkor azt szeretné, hogy az eredmény <country>,<city>legyen. Normális esetben valami null-val való összefűzése NULL eredményt eredményez. Ezt a viselkedést megváltoztathatja a CONCAT_NULL_YIELDS_NULL munkamenet opció kikapcsolásával, de nem javasolnám a nem szabványos viselkedés engedélyezését.
Ha nem tudom, hogy létezik a CONCAT, valamint CONCAT_WS funkciók volna, valószínűleg használt ISNULL vagy ÖSSZEOLVADNAK, hogy cserélje ki egy ÜRES üres string, valahogy így:
SELECT empid, country + ISNULL(',' + region, '') + ',' + city AS locationFROM HR.Employees;
Itt a kimeneti ezt a lekérdezést:
empid location----------- -----------------------------------------------1 USA,WA,Seattle2 USA,WA,Tacoma3 USA,WA,Kirkland4 USA,WA,Redmond5 UK,London6 UK,London7 UK,London8 USA,WA,Seattle9 UK,London
az SQL Server 2012-ben bevezetett, a CONCAT függvény. Ez a függvény elfogadja a karakterlánc bemenetek listáját, összefűzi őket, és eközben figyelmen kívül hagyja a nullákat. Tehát a CONCAT használatával egyszerűsítheti a megoldást:
ennek ellenére a függvény bemeneteinek részeként kifejezetten meg kell adnia az elválasztókat. Az életünk még könnyebbé tétele érdekében az SQL Server 2017 bevezette a CONCAT_WS nevű hasonló funkciót, ahol az elválasztó megjelölésével kezdődik, majd az összefűzni kívánt elemek. Ezzel a funkcióval a megoldás tovább egyszerűsödik:
a következő lépés természetesen mindreading. 1. Április 2020-án a Microsoft a CONCAT_MR kiadását tervezi. a funkció Elfogad egy üres bemenetet, és automatikusan kitalálja, hogy mely elemeket szeretné összefűzni az elméd olvasásával. A lekérdezés így fog kinézni:
A naplónak van egy második paramétere
hasonló az EOMONTH függvényhez, sokan nem veszik észre, hogy már az SQL Server-Rel kezdve 2012, a log funkció támogatja a második paramétert, amely lehetővé teszi a logaritmus alapjának megjelölését. Ezt megelőzően a T-SQL támogatta a LOG(input) függvényt, amely a bemenet természetes logaritmusát adja vissza (az e állandót használja alapként), valamint a LOG10(bemenet) függvényt, amely 10-et használ alapként.
mivel nem voltak tisztában a LOG függvény második paraméterének létezésével, amikor az emberek a Logb(x) – t akarták kiszámítani, ahol b az e és 10-től eltérő alap, gyakran hosszú utat tettek meg. A következő egyenletre támaszkodhat:
példaként a Log2(8) kiszámításához a következő egyenletre támaszkodik:
lefordítva T-SQL-re, a következő számítást alkalmazza:
DECLARE @x AS FLOAT = 8, @b AS INT = 2;SELECT LOG(@x, @b);
kurzor változó
ha már dolgozik a T-SQL egy darabig, akkor valószínűleg volt sok esélye dolgozni kurzorok. Mint tudják, amikor kurzorral dolgozik, általában a következő lépéseket használja:
- deklarálja a kurzort
- nyissa meg a kurzort
- ismételje meg a kurzor rekordokat
- zárja be a kurzort
- Deallocate a kurzort
példaként tegyük fel, hogy adatbázisonként végre kell hajtania valamilyen feladatot a példányában. Kurzor használatával általában a következőkhöz hasonló kódot használ:
a CLOSE parancs felszabadítja az aktuális eredményhalmazt és felszabadítja a zárakat. A DEALLOCATE parancs eltávolítja a kurzor hivatkozását, és amikor az utolsó hivatkozás ki van osztva, felszabadítja a kurzort tartalmazó adatstruktúrákat. Ha kétszer próbálja meg futtatni a fenti kódot a bezárás és a DEALLOCATE parancsok nélkül, akkor a következő hibaüzenet jelenik meg:
Msg 16915, Level 16, State 1, Line 4A cursor with the name 'C' already exists.Msg 16905, Level 16, State 1, Line 6The cursor is already open.
győződjön meg róla, hogy a folytatás előtt futtatja a bezárás és a DEALLOCATE parancsokat.
sokan nem veszik észre, hogy amikor csak egy tételben kell kurzorral dolgozniuk, ami a leggyakoribb eset, a szokásos kurzor használata helyett a kurzor változóval dolgozhat. Mint minden változó, a kurzor változó hatóköre csak az a köteg, ahol deklarálták. Ez azt jelenti, hogy amint egy köteg befejeződik, az összes változó lejár. A kurzor változó használatával, ha egy köteg befejeződik, az SQL Server automatikusan bezárja és deallocates, így a CLOSE and DEALLOCATE parancs futtatásának szükségessége kifejezetten megtakarítható.
itt van a módosított kód egy kurzor változó ezúttal:
nyugodtan hajtsa végre többször, és vegye figyelembe, hogy ezúttal nem kap semmilyen hibát. Csak tisztább, és nem kell aggódnia a kurzor erőforrásainak megtartása miatt, ha elfelejtette bezárni és elosztani a kurzort.
egyesítés a kimenettel
az SQL Server 2005 módosítási utasításainak kimeneti záradékának kezdete óta nagyon praktikus eszköznek bizonyult, amikor módosított sorokból akart adatokat visszaadni. Az emberek rendszeresen használják ezt a funkciót olyan célokra, mint archiválás, auditálás és sok más felhasználási eset. Az egyik bosszantó dolog ezzel a funkcióval kapcsolatban azonban az, hogy ha beszúrási utasításokkal használja, akkor csak a beillesztett sorokból adhat vissza adatokat, a kimeneti oszlopokat pedig beillesztve. Nincs hozzáférése a forrástábla oszlopaihoz, annak ellenére, hogy néha vissza kell adnia a forrás oszlopait a cél oszlopai mellett.
példaként vegye figyelembe a T1 és T2 táblákat, amelyeket a következő kód futtatásával hoz létre és tölt fel:
tegyük fel, hogy át kell másolnia néhány sort T1-ről T2-re; mondjuk azokat, ahol keycol % 2 = 1. A kimeneti záradék segítségével vissza szeretné adni az újonnan létrehozott kulcsokat a T2 – ben, de a kulcsok mellett vissza szeretné adni a megfelelő forráskulcsokat a T1-ből. Az intuitív elvárás a következő INSERT utasítás használata:
sajnos, mint említettük, a kimeneti záradék nem teszi lehetővé, hogy hivatkozzon az oszlopokra a forrás táblából, így a következő hibát kapja:
a többrészes azonosító”T1.keycol ” nem volt kötve.
sokan nem veszik észre, hogy furcsa módon ez a korlátozás nem vonatkozik a MERGE utasításra. Tehát annak ellenére, hogy kissé kínos, konvertálhatja az INSERT utasítást MERGE utasításmá, de ehhez szüksége van arra, hogy az egyesítési predikátum mindig hamis legyen. Ez aktiválja a WHEN NOT MATCHED záradékot, és alkalmazza az egyetlen támogatott beszúrási műveletet. Használhat egy hamis hamis feltételt, például 1 = 2. Itt van a teljes konvertált kód:
ezúttal a kód sikeresen fut, a következő kimenetet produkálva:
T1_keycol T2_keycol----------- -----------1 13 25 3