II. SQL: 2008
II-A. News
The function:
TRIM_ARRAY (table, n)
... which can be extracted under a table.
A clause limiting the number of line with a request that delight afficionados MySQL: OFFSET / FETCH.
New functions Window: ntile, lead, lag, first value, last value, nth value.
The order TRUNCATE TABLE with an option for the value of restarting the identity column.
II-B. Modifications
Some minor improvements have been made on the following: table, multiset, types distincs, cursors and self increments.
II-C. Syntaxes
Syntax new SQL: 2008
-- Packages of lines
NTILE (<number of tiles>)
<number of tiles>: =
(<simple Value specification> | <dynamic parameter specification>)
-- Previous value, following
LEAD (|) LAG (<extent> [, <offset> [<default expression>]]) [<null treatment>]
<extent>: =
<value expression>
<offset>: =
<exact numeric>
<default expression>: =
<value expression>
<null treatment>: =
COMPLIANCE NULLS (|) IGNORE NULLS
-- First, last value
FIRST_VALUE (|) LAST_VALUE (<value expression>) [<null treatment>]
-- Nth value
NTH_VALUE (<value expression>, <nth row>) [<from first or last>] [<null treatment>]
<nth row>.: =
(<simple Value specification> | <dynamic parameter specification>)
<from first or last>: =
FROM FIRST (| FROM LAST)
-- Return lines from ...
OFFSET <offset row count> ROW (|) ROWS
-- No return lines
FETCH FIRST (| NEXT) [<fetch first row count>] ROW (|) ONLY ROWS
<offset row count>: =
<simple value specification>
<fetch first row count>: =
<simple value specification>
II-D. Examples
Examples of the new SQL: 2008
-- After the 25th line, take the following 10 lines in the order of ORDER BY clause.
SELECT *
FROM T_CLIENT_CLI
ORDER BY 1
OFFSET 25 FETCH NEXT 10 ROWS
-- Grouping in 10 packets for each department, the common names of each department
SELECT CMN_DEPARTEMENT, CMN_NUM_DEPARTEMENT,
NTILE (10) OVER (CMN_DEPARTEMENT PARTITION BY ORDER BY CMN_NOM) AS GROUPE_DEPARTEMENT
FROM T_COMMUNE_CMN
-- A common list of departments, with no preceding and following department
SELECT CMN_NON_DEPARTEMENT, CMN_NOM, ROW_NUMBER ()
LEAD (CMN_NUM_DEPARTEMENT, 1, 0, IGNORE NULLS) OVER (ORDER BY CMN_NUM_DEPARTEMENT) AS ABOVE
LAG (CMN_NUM_DEPARTEMENT, 1, 0, IGNORE NULLS) OVER (ORDER BY CMN_NUM_DEPARTEMENT) AS NEXT
-- To obtain for each department, the town's largest in terms of demographics
SELECT CMN_DEPARTEMENT, CMN_NUM_DEPARTEMENT,
FIRST_VALUE (CMN_NOM) OVER (CMN_DEPARTEMENT PARTITION BY ORDER BY CMN_DEMOGRAPHIE DESC) AS VILLE_PLUS_PEUPLEE
FROM T_COMMUNE_CMN
III. Conclusions
It is of course that the new and future standard SQL: 2008 is nothing much new to the basics of language.
It is a minor, in the spirit of relational / object, which fixes some defects and small gaps in previous versions (SQL: 1999 and SQL: 2003).
|
