Származtatott táblákat használó lekérdezések írása
A származtatott táblák lehetővé teszik, hogy modulárisabb Transact-SQL-utasításokat írjon, így könnyebben kezelhető részekre bonthatja az összetett lekérdezéseket. A származtatott táblák lekérdezésekben való használata áthidaló megoldásokat is kínálhat a lekérdezésfeldolgozás logikai sorrendje által előírt korlátozások némelyikére, például az oszlop aliasok használatára.
Az al lekérdezésekhez hasonlóan származtatott táblákat is létrehozhat egy külső Standard kiadás LECT utasítás FROM záradékában. Az aljegyzőktől eltérően a származtatott táblákat egy táblával logikailag egyenértékű, névvel ellátott kifejezéssel kell írnia, és a külső lekérdezés más részén táblaként hivatkozhat rá.
A származtatott táblákat a rendszer nem tárolja az adatbázisban. Ezért a lekérdezések származtatott táblák használatával történő írásához nincs szükség különleges biztonsági jogosultságokra, kivéve a forrásobjektumok közül való kiválasztáshoz szükséges jogosultságokat. A származtatott tábla a külső lekérdezés végrehajtásakor jön létre, és a külső lekérdezés befejeződésekor kimegy a hatókörből. A származtatott táblák nem feltétlenül befolyásolják a teljesítményt, összehasonlítva ugyanazzal a lekérdezéssel, amelyet másként fejeznek ki. A lekérdezés feldolgozásakor a rendszer kicsomagolja és kiértékeli az utasítást a mögöttes adatbázis-objektumok alapján.
Eredmények visszaadése származtatott táblák használatával
Származtatott tábla létrehozásához írjon be egy belső lekérdezést zárójelek közé, majd egy AS záradékot és a származtatott tábla nevét a következő szintaxissal:
SELECT <outer query column list>
FROM (SELECT <inner query column list>
FROM <table source>) AS <derived table alias>
Egy származtatott táblával például lekérheti az egyes ügyfelek által évente leadott rendelésekre vonatkozó információkat:
SELECT orderyear, COUNT(DISTINCT custid) AS cust_count
FROM (SELECT YEAR(orderdate) AS orderyear, custid
FROM Sales.Orders) AS derived_year
GROUP BY orderyear;
A belső lekérdezés megrendelések készletét hozza létre, és a származtatott tábla származtatott évébe helyezi. A külső lekérdezés a származtatott táblán működik, és összegzi az eredményeket. Az eredmények a következőképpen néznek ki:
| orderyear | cust_count |
|---|---|
| 2019 | 67 |
| 2020 | 86 |
| 2021 | 81 |
Argumentumok átadása származtatott tábláknak
A származtatott táblák elfogadhatják a hívási rutinból átadott argumentumokat, például Transact-SQL-köteget, függvényt vagy tárolt eljárást. Származtatott táblákat írhat helyőrzőként szolgáló helyi változókkal. Futásidőben a helyőrzők lecserélhetők a kötegben megadott értékekre vagy a lekérdezést meghívó tárolt eljárás paraméterként átadott értékeire. Ez lehetővé teszi a kód rugalmasabb újrafelhasználását, mint ugyanazt a lekérdezést minden alkalommal más értékekkel újraírni.
Az alábbi köteg például deklarál egy helyi változót (a szimbólummal @ jelölve) az alkalmazotti azonosítóhoz, majd az SQL Server azon képességét használja, hogy ugyanabban az utasításban adjon hozzá egy értéket a változóhoz. A lekérdezés elfogadja a @emp_id változót, és a származtatott táblakifejezésben használja:
DECLARE @emp_id INT = 9; --declare and assign the variable
SELECT orderyear, COUNT(DISTINCT custid) AS cust_count
FROM (
SELECT YEAR(orderdate) AS orderyear, custid
FROM Sales.Orders
WHERE empid=@emp_id --use the variable to pass a value to the derived table query
) AS derived_year
GROUP BY orderyear;
GO
Származtatott táblákat használó lekérdezések írásakor tartsa szem előtt az alábbi irányelveket:
- A származtatott táblát definiáló beágyazott Standard kiadás LECT utasításnak hozzá kell rendelnie egy aliast. A külső lekérdezés az aliast a Standard kiadás LECT utasításban ugyanúgy fogja használni, mint a FROM záradékban összekapcsolt aliastáblákra.
- A származtatott tábla Standard kiadás LECT záradékában hivatkozott összes oszlophoz aliasokat kell hozzárendelni, ami a Transact-SQL-ben nem mindig szükséges ajánlott eljárás. Minden aliasnak egyedinek kell lennie a kifejezésben. Az oszlop aliasai az oszlopokkal beágyazottan vagy a záradékon kívül deklarálhatók.
- A származtatott táblakifejezést meghatározó Standard kiadás LECT utasítás nem használhat ORDER BY záradékot, kivéve, ha TARTALMAZ TOP operátort, OFF Standard kiadás T/FETCH záradékot vagy FOR XML záradékot. Ennek eredményeképpen a származtatott tábla nem biztosít rendezési sorrendet. Az eredményeket a külső lekérdezésben rendezheti.
- A származtatott táblát meghatározó Standard kiadás LECT utasítás írható úgy, hogy az argumentumokat helyi változók formájában fogadja el. Ha a Standard kiadás LECT utasítás beágyazott egy tárolt eljárásba, az argumentumok megírhatók az eljárás paramétereként.
- A külső lekérdezésbe beágyazott származtatott táblakifejezések más származtatott táblakifejezéseket is tartalmazhatnak. A beágyazás engedélyezett, de a megnövekedett összetettség és a csökkent olvashatóság miatt nem ajánlott.
- A származtatott táblákra külső lekérdezésben nem lehet többször hivatkozni. Ha ugyanazokat az eredményeket kell módosítania, minden alkalommal meg kell határoznia a származtatott táblakifejezést, például egy JOIN operátor mindkét oldalán.