106
Microsoft SELECT Tansact SQL 2014 Este documento se proporciona "tal cual". La información y los puntos de vista expresados en este documento, incluyendo las referencias a sitios web de Internet y direcciones URL, está sujeta a cambios sin aviso. Este documento no implica ningún derecho legal respecto a ninguna propiedad intelectual de ningún nombre de producto o producto de Microsoft. Puede copiar y utilizar este documento con fines internos y de referencia. Se permite que modifique este documento para sus fines internos y de referencia. © 2013 Microsoft. Reservados todos los derechos. Términos de uso (http://msdn.microsoft.com/cc300389.aspx) | Marcas comerciales (http://www.microsoft.com/library/toolbar/3.0/trademarks/en-us.mspx)

Microsoft SELECT Transact SQL 2014

Embed Size (px)

Citation preview

Page 1: Microsoft SELECT Transact SQL 2014

Microsoft SELECT Tansact SQL

2014

Este documento se proporciona "tal cual". La información y los puntos de vista expresados en este documento, incluyendo las referencias a sitios web deInternet y direcciones URL, está sujeta a cambios sin aviso. Este documento no implica ningún derecho legal respecto a ninguna propiedad intelectual de ningún

nombre de producto o producto de Microsoft. Puede copiar y utilizar este documento con fines internos y de referencia. Se permite que modifique este

documento para sus fines internos y de referencia. © 2013 Microsoft. Reservados todos los derechos. Términos de uso(http://msdn.microsoft.com/cc300389.aspx) | Marcas comerciales (http://www.microsoft.com/library/toolbar/3.0/trademarks/en-us.mspx)

Page 3: Microsoft SELECT Transact SQL 2014

SELECT Transact SQL 2014

Page 4: Microsoft SELECT Transact SQL 2014

SELECT (Transact-SQL)

Recupera filas de la base de datos y habilita la selección de una o varias filas o columnas de una o varias tablas en SQL Server. La sintaxis completa de la instrucciónSELECT es compleja, aunque las cláusulas principales se pueden resumir del modo siguiente:

[ WITH <common_table_expression>]

SELECT select_list [ INTO new_table ]

[ FROM table_source ] [ WHERE search_condition ]

[ GROUP BY group_by_expression ]

[ HAVING search_condition ]

[ ORDER BY order_expression [ ASC | DESC ] ]

Los operadores UNION, EXCEPT e INTERSECT se pueden utilizar entre consultas para combinar o comparar resultados en un conjunto de resultados.

Se aplica a: SQL Server (desde SQL Server 2008 hasta la versión actual﴿, Windows Azure SQL Database ﴾desde la versión inicial hasta la versión actual).

Convenciones de sintaxis de Transact-SQL

Sintaxis

Comentarios

Debido a la complejidad de la instrucción SELECT, se muestran elementos y argumentos detallados de la sintaxis de cada cláusula:

WITH common_table_expression HAVING

Cláusula SELECT UNION

Cláusula INTO EXCEPT e INTERSECT

FROM ORDER BY

WHERE Cláusula FOR

GROUP BY Cláusula OPTION

El orden de las cláusulas en la instrucción SELECT es importante. Se puede omitir cualquiera de las cláusulas opcionales pero, cuando se utilizan, deben aparecer en elorden apropiado.

Las instrucciones SELECT se permiten en las funciones definidas por el usuario solo si las listas de selección de estas instrucciones contienen expresiones que asignanvalores a variables locales de las funciones.

Un nombre de cuatro partes creado con la función OPENDATASOURCE como la parte de nombre de servidor se puede utilizar como origen de tabla en todos loslugares de las instrucciones SELECT donde pueda aparecer un nombre de tabla. No se puede especificar un nombre de cuatro partes para Windows Azure SQL

Database.

Existen algunas restricciones sintácticas en las instrucciones SELECT relacionadas con las tablas remotas.

SQL Server 2014

<SELECT statement> ::=

[WITH <common_table_expression> [,...n]]

<query_expression>

[ ORDER BY { order_by_expression | column_position [ ASC | DESC ] }

[ ,...n ] ]

[ <FOR Clause>]

[ OPTION ( <query_hint> [ ,...n ] ) ]

<query_expression> ::=

{ <query_specification> | ( <query_expression> ) }

[ { UNION [ ALL ] | EXCEPT | INTERSECT }

<query_specification> | ( <query_expression> ) [...n ] ]

<query_specification> ::=

SELECT [ ALL | DISTINCT ]

[TOP ( expression ) [PERCENT] [ WITH TIES ] ]

< select_list >

[ INTO new_table ]

[ FROM { <table_source> } [ ,...n ] ]

[ WHERE <search_condition> ]

[ <GROUP BY> ]

[ HAVING < search_condition > ]

Page 5: Microsoft SELECT Transact SQL 2014

Orden de procesamiento lógico de la instrucción SELECTLos pasos siguientes muestran el orden de procesamiento lógico, u orden de enlaces, de una instrucción SELECT. Este orden determina cuándo los objetos definidos enun paso están disponibles para las cláusulas en pasos posteriores. Por ejemplo, si el procesador de consultas puede enlazar (obtener acceso) a las tablas o las vistas

definidas en la cláusula FROM, estos objetos y sus columnas están disponibles para todos los pasos siguientes. Por el contrario, dado que la cláusula SELECT es elpaso 8, las cláusulas anteriores no pueden hacer referencia a los alias de columna o columnas derivadas definidas en esa cláusula. Sin embargo, las cláusulassiguientes, tales como la cláusula ORDER BY, sí pueden hacer referencia. Observe que la ejecución física real de la instrucción está determinada por el procesador deconsultas y el orden puede variar en esta lista.

1. FROM

2. ON

3. JOIN

4. WHERE

5. GROUP BY

6. WITH CUBE o WITH ROLLUP

7. HAVING

8. SELECT

9. DISTINCT

10. ORDER BY

11. TOP

Permisos

La selección de datos necesita el permiso SELECT en la tabla o en la vista, que se puede heredar de un ámbito superior como el permiso SELECT en el esquema o el

permiso CONTROL en la tabla. O bien, se necesita pertenecer a los roles fijos de base de datos db_datareader o db_owner, o al rol fijo de servidor sysadmin. La

creación de una nueva tabla mediante SELECTINTO también necesita tanto el permiso CREATETABLE como el permiso ALTERSCHEMA en el esquema al que pertenece

la nueva tabla.

Vea también

ReferenciaEjemplos de SELECT (Transact-SQL)

© 2014 Microsoft

Page 6: Microsoft SELECT Transact SQL 2014

SELECT ﴾cláusula de Transact‐SQL﴿

Especifica las columnas que la consulta debe devolver.

Se aplica a: SQL Server (SQL Server 2008 a versión actual﴿, Windows Azure SQL Database ﴾Versión inicial a versión actual).

Convenciones de sintaxis de Transact-SQL

Sintaxis

Argumentos

ALL

Especifica que el conjunto de resultados puede incluir filas duplicadas. ALL es el valor predeterminado.

DISTINCT

Especifica que el conjunto de resultados solo puede incluir filas únicas. Los valores NULL se consideran iguales desde el punto de vista de la palabra clave

DISTINCT.

TOP (expression ) [ PERCENT ] [ WITH TIES ]

Indica que el conjunto de resultados de la consulta solamente devolverá un primer conjunto o porcentaje de filas especificado. expression puede ser un número oun porcentaje de las filas.

Por compatibilidad con versiones anteriores, se permite el uso de TOP expression sin paréntesis en las instrucciones SELECT, aunque no se recomienda. Para

obtener más información, vea TOP (Transact-SQL).

< select_list >

Columnas que se seleccionarán para el conjunto de resultados. La lista de selección es una serie de expresiones separadas por comas. El número máximo deexpresiones que se puede especificar en la lista de selección es 4.096.

*

Especifica que se deben devolver todas las columnas de todas las tablas y vistas de la cláusula FROM. Las columnas se devuelven por tabla o vista, tal como se

especifique en la cláusula FROM, en el orden en que se encuentran en la tabla o vista.

table_name | view_name | table_alias.*

Limita el ámbito de * a la tabla o vista especificada.

column_name

Es el nombre de una columna que se va a devolver. Para impedir referencias ambiguas, como sucedería si dos tablas de la cláusula FROM tuvieran columnas connombres duplicados, se debe calificar column_name. Por ejemplo, las tablas SalesOrderHeader y SalesOrderDetail de la base de datos AdventureWorks2012

tienen ambas una columna denominada ModifiedDate. Si se combinan las dos tablas en una consulta, se puede especificar la fecha de modificación de lasentradas SalesOrderDetail en la lista de selección como SalesOrderDetail.ModifiedDate.

expression

Es una constante, una función o una combinación de nombres de columna, constantes y funciones conectados mediante un operador, varios operadores o unasubconsulta.

$IDENTITY

Devuelve la columna de identidad. Para obtener más información, vea IDENTITY (propiedad de Transact-SQL), ALTER TABLE (Transact-SQL) y CREATE TABLE (SQL

Server).

Si más de una tabla de la cláusula FROM contiene una columna con la propiedad IDENTITY, se debe calificar $IDENTITY con el nombre de tabla específico; porejemplo, T1.$IDENTITY.

$ROWGUID

Devuelve la columna GUID de fila.

Si más de una tabla de la cláusula FROM tiene la propiedad ROWGUIDCOL, se debe calificar $ROWGUID con el nombre de tabla específico; por ejemplo,

SQL Server 2014

SELECT [ ALL | DISTINCT ]

[ TOP ( expression ) [ PERCENT ] [ WITH TIES ] ]

<select_list>

<select_list> ::=

{

*

| { table_name | view_name | table_alias }.*

| {

[ { table_name | view_name | table_alias }. ]

{ column_name | $IDENTITY | $ROWGUID }

| udt_column_name [ { . | :: } { { property_name | field_name }

| method_name ( argument [ ,...n] ) } ]

| expression

[ [ AS ] column_alias ]

}

| column_alias = expression

} [ ,...n ]

Page 7: Microsoft SELECT Transact SQL 2014

T1.$ROWGUID.

udt_column_name

Es el nombre de la columna que tiene un tipo CLR (Common Language Runtime) definido por el usuario que se va a devolver.

Nota

SQL Server Management Studio devuelve los valores de los tipos definidos por el usuario en representación binaria. Para devolver los valores de los tipos

definidos por el usuario en formato XML o de cadena, use CAST o CONVERT.

{ . | :: }

Especifica un método, una propiedad o un campo de un tipo definido por el usuario CLR. Use . para métodos, propiedades o campos de instancia ﴾noestáticos﴿. Use :: para métodos, propiedades o campos estáticos. Para invocar un método, una propiedad o un campo de un tipo definido por el usuario CLR,debe disponer de permiso EXECUTE en el tipo.

property_name

Es una propiedad pública de udt_column_name.

field_name

Es un miembro de datos público de udt_column_name.

method_name

Es un método público udt_column_name que utiliza uno o varios argumentos. method_name no puede ser un método mutador.

En el ejemplo siguiente se seleccionan los valores de la columna Location, definida como de tipo point, de la tabla Cities, mediante la invocación de unmétodo del tipo denominado Distance:

column_ alias

Es un nombre alternativo que se utiliza para reemplazar el nombre de la columna en el conjunto de resultados de la consulta. Por ejemplo, se puede especificar

un alias como Quantity, Quantity to Date o Qty para una columna denominada quantity.

Los alias se emplean también para especificar nombres para los resultados de expresiones; por ejemplo:

USE AdventureWorks2012;

GO

SELECT AVG(UnitPrice) AS [Average Price]

FROM Sales.SalesOrderDetail;

column_alias se puede utilizar en una cláusula ORDER BY. Sin embargo, no puede utilizarse en una cláusula WHERE, GROUP BY o HAVING. Si la expresión de laconsulta forma parte de una instrucción DECLARE CURSOR, en la cláusula FOR UPDATE no se puede utilizar column_alias.

Comentarios

La longitud de los datos devueltos para las columnas text o ntext incluidas en la lista de selección se establece en el valor menor de los siguientes: el tamaño real dela columna text, la configuración predeterminada de TEXTSIZE para la sesión o el límite de la aplicación codificado de forma rígida. Para cambiar la longitud del texto

devuelto de la sesión, utilice la instrucción SET. De forma predeterminada, la longitud máxima de los datos de texto que se devuelven con una instrucción SELECT es de4.000 bytes.

El Motor de base de datos de SQL Server provoca la excepción 511 y revierte la instrucción que se está ejecutando en ese momento si se produce alguno de estoscomportamientos:

La instrucción SELECT produce una fila de resultados o una fila de la tabla de trabajo intermedia que supera los 8.060 bytes.

La instrucción DELETE, INSERT o UPDATE intenta realizar una acción en una fila que supera los 8.060 bytes.

Se produce un error si no se proporciona un nombre a una columna creada con una instrucción SELECT INTO o CREATE VIEW.

Vea también

ReferenciaEjemplos de SELECT (Transact-SQL)

Expresiones (Transact-SQL)

SELECT (Transact-SQL)

CREATE TABLE dbo.Cities (

Name varchar(20),

State varchar(20),

Location point );

GO

DECLARE @p point (32, 23), @distance float;

GO

SELECT Location.Distance (@p)

FROM Cities;

Page 8: Microsoft SELECT Transact SQL 2014

© 2014 Microsoft

Page 9: Microsoft SELECT Transact SQL 2014

Ejemplos de SELECT (Transact-SQL)

En este tema se proporcionan ejemplos del uso de la instrucción SELECT.

A.Usar SELECT para recuperar filas y columnas

En el siguiente ejemplo se muestran tres fragmentos de código. En el primer ejemplo de código, se devuelven todas las filas ﴾no se especifica la cláusula WHERE﴿ ytodas las columnas (con *) de la tabla Product de la base de datos AdventureWorks2012 .

En este ejemplo se devuelven todas las filas ﴾no se ha especificado la cláusula WHERE﴿ y solo un subconjunto de las columnas ﴾Name, ProductNumber, ListPrice) de la

tabla Product de la base de datos AdventureWorks2012 . Además, se agrega un encabezado de columna.

En este ejemplo solo se devuelven las filas de Product que tienen una línea de productos de R y cuyo valor correspondiente a los días para fabricar es inferior a 4.

B.Usar SELECT con encabezados de columna y cálculos

En los siguientes ejemplos se devuelven todas las filas de la tabla Product. En el primer ejemplo se devuelven las ventas totales y los descuentos de cada producto. En

el segundo ejemplo se calculan los beneficios totales de cada producto.

Ésta es la consulta que calcula el beneficio de cada producto de cada pedido de venta.

SQL Server 2014

USE AdventureWorks2012;

GO

SELECT *

FROM Production.Product

ORDER BY Name ASC;

-- Alternate way.

USE AdventureWorks2012;

GO

SELECT p.*

FROM Production.Product AS p

ORDER BY Name ASC;

GO

USE AdventureWorks2012;

GO

SELECT Name, ProductNumber, ListPrice AS Price

FROM Production.Product

ORDER BY Name ASC;

GO

USE AdventureWorks2012;

GO

SELECT Name, ProductNumber, ListPrice AS Price

FROM Production.Product

WHERE ProductLine = 'R'

AND DaysToManufacture < 4

ORDER BY Name ASC;

GO

USE AdventureWorks2012;

GO

SELECT p.Name AS ProductName,

NonDiscountSales = (OrderQty * UnitPrice),

Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount)

FROM Production.Product AS p

INNER JOIN Sales.SalesOrderDetail AS sod

ON p.ProductID = sod.ProductID

ORDER BY ProductName DESC;

GO

USE AdventureWorks2012;

GO

SELECT 'Total income is', ((OrderQty * UnitPrice) * (1.0 - UnitPriceDiscount)), ' for ',

p.Name AS ProductName

Transact-SQL

Transact-SQL

Transact-SQL

Transact-SQL

Transact-SQL

Page 10: Microsoft SELECT Transact SQL 2014

C.Usar DISTINCT con SELECT

En el siguiente ejemplo se utiliza DISTINCT para evitar la recuperación de títulos duplicados.

D.Crear tablas con SELECT INTO

En el primer ejemplo se crea una tabla temporal denominada #Bicycles en tempdb.

En el segundo ejemplo se crea la tabla permanente NewProducts.

E.Usar subconsultas correlacionadas

En el siguiente ejemplo se muestran consultas que son semánticamente equivalentes y se demuestra la diferencia entre la utilización de la palabra clave EXISTS y la

palabra clave IN. Ambos son ejemplos de subconsultas válidas que recuperan una instancia de cada nombre de producto cuyo modelo es un jersey de manga largacon logotipo y cuyos números de ProductModelID coinciden en las tablas Product y ProductModel.

FROM Production.Product AS p

INNER JOIN Sales.SalesOrderDetail AS sod

ON p.ProductID = sod.ProductID

ORDER BY ProductName ASC;

GO

USE AdventureWorks2012;

GO

SELECT DISTINCT JobTitle

FROM HumanResources.Employee

ORDER BY JobTitle;

GO

USE tempdb;

GO

IF OBJECT_ID (N'#Bicycles',N'U') IS NOT NULL

DROP TABLE #Bicycles;

GO

SELECT *

INTO #Bicycles

FROM AdventureWorks2012.Production.Product

WHERE ProductNumber LIKE 'BK%';

GO

USE AdventureWorks2012;

GO

IF OBJECT_ID('dbo.NewProducts', 'U') IS NOT NULL

DROP TABLE dbo.NewProducts;

GO

ALTER DATABASE AdventureWorks2012 SET RECOVERY BULK_LOGGED;

GO

SELECT * INTO dbo.NewProducts

FROM Production.Product

WHERE ListPrice > $25

AND ListPrice < $100;

GO

ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL;

GO

USE AdventureWorks2012;

GO

SELECT DISTINCT Name

FROM Production.Product AS p

WHERE EXISTS

(SELECT *

FROM Production.ProductModel AS pm

WHERE p.ProductModelID = pm.ProductModelID

AND pm.Name LIKE 'Long-Sleeve Logo Jersey%');

GO

-- OR

Transact-SQL

Transact-SQL

Transact-SQL

Transact-SQL

Page 11: Microsoft SELECT Transact SQL 2014

En el siguiente ejemplo se utiliza IN en una subconsulta correlativa o repetitiva. Se trata de una consulta que depende de la consulta externa de sus valores. Se ejecuta

varias veces, una vez por cada fila que pueda seleccionar la consulta externa. Esta consulta recupera una instancia del nombre y apellido de cada empleado cuya

bonificación en la tabla SalesPerson sea de 5000.00 y cuyos números de identificación coincidan en las tablas Employee y SalesPerson.

La subconsulta anterior de esta instrucción no se puede evaluar independientemente de la consulta externa. Necesita el valor Employee.EmployeeID, aunque este valor

cambia a medida que el Motor de base de datos de SQL Server examina diferentes filas de Employee.

Una subconsulta correlativa se puede usar también en la cláusula HAVING de una consulta externa. En este ejemplo se buscan los modelos cuyo precio máximo essuperior al doble de la media del modelo.

En este ejemplo se utilizan dos subconsultas correlativas para buscar los nombres de los empleados que han vendido un producto específico.

F.Usar GROUP BY

En este ejemplo se busca el total de cada pedido de venta de la base de datos.

USE AdventureWorks2012;

GO

SELECT DISTINCT Name

FROM Production.Product

WHERE ProductModelID IN

(SELECT ProductModelID

FROM Production.ProductModel

WHERE Name LIKE 'Long-Sleeve Logo Jersey%');

GO

USE AdventureWorks2012;

GO

SELECT DISTINCT p.LastName, p.FirstName

FROM Person.Person AS p

JOIN HumanResources.Employee AS e

ON e.BusinessEntityID = p.BusinessEntityID WHERE 5000.00 IN

(SELECT Bonus

FROM Sales.SalesPerson AS sp

WHERE e.BusinessEntityID = sp.BusinessEntityID);

GO

USE AdventureWorks2012;

GO

SELECT p1.ProductModelID

FROM Production.Product AS p1

GROUP BY p1.ProductModelID

HAVING MAX(p1.ListPrice) >= ALL

(SELECT AVG(p2.ListPrice)

FROM Production.Product AS p2

WHERE p1.ProductModelID = p2.ProductModelID);

GO

USE AdventureWorks2012;

GO

SELECT DISTINCT pp.LastName, pp.FirstName

FROM Person.Person pp JOIN HumanResources.Employee e

ON e.BusinessEntityID = pp.BusinessEntityID WHERE pp.BusinessEntityID IN

(SELECT SalesPersonID

FROM Sales.SalesOrderHeader

WHERE SalesOrderID IN

(SELECT SalesOrderID

FROM Sales.SalesOrderDetail

WHERE ProductID IN

(SELECT ProductID

FROM Production.Product p

WHERE ProductNumber = 'BK-M68B-42')));

GO

USE AdventureWorks2012;

GO

SELECT SalesOrderID, SUM(LineTotal) AS SubTotal

FROM Sales.SalesOrderDetail

GROUP BY SalesOrderID

ORDER BY SalesOrderID;

GO

Transact-SQL

Transact-SQL

Transact-SQL

Transact-SQL

Page 12: Microsoft SELECT Transact SQL 2014

Debido a la cláusula GROUP BY, solo se devuelve una fila que contiene la suma de todas las ventas por cada pedido de venta.

G.Usar GROUP BY con varios grupos

En este ejemplo se busca el precio medio y la suma de las ventas anuales hasta la fecha, agrupados por Id. de producto e Id. de oferta especial.

H.Usar GROUP BY y WHERE

En el siguiente ejemplo se colocan los resultados en grupos después de recuperar únicamente las filas con precios superiores a $1000.

I.Usar GROUP BY con una expresión

En este ejemplo se agrupa por una expresión. Puede agrupar por una expresión si ésta no incluye funciones de agregado.

J.Usar GROUP BY con ORDER BY

En este ejemplo se busca el precio medio de cada tipo de producto y se ordenan los resultados por precio medio.

K.Usar la cláusula HAVING

En el primer ejemplo se muestra una cláusula HAVING con una función de agregado. Agrupa las filas de la tabla SalesOrderDetail por Id. de producto y elimina

aquellos productos cuyas cantidades de pedido medias son cinco o menos. En el segundo ejemplo se muestra una cláusula HAVING sin funciones de agregado.

USE AdventureWorks2012;

GO

SELECT ProductID, SpecialOfferID, AVG(UnitPrice) AS [Average Price],

SUM(LineTotal) AS SubTotal

FROM Sales.SalesOrderDetail

GROUP BY ProductID, SpecialOfferID

ORDER BY ProductID;

GO

USE AdventureWorks2012;

GO

SELECT ProductModelID, AVG(ListPrice) AS [Average List Price]

FROM Production.Product

WHERE ListPrice > $1000

GROUP BY ProductModelID

ORDER BY ProductModelID;

GO

USE AdventureWorks2012;

GO

SELECT AVG(OrderQty) AS [Average Quantity],

NonDiscountSales = (OrderQty * UnitPrice)

FROM Sales.SalesOrderDetail

GROUP BY (OrderQty * UnitPrice)

ORDER BY (OrderQty * UnitPrice) DESC;

GO

USE AdventureWorks2012;

GO

SELECT ProductID, AVG(UnitPrice) AS [Average Price]

FROM Sales.SalesOrderDetail

WHERE OrderQty > 10

GROUP BY ProductID

ORDER BY AVG(UnitPrice);

GO

USE AdventureWorks2012;

GO

Transact-SQL

Transact-SQL

Transact-SQL

Transact-SQL

Transact-SQL

Page 13: Microsoft SELECT Transact SQL 2014

En esta consulta se utiliza la cláusula LIKE en la cláusula HAVING.

L.Usar HAVING y GROUP BY

En el siguiente ejemplo se muestra el uso de las cláusulas GROUP BY, HAVING, WHERE y ORDER BY en una instrucción SELECT. Genera grupos y valores de resumen pero lo

hace tras eliminar los productos cuyos precios superan los 25 $ y cuyas cantidades de pedido medias son inferiores a 5. También organiza los resultados porProductID.

M.Usar HAVING con SUM y AVG

En el siguiente ejemplo se agrupa la tabla SalesOrderDetail por Id. de producto y solo se incluyen aquellos grupos de productos cuyos pedidos suman más de$1000000.00 y cuyas cantidades de pedido medias son inferiores a 3.

Para ver los productos cuyas ventas totales son superiores a $2000000.00, utilice esta consulta:

Si desea asegurarse de que hay al menos mil quinientos elementos para los cálculos de cada producto, use HAVING COUNT(*) > 1500 para eliminar los productos que

devuelven totales inferiores a 1500 elementos vendidos. La consulta sería la siguiente:

SELECT ProductID

FROM Sales.SalesOrderDetail

GROUP BY ProductID

HAVING AVG(OrderQty) > 5

ORDER BY ProductID;

GO

USE AdventureWorks2012 ;

GO

SELECT SalesOrderID, CarrierTrackingNumber

FROM Sales.SalesOrderDetail

GROUP BY SalesOrderID, CarrierTrackingNumber

HAVING CarrierTrackingNumber LIKE '4BD%'

ORDER BY SalesOrderID ;

GO

USE AdventureWorks2012;

GO

SELECT ProductID

FROM Sales.SalesOrderDetail

WHERE UnitPrice < 25.00

GROUP BY ProductID

HAVING AVG(OrderQty) > 5

ORDER BY ProductID;

GO

USE AdventureWorks2012;

GO

SELECT ProductID, AVG(OrderQty) AS AverageQuantity, SUM(LineTotal) AS Total

FROM Sales.SalesOrderDetail

GROUP BY ProductID

HAVING SUM(LineTotal) > $1000000.00

AND AVG(OrderQty) < 3;

GO

USE AdventureWorks2012;

GO

SELECT ProductID, Total = SUM(LineTotal)

FROM Sales.SalesOrderDetail

GROUP BY ProductID

HAVING SUM(LineTotal) > $2000000.00;

GO

USE AdventureWorks2012;

GO

SELECT ProductID, SUM(LineTotal) AS Total

FROM Sales.SalesOrderDetail

GROUP BY ProductID

HAVING COUNT(*) > 1500;

GO

Transact-SQL

Transact-SQL

Transact-SQL

Transact-SQL

Page 14: Microsoft SELECT Transact SQL 2014

N.Usar la sugerencia del optimizador INDEX

En el ejemplo siguiente se muestran dos formas de usar la sugerencia del optimizador INDEX. En el primer ejemplo se muestra cómo obligar al optimizador a que useun índice no clúster para recuperar filas de una tabla, mientras que en el segundo ejemplo se obliga a realizar un recorrido de tabla mediante un índice igual a 0.

M.Usar OPTION y las sugerencias GROUP

En el ejemplo siguiente se muestra cómo se usa la cláusula OPTION (GROUP) con una cláusula GROUP BY.

O.Usar la sugerencia de consulta UNION

En el ejemplo siguiente se usa la sugerencia de consulta MERGE UNION.

P.Usar una instrucción UNION simple

En el ejemplo siguiente, el conjunto de resultados incluye el contenido de las columnas ProductModelID y Name de las tablas ProductModel y Gloves.

USE AdventureWorks2012;

GO

SELECT pp.FirstName, pp.LastName, e.NationalIDNumber

FROM HumanResources.Employee AS e WITH (INDEX(AK_Employee_NationalIDNumber))

JOIN Person.Person AS pp on e.BusinessEntityID = pp.BusinessEntityID

WHERE LastName = 'Johnson';

GO

-- Force a table scan by using INDEX = 0.

USE AdventureWorks2012;

GO

SELECT pp.LastName, pp.FirstName, e.JobTitle

FROM HumanResources.Employee AS e WITH (INDEX = 0) JOIN Person.Person AS pp

ON e.BusinessEntityID = pp.BusinessEntityID

WHERE LastName = 'Johnson';

GO

USE AdventureWorks2012;

GO

SELECT ProductID, OrderQty, SUM(LineTotal) AS Total

FROM Sales.SalesOrderDetail

WHERE UnitPrice < $5.00

GROUP BY ProductID, OrderQty

ORDER BY ProductID, OrderQty

OPTION (HASH GROUP, FAST 10);

GO

USE AdventureWorks2012;

GO

SELECT BusinessEntityID, JobTitle, HireDate, VacationHours, SickLeaveHours

FROM HumanResources.Employee AS e1

UNION

SELECT BusinessEntityID, JobTitle, HireDate, VacationHours, SickLeaveHours

FROM HumanResources.Employee AS e2

OPTION (MERGE UNION);

GO

USE AdventureWorks2012;

GO

IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL

DROP TABLE dbo.Gloves;

GO

-- Create Gloves table.

SELECT ProductModelID, Name

INTO dbo.Gloves

FROM Production.ProductModel

WHERE ProductModelID IN (3, 4);

GO

Transact-SQL

Transact-SQL

Transact-SQL

Transact-SQL

Page 15: Microsoft SELECT Transact SQL 2014

Q.Usar SELECT INTO con UNION

En el ejemplo siguiente, la cláusula INTO de la segunda instrucción SELECT especifica que la tabla denominada ProductResults contiene el conjunto final de resultados

de la unión de las columnas designadas de las tablas ProductModel y Gloves. Tenga en cuenta que la tabla Gloves se crea en la primera instrucción SELECT.

R.Usar UNION con dos instrucciones SELECT y ORDER BY

El orden de algunos parámetros empleados con la cláusula UNION es importante. En el ejemplo siguiente se muestra el uso correcto e incorrecto de UNION en dos

instrucciones SELECT en las que se va a cambiar el nombre de una columna en el resultado.

-- Here is the simple union.

USE AdventureWorks2012;

GO

SELECT ProductModelID, Name

FROM Production.ProductModel

WHERE ProductModelID NOT IN (3, 4)

UNION

SELECT ProductModelID, Name

FROM dbo.Gloves

ORDER BY Name;

GO

USE AdventureWorks2012;

GO

IF OBJECT_ID ('dbo.ProductResults', 'U') IS NOT NULL

DROP TABLE dbo.ProductResults;

GO

IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL

DROP TABLE dbo.Gloves;

GO

-- Create Gloves table.

SELECT ProductModelID, Name

INTO dbo.Gloves

FROM Production.ProductModel

WHERE ProductModelID IN (3, 4);

GO

USE AdventureWorks2012;

GO

SELECT ProductModelID, Name

INTO dbo.ProductResults

FROM Production.ProductModel

WHERE ProductModelID NOT IN (3, 4)

UNION

SELECT ProductModelID, Name

FROM dbo.Gloves;

GO

SELECT ProductModelID, Name

FROM dbo.ProductResults;

USE AdventureWorks2012;

GO

IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL

DROP TABLE dbo.Gloves;

GO

-- Create Gloves table.

SELECT ProductModelID, Name

INTO dbo.Gloves

FROM Production.ProductModel

WHERE ProductModelID IN (3, 4);

GO

/* INCORRECT */

USE AdventureWorks2012;

GO

SELECT ProductModelID, Name

FROM Production.ProductModel

WHERE ProductModelID NOT IN (3, 4)

ORDER BY Name

UNION

SELECT ProductModelID, Name

FROM dbo.Gloves;

GO

/* CORRECT */

Transact-SQL

Transact-SQL

Page 16: Microsoft SELECT Transact SQL 2014

S.Usar UNION de tres instrucciones SELECT para mostrar los efectos de ALL y los paréntesis

En los siguientes ejemplos se utiliza UNION para combinar los resultados de tres tablas que tienen las mismas 5 filas de datos. En el primer ejemplo se utiliza UNION ALL

para mostrar los registros duplicados y se devuelven las 15 filas. En el segundo ejemplo se utiliza UNION sin ALL para eliminar las filas duplicadas de los resultados

combinados de las tres instrucciones SELECT y se devuelven 5 filas.

En el tercer ejemplo se utiliza ALL con el primer UNION y los paréntesis incluyen al segundo UNION que no utiliza ALL. El segundo UNION se procesa en primer lugar

porque se encuentra entre paréntesis. Devuelve 5 filas porque no se utiliza la opción ALL y se quitan los duplicados. Estas 5 filas se combinan con los resultados del

primer SELECT mediante las palabras clave UNION ALL. Esto no quita los duplicados entre los dos conjuntos de 5 filas. El resultado final es de 10 filas.

USE AdventureWorks2012;

GO

SELECT ProductModelID, Name

FROM Production.ProductModel

WHERE ProductModelID NOT IN (3, 4)

UNION

SELECT ProductModelID, Name

FROM dbo.Gloves

ORDER BY Name;

GO

USE AdventureWorks2012;

GO

IF OBJECT_ID ('dbo.EmployeeOne', 'U') IS NOT NULL

DROP TABLE dbo.EmployeeOne;

GO

IF OBJECT_ID ('dbo.EmployeeTwo', 'U') IS NOT NULL

DROP TABLE dbo.EmployeeTwo;

GO

IF OBJECT_ID ('dbo.EmployeeThree', 'U') IS NOT NULL

DROP TABLE dbo.EmployeeThree;

GO

SELECT pp.LastName, pp.FirstName, e.JobTitle

INTO dbo.EmployeeOne

FROM Person.Person AS pp JOIN HumanResources.Employee AS e

ON e.BusinessEntityID = pp.BusinessEntityID

WHERE LastName = 'Johnson';

GO

SELECT pp.LastName, pp.FirstName, e.JobTitle

INTO dbo.EmployeeTwo

FROM Person.Person AS pp JOIN HumanResources.Employee AS e

ON e.BusinessEntityID = pp.BusinessEntityID

WHERE LastName = 'Johnson';

GO

SELECT pp.LastName, pp.FirstName, e.JobTitle

INTO dbo.EmployeeThree

FROM Person.Person AS pp JOIN HumanResources.Employee AS e

ON e.BusinessEntityID = pp.BusinessEntityID

WHERE LastName = 'Johnson';

GO

-- Union ALL

SELECT LastName, FirstName, JobTitle

FROM dbo.EmployeeOne

UNION ALL

SELECT LastName, FirstName ,JobTitle

FROM dbo.EmployeeTwo

UNION ALL

SELECT LastName, FirstName,JobTitle

FROM dbo.EmployeeThree;

GO

SELECT LastName, FirstName,JobTitle

FROM dbo.EmployeeOne

UNION

SELECT LastName, FirstName, JobTitle

FROM dbo.EmployeeTwo

UNION

SELECT LastName, FirstName, JobTitle

FROM dbo.EmployeeThree;

GO

SELECT LastName, FirstName,JobTitle

FROM dbo.EmployeeOne

UNION ALL

(

SELECT LastName, FirstName, JobTitle

FROM dbo.EmployeeTwo

UNION

SELECT LastName, FirstName, JobTitle

FROM dbo.EmployeeThree

);

Transact-SQL

Page 18: Microsoft SELECT Transact SQL 2014

FOR ﴾cláusula de Transact‐SQL﴿

La cláusula FOR se utiliza para especificar la opción BROWSE o XML. BROWSE y XML son opciones no relacionadas.

Importante

La directiva XMLDATA para la opción FOR XML ha quedado desusada. Utilice la XSD generación en los modos RAW y AUTO. No hay sustitución para la directivaXMLDATA en modo EXPLICIT. Esta característica se quitará en una versión futura de Microsoft SQL Server. Evite utilizar esta característica en nuevos trabajos dedesarrollo y tenga previsto modificar las aplicaciones que actualmente la utilizan.

Se aplica a: SQL Server (SQL Server 2008 a versión actual﴿, Windows Azure SQL Database ﴾Versión inicial a versión actual).

Convenciones de sintaxis de Transact-SQL

Sintaxis

Argumentos

BROWSE

Especifica que se permiten las actualizaciones mientras se visualizan los datos en el cursor del modo de exploración de DB‐Library. Una tabla se puede explorar

en una aplicación si la tabla incluye una columna timestamp, la tabla tiene un índice único y la opción FOR BROWSE está al final de las instrucciones SELECTenviadas a una instancia de SQL Server.

Nota

No se puede utilizar <lock_hint> HOLDLOCK en una instrucción SELECT que incluya la opción FOR BROWSE.

FOR BROWSE no puede aparecer en instrucciones SELECT combinadas mediante el operador UNION.

Nota

Cuando las columnas de clave de índice único de una tabla pueden aceptar valores NULL, y la tabla está en la parte interna de la combinación externa, elíndice no se admite en el modo de exploración.

El modo de exploración permite examinar las filas de la tabla de SQL Server y actualizar los datos de la tabla fila por fila. Para tener acceso a una tabla de SQL

Server en una aplicación en el modo de exploración, debe utilizar una de las dos opciones siguientes:

La instrucción SELECT que utiliza para tener acceso a los datos de la tabla de SQL Server debe finalizar con las palabras clave FOR BROWSE. Al activar la

opción FOR BROWSE para utilizar el modo de exploración, se crean tablas temporales.

Debe ejecutar la instrucción de Transact‐SQL siguiente para activar el modo de exploración utilizando la opción NO_BROWSETABLE:

SQL Server 2014

[ FOR { BROWSE | <XML> } ]

<XML> ::=

XML

{

{ RAW [ ( 'ElementName' ) ] | AUTO }

[

<CommonDirectives>

[ , { XMLDATA | XMLSCHEMA [ ( 'TargetNameSpaceURI' ) ] } ]

[ , ELEMENTS [ XSINIL | ABSENT ]

]

| EXPLICIT

[

<CommonDirectives>

[ , XMLDATA ]

]

| PATH [ ( 'ElementName' ) ]

[

<CommonDirectives>

[ , ELEMENTS [ XSINIL | ABSENT ] ]

]

}

<CommonDirectives> ::=

[ , BINARY BASE64 ]

[ , TYPE ]

[ , ROOT [ ( 'RootName' ) ] ]

SET NO_BROWSETABLE ON

Page 19: Microsoft SELECT Transact SQL 2014

Al activar la opción NO_BROWSETABLE, todas las instrucciones SELECT se comportan como si la opción FOR BROWSE se anexara a las instrucciones. Sin

embargo, la opción NO_BROWSETABLE no crea las tablas temporales que la opción FOR BROWSE utiliza generalmente para enviar los resultados a la

aplicación.

Cuando se intenta tener acceso a los datos de las tablas de SQL Server en modo de exploración utilizando una consulta SELECT que implica una instrucción decombinación externa, y cuando se define un índice único en la tabla que está presente en el lado interno de una instrucción de combinación externa, el modo deexploración no admite el índice único. El modo de exploración únicamente admite el índice único cuando todas las columnas de clave de índice único puedenaceptar valores NULL. El modo de exploración no admite el índice único si se cumplen las condiciones siguientes:

Intenta tener acceso a los datos de las tablas de SQL Server en modo de exploración utilizando una consulta SELECT que implica una instrucción decombinación externa.

Un índice único se define en la tabla que está presente en el lado interno de una instrucción de combinación externa.

Para reproducir este comportamiento en el modo de exploración, siga estos pasos:

1. En SQL Server Management Studio, cree una base de datos denominada SampleDB.

2. En la base de datos SampleDB, cree una tabla tleft y una tabla tright que contengan ambas una única columna que se denomine c1. Defina un índice únicoen la columna c1 de la tabla tleft y establezca la columna para aceptar valores NULL. Para ello, ejecute las instrucciones de Transact-SQL siguientes en una

ventana de consulta adecuada:

3. Inserte varios valores en las tablas tleft y tright. Asegúrese de insertar un valor NULL en la tabla tleft. Para ello, ejecute las instrucciones de Transact-SQL

siguientes en la ventana de consulta:

4. Active la opción NO_BROWSETABLE. Para ello, ejecute las instrucciones de Transact-SQL siguientes en la ventana de consulta:

5. Obtenga acceso a los datos de las tablas tleft y tright utilizando una instrucción de combinación externa en la consulta SELECT. Asegúrese de que la tablatleft está en el lado interno de la instrucción de combinación externa. Para ello, ejecute las instrucciones de Transact-SQL siguientes en la ventana de

consulta:

Observe la salida siguiente en el panel Resultados:

c1

----

NULL

NULL

Después de ejecutar la consulta SELECT para obtener acceso a las tablas en el modo de exploración, el conjunto de resultados de la consulta SELCT contienedos valores NULL para la columna c1 de la tabla tleft debido a la definición de la instrucción de combinación externa derecha. Por consiguiente, en el conjunto

de resultados no puede distinguir entre los valores NULL que procedían de la tabla y los incluidos por la instrucción de combinación externa derecha. Puede

recibir resultados incorrectos si debe omitir los valores NULL del conjunto de resultados.

Nota

Si las columnas que están incluidas en el índice único no aceptan valores NULL, todos los valores NULL en el conjunto de resultados fueron incluidos por lainstrucción de combinación externa.

CREATE TABLE tleft(c1 INT NULL UNIQUE) ;

GO

CREATE TABLE tright(c1 INT NULL) ;

GO

INSERT INTO tleft VALUES(2) ;

INSERT INTO tleft VALUES(NULL) ;

INSERT INTO tright VALUES(1) ;

INSERT INTO tright VALUES(3) ;

INSERT INTO tright VALUES(NULL) ;

GO

SET NO_BROWSETABLE ON ;

GO

SELECT tleft.c1

FROM tleft

RIGHT JOIN tright

ON tleft.c1 = tright.c1

WHERE tright.c1 <> 2 ;

Page 20: Microsoft SELECT Transact SQL 2014

XML

Especifica que el resultado de una consulta se devolverá como documento XML. Debe especificarse uno de los siguientes modos XML: RAW, AUTO, EXPLICIT.

Para obtener más información acerca de datos XML y SQL Server, vea FOR XML (SQL Server).

RAW [ ('ElementName') ]

Obtiene el resultado de la consulta y transforma cada fila del conjunto de resultados en un elemento XML con un identificador genérico <row /> como etiquetadel elemento. Opcionalmente, puede especificar un nombre para el elemento de fila. La salida XML resultante utiliza el parámetro ElementName especificado

como el elemento de fila generado para cada fila. Para obtener más información, vea Usar el modo RAW con FOR XML y Usar el modo RAW con FOR XML.

AUTO

Devuelve los resultados de la consulta en un árbol anidado XML sencillo. Cada tabla de la cláusula FROM, para la que al menos se presenta una columna en lacláusula SELECT, se representa como elemento XML. A las columnas presentadas en la cláusula SELECT se les asignan los atributos de elemento apropiados.Para obtener más información, vea Usar el modo AUTO con FOR XML.

EXPLICIT

Especifica que la forma del árbol XML resultante está definida explícitamente. Con este modo, es necesario escribir las consultas de una cierta manera, de modo

que se pueda especificar explícitamente información adicional acerca de la anidación deseada. Para obtener más información, vea Usar el modo EXPLICIT con

FOR XML.

XMLDATA

Devuelve el esquema XDR insertado, pero no agrega el elemento raíz al resultado. Si se especifica XMLDATA, el esquema XDR se agrega al documento.

XMLSCHEMA [ ('TargetNameSpaceURI') ]

Devuelve el esquema XSD insertado. Opcionalmente puede especificar un URI de espacio de nombres de destino al especificar esta directiva, que devuelve el

espacio de nombres especificado en el esquema. Para obtener más información, vea Generar un esquema XSD insertado.

ELEMENTS

Especifica que las columnas se devuelven como subelementos. Sin embargo, se les asignan atributos XML. Esta opción solo se admite en los modos RAW, AUTOy PATH. Para obtener más información, vea Usar el modo RAW con FOR XML.

XSINIL

Especifica que se va a crear un elemento con el atributo xsi:nil establecido en True para los valores de columna NULL. Esta opción solo se puede especificarcon la directiva ELEMENTS. Para obtener más información, vea Generar elementos para valores NULL mediante el parámetro XSINIL.

ABSENT

Indica que para los valores de columna NULL, no se agregarán los elementos XML correspondientes en el resultado XML. Especifique esta opción solo conELEMENTS.

PATH [ ('ElementName') ]

Genera un contenedor del elemento de <row> para cada fila en el conjunto de resultados. Opcionalmente, especifique un nombre de elemento para el

contenedor del elemento <row>. Si se proporciona una cadena vacía, como FOR XML PATH ﴾'') , no se genera un elemento contenedor. El uso de PATH puede

proporcionar una alternativa más sencilla a consultas escritas con la directiva EXPLICIT. Para obtener más información, vea Usar el modo PATH con FOR XML.

BINARY BASE64

Especifica que la consulta devuelve los datos binarios en el formato codificado BINARY BASE64. Al recuperar datos binarios mediante el modo RAW y EXPLICIT,

se debe especificar esta opción. Éste es el valor predeterminado en el modo AUTO.

TYPE

Especifica que la consulta devuelve un resultado de tipo xml. Para obtener más información, vea Directiva TYPE en consultas FOR XML.

ROOT [ ('RootName') ]

Especifica que se va a agregar un solo elemento de nivel superior al XML resultante. Opcionalmente puede especificar el nombre del elemento raíz que se va agenerar. Si no se especifica el nombre de raíz opcional, se agrega el elemento <root> predeterminado.

Ejemplos

En el siguiente ejemplo se especifica FOR XML AUTO con las opciones TYPE y XMLSCHEMA. Debido a la opción TYPE, el conjunto de resultados que se devuelve al cliente

es de tipo xml. La opción XMLSCHEMA especifica que el esquema XSD insertado está incluido en los datos XML devueltos y la opción ELEMENTS especifica que el

resultado XML está centrado en elementos.

Vea también

ReferenciaSELECT (Transact-SQL)

FOR XML (SQL Server)

© 2014 Microsoft

USE AdventureWorks2012;

GO

SELECT p.BusinessEntityID, FirstName, LastName, PhoneNumber AS Phone

FROM Person.Person AS p

JOIN Person.PersonPhone AS pph ON p.BusinessEntityID = pph.BusinessEntityID

WHERE LastName LIKE 'G%'

ORDER BY LastName, FirstName

FOR XML AUTO, TYPE, XMLSCHEMA, ELEMENTS XSINIL;

Page 21: Microsoft SELECT Transact SQL 2014

GROUP BY (Transact-SQL)

Agrupa un conjunto de filas seleccionado en un conjunto de filas de resumen de acuerdo con los valores de una o más columnas o expresiones en SQL Server 2014. Se

devuelve una fila para cada grupo. Las funciones de agregado de la lista <select> de la cláusula SELECT proporcionan información de cada grupo en lugar de filasindividuales.

La cláusula GROUP BY tiene una sintaxis que cumple la norma ISO y otra sintaxis que no cumple dicha norma. Solo se puede usar un estilo de sintaxis en cada instrucciónSELECT. Use la sintaxis que cumple la norma ISO para todos los trabajos nuevos. La sintaxis que no cumple dicha norma solo se incluye por motivos de compatibilidad

con versiones anteriores.

En este tema, una cláusula GROUP BY se puede describir como general o como simple:

Una cláusula GROUP BY general incluye los elementos GROUPING SETS, CUBE, ROLLUP, WITH CUBE o WITH ROLLUP.

Una cláusula GROUP BY simple no incluye GROUPING SETS, CUBE, ROLLUP, WITH CUBE ni WITH ROLLUP. GROUP BY ﴾﴿, total general, se considera una cláusulaGROUP BY simple.

Se aplica a: SQL Server (desde SQL Server 2008 hasta la versión actual﴿, Windows Azure SQL Database ﴾desde la versión inicial hasta la versión actual).

Convenciones de sintaxis de Transact-SQL (Transact-SQL)

Sintaxis

SQL Server 2014

ISO-Compliant Syntax

GROUP BY <group by spec>

<group by spec> ::=

<group by item> [ ,...n ]

<group by item> ::=

<simple group by item>

| <rollup spec>

| <cube spec>

| <grouping sets spec>

| <grand total>

<simple group by item> ::=

<column_expression>

<rollup spec> ::=

ROLLUP ( <composite element list> )

<cube spec> ::=

CUBE ( <composite element list> )

<composite element list> ::=

<composite element> [ ,...n ]

<composite element> ::=

<simple group by item>

| ( <simple group by item list> )

<simple group by item list> ::=

<simple group by item> [ ,...n ]

<grouping sets spec> ::=

GROUPING SETS ( <grouping set list> )

<grouping set list> ::=

<grouping set> [ ,...n ]

<grouping set> ::=

<grand total>

| <grouping set item>

| ( <grouping set item list> )

<empty group> ::=

( )

<grouping set item> ::=

<simple group by item>

| <rollup spec>

| <cube spec>

<grouping set item list> ::=

<grouping set item> [ ,...n ]

Page 22: Microsoft SELECT Transact SQL 2014

Argumentos

< column_expression>

Es la expresión en la que se realiza la operación de agrupamiento.

ROLLUP ( )

Genera filas de agregado mediante la cláusula GROUP BY simple, más filas de subtotal o de superagregado, así como una fila de total general.

El número de agrupaciones que se devuelve es igual al número de expresiones de la <lista de elementos compuestos> más uno. Considere, por ejemplo, la

siguiente instrucción:

Se genera una fila con un subtotal para cada combinación única de valores de (a, b, c), (a, b) y (a). También se calcula una fila de total general.

Las columnas se acumulan de derecha a izquierda. El orden de las columnas influye en las agrupaciones de salida de ROLLUP y también puede afectar al númerode filas del conjunto de resultados.

CUBE ( )

Genera filas de agregado mediante la cláusula GROUP BY simple, filas de superagregado mediante la instrucción ROLLUP y filas de tabulación cruzada.

CUBE genera una agrupación para todas las permutaciones de expresiones de la <lista de elementos compuestos>.

El número de agrupaciones generado es igual a ﴾2n﴿, donde n es el número de expresiones de la <lista de elementos compuestos>. Considere, por ejemplo, la

siguiente instrucción:

Se genera una fila para cada combinación única de valores de (a, b, c), (a, b), (a, c), (b, c), (a), (b) y (c), con un subtotal para cada fila y una fila de

total general.

El orden de las columnas no afecta a la salida de CUBE.

GROUPING SETS ( )

Especifica varias agrupaciones de datos en una consulta. Solo se agregan los grupos especificados, en lugar del conjunto completo de agregaciones generado

por CUBE o ROLLUP. Los resultados son equivalentes a usar la instrucción UNION ALL en los grupos especificados. GROUPING SETS puede contener un únicoelemento o una lista de elementos. GROUPING SETS puede especificar agrupaciones equivalentes a las devueltas por ROLLUP o CUBE. La <lista de elementos de

conjunto de agrupación> puede contener ROLLUP o CUBE.

( )

El grupo vacío genera un total.

Sintaxis que no cumple la norma ISO

ALL

Esta característica se quitará en una versión futura de Microsoft SQL Server. Evite utilizar esta característica en nuevos trabajos de desarrollo y tenga previstomodificar las aplicaciones que actualmente la utilizan.Incluye todos los grupos y conjuntos de resultados, incluso aquellos en los que no hay filas que cumplan la

condición de búsqueda especificada en la cláusula WHERE. Cuando se especifica ALL, se devuelven valores NULL para las columnas de resumen de los grupos

que no cumplen la condición de búsqueda. No puede especificar ALL con los operadores CUBE y ROLLUP.

GROUP BY ALL no se admite en consultas que tienen acceso a tablas remotas si también hay una cláusula WHERE en la consulta. GROUP BY ALL generará erroren las columnas que tengan el atributo FILESTREAM.

group_by_expression

Es una expresión según la cual se realiza la agrupación. group_by_expression también se conoce como columna de agrupación. group_by expression puede ser una

columna o una expresión no agregada que haga referencia a una columna devuelta por la cláusula FROM. Un alias de columna que esté definido en la listaSELECT no puede usarse para especificar una columna de agrupamiento.

Nota

Las columnas de tipo text, ntext e image no se pueden utilizar en group_by_expression.

En las cláusulas GROUP BY que no contengan CUBE o ROLLUP, el número de elementos group_by_expression está limitado por los tamaños de columna de GROUPBY, las columnas de agregado y los valores de agregado que participan en la consulta. Este límite procede del límite de 8.060 bytes de la tabla de trabajointermedia que se necesita para contener los resultados intermedios de la consulta. Se permite un máximo de 12 expresiones de agrupamiento cuando se

Non-ISO-Compliant Syntax

[ GROUP BY [ ALL ] group_by_expression [ ,...n ]

[ WITH { CUBE | ROLLUP } ]

]

SELECT a, b, c, SUM ( <expression> )

FROM T

GROUP BY ROLLUP (a,b,c);

SELECT a, b, c, SUM (<expression>)

FROM T

GROUP BY CUBE (a,b,c);

Page 23: Microsoft SELECT Transact SQL 2014

especifica CUBE o ROLLUP.

No se pueden especificar métodos de tipos de datos xml directamente en group_by_expression. En su lugar, haga referencia a una función definida por el usuarioque incluya métodos de tipos de datos xml, o haga referencia a una columna calculada que los utilice.

WITH CUBE

Esta característica se quitará en una versión futura de Microsoft SQL Server. Evite utilizar esta característica en nuevos trabajos de desarrollo y tenga previstomodificar las aplicaciones que actualmente la utilizan. Especifica que, además de las filas que normalmente proporciona GROUP BY, deben incluirse filas deresumen en el conjunto de resultados. Se devuelve una fila de resumen GROUP BY por cada posible combinación de grupo y subgrupo del conjunto deresultados. Utilice la función GROUPING para determinar si los valores NULL del conjunto de resultados son valores de resumen GROUP BY.

El número de filas de resumen del conjunto de resultados se determina mediante el número de columnas que contiene la cláusula GROUP BY. Debido a que

CUBE devuelve todas las combinaciones posibles de grupo y subgrupo, el número de filas es el mismo, independientemente del orden en que se especifiquenlas columnas de agrupamiento.

WITH ROLLUP

Esta característica se quitará en una versión futura de Microsoft SQL Server. Evite utilizar esta característica en nuevos trabajos de desarrollo y tenga previstomodificar las aplicaciones que actualmente la utilizan. Especifica que, además de las filas que normalmente proporciona GROUP BY, deben incluirse filas deresumen en el conjunto de resultados. Los grupos se resumen en un orden jerárquico, desde el nivel inferior del grupo hasta el superior. La jerarquía del grupose determina por el orden en que se especifican las columnas de agrupamiento. Cambiar el orden de las columnas de agrupamiento puede afectar al númerode filas generadas en el conjunto de resultados.

Importante

Cuando se utiliza CUBE o ROLLUP no se admiten agregados Distinct, como AVG (DISTINCT column_name), COUNT (DISTINCT column_name) y SUM (DISTINCT

column_name). Si se utilizan, Motor de base de datos de SQL Server devuelve un mensaje de error y cancela la consulta.

Comentarios

Las expresiones de la cláusula GROUP BY pueden contener columnas de las tablas, de las tablas derivadas o de las vistas de la cláusula FROM. No es necesario que

aparezcan las columnas en la lista de <selección> de la cláusula SELECT.

Deben incluirse en la lista GROUP BY todas las columnas de la tabla o la vista de cualquier expresión no agregada de la lista de <selección>:

Están permitidas las siguientes instrucciones:

No están permitidas las siguientes instrucciones:

Si se incluyen funciones de agregado en la <lista de selección> de la cláusula SELECT, GROUP BY calcula un valor de resumen para cada grupo. Se conocen como

agregados vectoriales.

Las filas que no cumplen las condiciones especificadas en la cláusula WHERE se quitan antes de realizar ninguna operación de agrupación.

La cláusula HAVING se usa junto con la cláusula GROUP BY para filtrar los grupos en el conjunto de resultados.

La cláusula GROUP BY no ordena el conjunto de resultados. En su lugar, use la cláusula ORDER BY para ordenarlo.

Si una columna de agrupamiento contiene varios valores NULL, todos ellos se consideran equivalentes y se colocan en un grupo individual.

No es posible usar GROUP BY con un alias para reemplazar el nombre de una columna en la cláusula AS, a menos que dicho alias sustituya a un nombre de columna enuna tabla derivada de la cláusula FROM.

Los conjuntos de agrupación duplicados de una lista GROUPING SETS no se eliminan. Los conjuntos de agrupación duplicados se pueden generar especificando unaexpresión de columna más de una vez o incluyendo una expresión de columna también generada por una instrucción CUBE o ROLLUP en la lista GROUPING SETS.

Cuando se utiliza ROLLUP, CUBE y GROUPING SETS se admiten agregados Distinct, como AVG (DISTINCT column_name), COUNT (DISTINCT column_name) y SUM

(DISTINCT column_name).

ROLLUP, CUBE y GROUPING SETS no se pueden especificar en una vista indexada.

GROUP BY o HAVING no se pueden usar directamente en columnas de ntext, text o image. Estas columnas se pueden usar como argumentos en funciones que

devuelven un valor de otro tipo de datos, como SUBSTRING() y CAST().

No se pueden especificar métodos de tipos de datos xml directamente en <column_expression>. En su lugar, haga referencia a una función definida por el usuario queincluya métodos de tipos de datos xml o haga referencia a una columna calculada que los utilice.

Limitaciones de GROUP BY para GROUPING SETS, ROLLUP y CUBE

Limitaciones de la sintaxis

Los operadores GROUPING SETS no se pueden usar en la cláusula GROUP BY a menos que formen parte de una lista GROUPING SETS. Por ejemplo, no se admite

SELECT ColumnA, ColumnB FROM T GROUP BY ColumnA, ColumnB;

SELECT ColumnA + ColumnB FROM T GROUP BY ColumnA, ColumnB;

SELECT ColumnA + ColumnB FROM T GROUP BY ColumnA + ColumnB;

SELECT ColumnA + ColumnB + constant FROM T GROUP BY ColumnA, ColumnB;

SELECT ColumnA, ColumnB FROM T GROUP BY ColumnA + ColumnB;

SELECT ColumnA + constant + ColumnB FROM T GROUP BY ColumnA + ColumnB;

Page 24: Microsoft SELECT Transact SQL 2014

GROUP BY C1, (C2,..., Cn) pero sí GROUP BY GROUPING SETS (C1, (C2, ..., Cn)).

Los operadores GROUPING SETS no se pueden usar dentro de GROUPING SETS. Por ejemplo, no se admite GROUP BY GROUPING SETS (C1, GROUPING SETS (C2,

C3)).

Las palabras clave que no cumplen la norma ISO ALL, WITH CUBE y WITH ROLLUP no se pueden usar en una cláusula GROUP BY con las palabras clave ROLLUP, CUBE oGROUPING SETS.

Limitaciones de tamañoEn la cláusula simple GROUP BY, no hay ningún límite en cuanto al número de expresiones.

En una cláusula GROUP BY que utilice ROLLUP, CUBE o GROUPING SETS, el número máximo de expresiones es 32, y el número máximo de conjuntos de agrupación que

se pueden generar es 4.096 (212). Los ejemplos siguientes producen un error debido a que la cláusula GROUP BY es demasiado compleja:

En los ejemplos siguientes se generan 8.192 (213) conjuntos de agrupamiento.

En el ejemplo siguiente se generan 4.097 (212 + 1) conjuntos de agrupamiento.

En el ejemplo siguiente también se generan 4.097 ﴾212 + 1) conjuntos de agrupamiento. Los conjuntos de agrupación CUBE () y () generan una fila de total

general y los conjuntos de agrupación duplicados no se eliminan.

Compatibilidad con las características GROUP BY de ISO y ANSI SQL‐2006En SQL Server 2014, la cláusula GROUP BY no puede contener una subconsulta en una expresión que se use para la lista de agrupación. Se devuelve el error 144.

SQL Server 2014 admite todas las características GROUP BY incluidas en el estándar SQL‐2006 con las excepciones de sintaxis siguientes:

Los conjuntos de agrupamiento no se pueden usar en la cláusula GROUP BY a menos que formen parte de una lista GROUPING SETS explícita. Por ejemplo,

GROUP BY Column1, (Column2, ...ColumnN﴿ se admite en el estándar pero no en SQL Server. Se permite GROUP BY C1, GROUPING SETS ((Column2,

...ColumnN)) o GROUP BY Column1, Column2, ... ColumnN. Éstos son equivalentes semánticamente al ejemplo de GROUP BY anterior. Con ello se evita la

posibilidad de que GROUP BY Column1, (Column2, ...ColumnN) se pueda malinterpretar como GROUP BY C1, GROUPING SETS ((Column2, ...ColumnN)).

Éste no es equivalente semánticamente.

No se pueden usar conjuntos de agrupamiento dentro de conjuntos de agrupamiento. Por ejemplo, GROUP BY GROUPING SETS (A1, A2,…An, GROUPING SETS(C1, C2, ...Cn)) se admite en el estándar SQL‐2006 pero no en SQL Server. SQL Server 2014 permite GROUP BY GROUPING SETS( A1, A2,...An, C1, C2,

...Cn ) o GROUP BY GROUPING SETS( (A1), (A2), ... (An), (C1), (C2), ... (Cn) ). Estos ejemplos son equivalentes semánticamente al primer ejemplode GROUP BY y tienen una sintaxis más clara.

No se admite GROUP BY [ALL/DISTINCT] en una cláusula GROUP BY general ni con las construcciones GROUPING SETS, ROLLUP, CUBE, WITH CUBE o WITHROLLUP. ALL es el valor predeterminado y es implícito.

Comparación de las características GROUP BY compatiblesEn la tabla siguiente se describen las características de GROUP BY que son compatibles dependiendo de la versión de SQL Server y del nivel de compatibilidad de labase de datos.

Característica SQL Server Integration ServicesNivel de compatibilidad 100 o superior

con SQL Server

SQL Server 2008 o posterior con el nivel

de compatibilidad 90.

Agregados DISTINCT No se admite en WITH CUBE ni en WITH

ROLLUP.

Se admite en WITH CUBE, WITH ROLLUP,

GROUPING SETS, CUBE o ROLLUP.

Igual que el nivel de compatibilidad 100.

Función definida por elusuario con un nombre

CUBE o ROLLUP en la

cláusula GROUP BY

Se admite la función definida por el usuariodbo.cube(arg1,...argN) o

dbo.rollup(arg1,...argN) en la cláusulaGROUP BY.

Por ejemplo:

No se admite la función definida por elusuario dbo.cube (arg1,...argN) o

dbo.rollup(arg1,...argN) en la cláusulaGROUP BY.

Por ejemplo:

Se devuelve el mensaje de error siguiente:

Se admite la función definida por el usuariodbo.cube (arg1,...argN) o

dbo.rollup(arg1,...argN) en la cláusulaGROUP BY

Por ejemplo:

GROUP BY CUBE (a1, ..., a13)

GROUP BY a1, ..., a13 WITH CUBE

GROUP BY GROUPING SETS( CUBE(a1, ..., a12), b )

GROUP BY GROUPING SETS( CUBE(a1, ..., a12), ())

SELECT SUM (x)

FROM T

GROUP BY dbo.cube(y);

SELECT SUM (x)

FROM T

GROUP BY dbo.cube(y);

SELECT SUM (x)

FROM T

GROUP BY dbo.cube(y);

Page 25: Microsoft SELECT Transact SQL 2014

"Sintaxis incorrecta cerca de la palabra clave

'cube'|'rollup'."

Para evitar este problema, reemplace

dbo.cube por [dbo].[cube] o dbo.rollup

por [dbo].[rollup].

Se admite el siguiente ejemplo:

GROUPING SETS No compatible Compatible Compatible

CUBE No compatible Compatible No compatible

ROLLUP No compatible Compatible No compatible

Total general, como

GROUP BY ()

No compatible Compatible Compatible

Función GROUPING_ID No compatible Compatible Compatible

Función GROUPING Compatible Compatible Compatible

WITH CUBE Compatible Compatible Compatible

WITH ROLLUP Compatible Compatible Compatible

Eliminación de gruposduplicados de WITH

CUBE o WITH ROLLUP

Compatible Compatible Compatible

Ejemplos

A.Utilizar una cláusula GROUP BY simpleEn el ejemplo siguiente se recupera el total de cada SalesOrderID de la tabla SalesOrderDetail de la base de datos AdventureWorks2012.

B.Utilizar una cláusula GROUP BY con varias tablasEn el ejemplo siguiente se recupera el número de empleados de cada City de la tabla Address combinada con la tabla EmployeeAddress de la base de datos

AdventureWorks2012.

C.Utilizar una cláusula GROUP BY con una expresiónEn el ejemplo siguiente se recuperan las ventas totales de cada año con la función DATEPART. Debe incluirse la misma expresión en la lista SELECT y en la cláusula GROUPBY.

D.Utilizar una cláusula GROUP BY con una cláusula HAVINGEn el ejemplo siguiente se usa la cláusula HAVING para especificar cuáles de los grupos generados en la cláusula GROUP BY deben incluirse en el conjunto de resultados.

SELECT SUM (x)

FROM T

GROUP BY [dbo].[cube](y);

SELECT SalesOrderID, SUM(LineTotal) AS SubTotal

FROM Sales.SalesOrderDetail AS sod

GROUP BY SalesOrderID

ORDER BY SalesOrderID;

SELECT a.City, COUNT(bea.AddressID) EmployeeCount

FROM Person.BusinessEntityAddress AS bea

INNER JOIN Person.Address AS a

ON bea.AddressID = a.AddressID

GROUP BY a.City

ORDER BY a.City;

SELECT DATEPART(yyyy,OrderDate) AS N'Year'

,SUM(TotalDue) AS N'Total Order Amount'

FROM Sales.SalesOrderHeader

GROUP BY DATEPART(yyyy,OrderDate)

ORDER BY DATEPART(yyyy,OrderDate);

Page 26: Microsoft SELECT Transact SQL 2014

Vea también

ReferenciaGROUPING_ID (Transact-SQL)

GROUPING (Transact-SQL)

SELECT (Transact-SQL)

SELECT ﴾cláusula de Transact‐SQL﴿

© 2014 Microsoft

SELECT DATEPART(yyyy,OrderDate) AS N'Year'

,SUM(TotalDue) AS N'Total Order Amount'

FROM Sales.SalesOrderHeader

GROUP BY DATEPART(yyyy,OrderDate)

HAVING DATEPART(yyyy,OrderDate) >= N'2003'

ORDER BY DATEPART(yyyy,OrderDate);

Page 27: Microsoft SELECT Transact SQL 2014

HAVING (Transact-SQL)

Especifica una condición de búsqueda para un grupo o agregado. HAVING solo se puede utilizar con la instrucción SELECT. Normalmente, HAVING se utiliza en una

cláusula GROUP BY. Cuando no se utiliza GROUP BY, HAVING se comporta como una cláusula WHERE.

Se aplica a: SQL Server (SQL Server 2008 a versión actual﴿, Windows Azure SQL Database ﴾Versión inicial a versión actual).

Convenciones de sintaxis de Transact-SQL

Sintaxis

Argumentos

<search_condition>

Especifica la condición de búsqueda del grupo o del agregado que se debe cumplir.

No se pueden utilizar los tipos de datos text, image y ntext en una cláusula HAVING.

Ejemplos

En el ejemplo siguiente, donde se utiliza una cláusula HAVING simple, se recupera el total de cada SalesOrderID de la tabla SalesOrderDetail que exceda

$100000.00.

Vea también

ReferenciaGROUP BY (Transact-SQL)

WHERE (Transact-SQL)

© 2014 Microsoft

SQL Server 2014

[ HAVING <search condition> ]

USE AdventureWorks2012 ;

GO

SELECT SalesOrderID, SUM(LineTotal) AS SubTotal

FROM Sales.SalesOrderDetail

GROUP BY SalesOrderID

HAVING SUM(LineTotal) > 100000.00

ORDER BY SalesOrderID ;

Page 28: Microsoft SELECT Transact SQL 2014

INTO ﴾cláusula de Transact‐SQL﴿

SELECT...INTO crea una nueva tabla en el grupo de archivos predeterminado e inserta las filas resultantes de la consulta en ella. Para ver la sintaxis completa de SELECT,

vea SELECT (Transact-SQL).

Se aplica a: SQL Server (SQL Server 2008 a versión actual﴿, Windows Azure SQL Database ﴾Versión inicial a versión actual).

Convenciones de sintaxis de Transact-SQL

Sintaxis

Argumentos

new_table

Especifica el nombre de una nueva tabla que se va a crear en función de las columnas de la lista de selección y de las filas elegidas desde el origen de datos.

El formato de new_table se determina mediante la evaluación de las expresiones de la lista de selección. Las columnas de new_table se crean en el orden que

especifica la lista de selección. Cada columna de new_table tiene el mismo nombre, tipo de datos, nulabilidad y valor que la expresión correspondiente de la listade selección. La propiedad IDENTITY de una columna se transfiere excepto bajo las condiciones definidas en "Trabajar con columnas de identidad" en la secciónComentarios.

Para crear la tabla en otra base de datos en la misma instancia de SQL Server, especifique new_table como un nombre completo con formato

database.schema.table_name.

No puede crear new_table en un servidor remoto; sin embargo, puede rellenar new_table desde un origen de datos remoto. Para crear new_table a partir de una

tabla de origen remota, especifique la tabla de origen utilizando un nombre con cuatro partes con el formato linked_server.catalog.schema.object en la cláusulaFROM de la instrucción SELECT. También puede utilizar la función OPENQUERY o la función OPENDATASOURCE en la cláusula FROM para especificar el origen dedatos remoto.

Tipos de datos

El atributo FILESTREAM no transfiere a la nueva tabla. Los BLOB FILESTREAM se copian y se almacenan en la nueva tabla como BLOB varbinary(max). Sin el atributo

FILESTREAM, el tipo de datos varbinary(max) tiene una limitación de 2 GB. Si un FILESTREAM BLOB supera este valor, se produce el error 7119 y se detiene la

instrucción.

Cuando se selecciona una columna de identidad existente en una nueva tabla, la nueva columna hereda la propiedad IDENTITY, a menos que se cumpla una de las

siguientes condiciones:

La instrucción SELECT contiene una combinación.

Se han combinado varias instrucciones SELECT con UNION.

La columna de identidad aparece más de una vez en la lista de selección.

La columna de identidad forma parte de una expresión.

La columna de identidad es de un origen de datos remoto.

Si se cumple alguna de estas condiciones, la columna se crea como NOT NULL en lugar de heredar la propiedad IDENTITY. Si una columna de identidad se requiere en

la nueva tabla pero este tipo de columna no está disponible o desea un valor de inicialización o de incremento diferente de la columna de identidad de origen, definala columna en la lista de selección utilizando la función IDENTITY. Vea "Crear una columna de identidad utilizando la función IDENTITY" en la sección Ejemplos siguiente.

Limitaciones y restricciones

No puede especificar una variable de tabla o parámetro con valores de tabla como la nueva tabla.

No puede utilizar SELECT…INTO para crear una tabla con particiones, incluso si la partición se realiza sobre la tabla de origen. SELECT...INTO no usa el esquema de

partición de la tabla de origen; en su lugar, la nueva tabla se crea en el grupo de archivos predeterminado. Para insertar filas en una tabla con particiones, primero

debe crearse la tabla con particiones y, a continuación, utilizar la instrucción INSERT INTO...SELECT FROM.

Los índices, restricciones y desencadenadores definidos en la tabla de origen no se transfieren a la nueva tabla, ni se pueden especificar en la instrucción SELECT...INTO.Si se requieren estos objetos, puede crearlos después de ejecutar la instrucción SELECT...INTO.

Especificar una cláusula ORDER BY no garantiza que las filas se inserten en el orden especificado.

Cuando se incluye una columna dispersa en la lista de selección, la propiedad de la columna dispersa no se transfiere a la columna de la nueva tabla. Si esta propiedad

SQL Server 2014

[ INTO new_table ]

Page 29: Microsoft SELECT Transact SQL 2014

es necesaria en la nueva tabla, modifique la definición de columna después de ejecutar la instrucción SELECT...INTO para que incluya esta propiedad.

Cuando se incluye una columna calculada en la lista de selección, la columna correspondiente de la nueva tabla no es una columna calculada. Los valores de la nueva

columna son los que se calcularon en el momento en que se ejecutó SELECT...INTO.

Comportamiento del registro

La cantidad de registro para SELECT...INTO depende del modelo de recuperación en vigor para la base de datos. En el modelo de recuperación simple o en eloptimizado para cargas masivas de registros, las operaciones masivas se registran mínimamente. Con registro mínimo, utilizar la instrucción SELECT… INTO puede sermás eficaz que crear una tabla y rellenarla con una instrucción INSERT. Para obtener más información, vea El registro de transacciones (SQL Server).

Permissions

Requiere el permiso CREATE TABLE en la base de datos de destino.

Ejemplos

A.Crear una tabla especificando columnas de varios orígenesEn el ejemplo siguiente se crea la tabla dbo.EmployeeAddresses en la base de datos AdventureWorks2012 seleccionando siete columnas de varias tablas relacionadas

con empleados y direcciones.

B.Insertar las filas utilizando el registro mínimoEl ejemplo siguiente crea la tabla dbo.NewProducts e inserta filas de la tabla Production.Product. El ejemplo supone que el modelo de recuperación de la base dedatos AdventureWorks2012 está establecido en FULL. Para asegurarse de que se utiliza el registro mínimo, el modelo de recuperación de la base de datosAdventureWorks2012 se establece en BULK_LOGGED antes de que las filas se inserten y se restablece en FULL después de la instrucción SELECT...INTO. De esta manera

se asegura de que la instrucción SELECT...INTO use el espacio mínimo en el registro de transacciones y funcione eficazmente.

C.Crear una columna de identidad utilizando la función IDENTITYEn el ejemplo siguiente se utiliza la función IDENTITY para crear una columna de identidad en la nueva tabla Person.USAddress de la base de datos

AdventureWorks2012. Se requiere esto porque la instrucción SELECT que define la tabla contiene una unión, que hace que la propiedad IDENTITY no transfiera a lanueva tabla. Tenga en cuenta que los valores de inicialización e incremento especificados en la función IDENTITY son diferentes de los de la columna AddressID de la

tabla de origen Person.Address.

SELECT c.FirstName, c.LastName, e.JobTitle, a.AddressLine1, a.City,

sp.Name AS [State/Province], a.PostalCode

INTO dbo.EmployeeAddresses

FROM Person.Person AS c

JOIN HumanResources.Employee AS e

ON e.BusinessEntityID = c.BusinessEntityID

JOIN Person.BusinessEntityAddress AS bea

ON e.BusinessEntityID = bea.BusinessEntityID

JOIN Person.Address AS a

ON bea.AddressID = a.AddressID

JOIN Person.StateProvince as sp

ON sp.StateProvinceID = a.StateProvinceID;

GO

IF OBJECT_ID('dbo.NewProducts', 'U') IS NOT NULL

DROP TABLE dbo.NewProducts;

GO

ALTER DATABASE AdventureWorks2012 SET RECOVERY BULK_LOGGED;

GO

SELECT * INTO dbo.NewProducts

FROM Production.Product

WHERE ListPrice > $25

AND ListPrice < $100;

GO

ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL;

GO

IF OBJECT_ID ('Person.USAddress') IS NOT NULL

DROP TABLE Person.USAddress;

GO

-- Determine the IDENTITY status of the source column AddressID.

SELECT OBJECT_NAME(object_id) AS TableName, name AS column_name, is_identity, seed_value, increment_value

FROM sys.identity_columns

WHERE name = 'AddressID';

Page 30: Microsoft SELECT Transact SQL 2014

D.Crear una tabla especificando las columnas de un origen de datos remoto

El ejemplo siguiente muestra tres métodos para crear una nueva tabla en el servidor local desde un origen de datos remoto. En el ejemplo se comienza creando un

vínculo al origen de datos remoto. El nombre del servidor vinculado, MyLinkServer, se especifica en la cláusula FROM de la primera instrucción SELECT...INTO y en lafunción OPENQUERY de la segunda instrucción SELECT...INTO. La tercera instrucción SELECT...INTO utiliza la función OPENDATASOURCE, que especifica el origen dedatos remoto directamente en lugar de utilizar el nombre del servidor vinculado.

Se aplica desde SQL Server 2008 hasta SQL Server 2014.

Vea también

ReferenciaSELECT (Transact-SQL)

Ejemplos de SELECT (Transact-SQL)

INSERT (Transact-SQL)

IDENTITY ﴾Función﴿ ﴾Transact‐SQL﴿

© 2014 Microsoft

-- Create a new table with columns from the existing table Person.Address. A new IDENTITY

-- column is created by using the IDENTITY function.

SELECT IDENTITY (int, 100, 5) AS AddressID,

a.AddressLine1, a.City, b.Name AS State, a.PostalCode

INTO Person.USAddress

FROM Person.Address AS a

INNER JOIN Person.StateProvince AS b ON a.StateProvinceID = b.StateProvinceID

WHERE b.CountryRegionCode = N'US';

-- Verify the IDENTITY status of the AddressID columns in both tables.

SELECT OBJECT_NAME(object_id) AS TableName, name AS column_name, is_identity, seed_value, increment_value

FROM sys.identity_columns

WHERE name = 'AddressID';

USE master;

GO

-- Create a link to the remote data source.

-- Specify a valid server name for @datasrc as 'server_name' or 'server_name\instance_name'.

EXEC sp_addlinkedserver @server = N'MyLinkServer',

@srvproduct = N' ',

@provider = N'SQLNCLI',

@datasrc = N'server_name',

@catalog = N'AdventureWorks2012';

GO

USE AdventureWorks2012;

GO

-- Specify the remote data source in the FROM clause using a four-part name

-- in the form linked_server.catalog.schema.object.

SELECT DepartmentID, Name, GroupName, ModifiedDate

INTO dbo.Departments

FROM MyLinkServer.AdventureWorks2012.HumanResources.Department

GO

-- Use the OPENQUERY function to access the remote data source.

SELECT DepartmentID, Name, GroupName, ModifiedDate

INTO dbo.DepartmentsUsingOpenQuery

FROM OPENQUERY(MyLinkServer, 'SELECT *

FROM AdventureWorks2012.HumanResources.Department');

GO

-- Use the OPENDATASOURCE function to specify the remote data source.

-- Specify a valid server name for Data Source using the format server_name or server_name\instance_name.

SELECT DepartmentID, Name, GroupName, ModifiedDate

INTO dbo.DepartmentsUsingOpenDataSource

FROM OPENDATASOURCE('SQLNCLI',

'Data Source=server_name;Integrated Security=SSPI')

.AdventureWorks2012.HumanResources.Department;

GO

Page 31: Microsoft SELECT Transact SQL 2014

ORDER BY ﴾cláusula de Transact‐SQL﴿

Ordenar los datos devueltos por una consulta en SQL Server. Use esta cláusula para:

Ordenar el conjunto de resultados de una consulta por la lista de columnas especificada y, opcionalmente, limitar las filas devueltas a un intervalo especificado. El

orden en que se devuelven las filas en un conjunto de resultados no se puede garantizar, a menos que se especifique una cláusula ORDER BY.

Determinar el orden en que se aplican los valores de la función de categoría al conjunto de resultados.

Se aplica a: SQL Server (SQL Server 2008 a versión actual﴿, Windows Azure SQL Database ﴾Versión inicial a versión actual).

Convenciones de sintaxis de Transact-SQL

Sintaxis

Argumentos

order_by_expression

Especifica una columna o expresión según la que se ordenará el conjunto de resultados de la consulta. Una columna de ordenación se puede especificar comoun nombre o un alias de columna, o un entero no negativo que representa la posición de la columna en la lista de selección.

Es posible especificar varias columnas de ordenación. Los nombres de columna tienen que ser únicos. La secuencia de las columnas de ordenación de la cláusulaORDER BY define la organización del conjunto de resultados ordenado. Es decir, el conjunto de resultados se ordena conforme a la primera columna y, a

continuación, esa lista ordenada se ordena según la segunda columna, y así sucesivamente.

Los nombres de columna a los que se hace referencia en la cláusula ORDER BY deben corresponderse con una columna de la lista de selección o con unacolumna definida en la tabla especificada en la cláusula FROM sin ambigüedades.

COLLATE collation_name

Especifica que la operación ORDER BY debe realizarse conforme a la intercalación especificada en collation_name y no conforme a la intercalación de la columnadefinida en la tabla o vista. collation_name puede ser un nombre de intercalación de Windows o un nombre de intercalación de SQL. Para obtener másinformación, vea Compatibilidad con la intercalación y Unicode. COLLATE solo se aplica a las columnas con tipos de datos char, varchar, nchar y nvarchar.

ASC | DESC

Indica que los valores de la columna especificada se deben ordenar en sentido ascendente o descendente. ASC ordena del valor mínimo al valor máximo. DESC

ordena del valor máximo al valor mínimo. ASC es el criterio de ordenación predeterminado. Los valores NULL se tratan como los valores más bajos posibles.

OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }

Especifica el número de filas que se deben omitir antes de comenzar a devolver filas de la expresión de consulta. El valor puede ser una expresión o constanteentera mayor o igual que cero.

Se aplica a: SQL Server 2012 a SQL Server 2014 y Windows Azure SQL Database.

offset_row_count_expression puede ser una variable, un parámetro o una subconsulta escalar constante. Cuando se utiliza una subconsulta, no puede hacer

referencia a ninguna columna definida en el ámbito de la consulta externa. Es decir, no se puede poner en correlación con la consulta externa.

ROW y ROWS son sinónimos y se proporcionan para ofrecer compatibilidad con ANSI.

En los planes de ejecución de consultas, el valor de recuento de filas de desplazamiento se muestra en el atributo Offset del operador de consulta TOP.

FETCH { FIRST | NEXT } { integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY

Especifica el número de filas que se devolverán después de procesar la cláusula OFFSET. El valor puede ser una expresión o constante entera mayor o igual queuno.

Se aplica a: SQL Server 2012 a SQL Server 2014 y Windows Azure SQL Database.

fetch_row_count_expression puede ser una variable, un parámetro o una subconsulta escalar constante. Cuando se utiliza una subconsulta, no puede hacer

referencia a ninguna columna definida en el ámbito de la consulta externa. Es decir, no se puede poner en correlación con la consulta externa.

SQL Server 2014

ORDER BY order_by_expression

[ COLLATE collation_name ]

[ ASC | DESC ]

[ ,...n ]

[ <offset_fetch> ]

<offset_fetch> ::=

{

OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }

[

FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY

]

}

Transact-SQL

Page 32: Microsoft SELECT Transact SQL 2014

FIRST y NEXT son sinónimos y se proporcionan para ofrecer compatibilidad con ANSI.

ROW y ROWS son sinónimos y se proporcionan para ofrecer compatibilidad con ANSI.

En los planes de ejecución de consultas, el valor de recuento de filas de desplazamiento se muestra en el atributo Rows o Top del operador de consulta TOP.

Prácticas recomendadas

Evite especificar enteros en la cláusula ORDER BY como representaciones posicionales de las columnas en la lista de selección. Por ejemplo, aunque una instruccióncomo SELECT ProductID, Name FROM Production.Production ORDER BY 2 es válida, otros usuarios no la entenderán tan bien como si especificase el nombre de lacolumna real. Además, para realizar cambios en la lista de selección, como modificar el orden de las columnas o agregar otras nuevas, será preciso modificar lacláusula ORDER BY a fin de evitar resultados inesperados.

En una instrucción SELECT TOP ﴾N﴿, utilice siempre una cláusula ORDER BY. Esta es la única manera de indicar previsiblemente a qué filas afecta TOP. Para obtener másinformación, vea TOP (Transact-SQL).

Interoperabilidad

Cuando se utiliza con una instrucción SELECT...INTO para insertar filas de otro origen, la cláusula ORDER BY no garantiza la inserción de las filas en el ordenespecificado.

Al usar OFFSET y FETCH en una vista no se cambia la propiedad Updateability de la vista.

Limitaciones y restricciones

No hay ningún límite en cuanto al número de columnas de la cláusula ORDER BY; sin embargo, el tamaño total de las columnas especificadas en una cláusula ORDER BYno puede superar los 8.060 bytes.

Las columnas de tipo ntext, text, image, geography, geometry y xml no se pueden usar en una cláusula ORDER BY.

No puede especificarse un entero o una constante cuando en una función de categoría aparece order_by_expression . Para obtener más información, vea OVER ﴾cláusulade Transact-SQL).

Si los nombres de tabla están asociados a un alias en la cláusula FROM, solo pueden usarse los nombres de alias para calificar sus columnas en la cláusula ORDER BY.

Los nombres y alias de columna especificados en la cláusula ORDER BY deben estar definidos en la lista de selección si la instrucción SELECT contiene uno de losoperadores o cláusulas siguientes:

UNION, operador

EXCEPT, operador

INTERSECT, operador

SELECT DISTINCT

Además, cuando la instrucción incluye un operador UNION, EXCEPT o INTERSECT, los nombres o los alias de columna deben ser los especificados en la lista deselección de la primera consulta ﴾lado izquierdo﴿.

En una consulta que utiliza los operadores UNION, INTERSECT o EXCEPT, ORDER BY se permite únicamente al final de la instrucción. Esta restricción se aplicaúnicamente cuando se especifica UNION, EXCEPT e INTERSECT en una consulta de nivel superior y no en una subconsulta. Vea la sección Ejemplos que aparece másadelante.

La cláusula ORDER BY no es válida en vistas, funciones insertadas, tablas derivadas y subconsultas, a menos que se especifiquen también las cláusulas TOP u OFFSET yFETCH. Cuando ORDER BY se utiliza en estos objetos, la cláusula únicamente se utiliza para determinar las filas devueltas por la cláusula TOP o las cláusulas OFFSET YFETCH. La cláusula ORDER BY no garantiza resultados ordenados cuando se consulten estos constructores, a menos que también se especifique ORDER BY en la mismaconsulta.

OFFSET y FETCH no se admiten en vistas indizadas ni en vistas definidas mediante la cláusula CHECK OPTION.

OFFSET y FETCH se pueden utilizar en cualquier consulta que permita TOP y ORDER BY con las siguientes limitaciones:

La cláusula OVER no admite OFFSET ni FETCH.

OFFSET y FETCH no se pueden especificar directamente en las instrucciones INSERT, UPDATE, MERGE ni DELETE, pero sí en una subconsulta definida en ellas. Por

ejemplo, en la instrucción INSERT INTO SELECT, se pueden especificar OFFSET y FETCH en la instrucción SELECT.

En una consulta que utiliza los operadores UNION, EXCEPT o INTERSECT, OFFSET y FETCH únicamente se pueden utilizar en la consulta final que especifica elorden de los resultados de la consulta.

TOP no se puede combinar con OFFSET y FETCH en la misma expresión de consulta ﴾en el mismo ámbito de la consulta﴿.

Utilizar OFFSET y FETCH para limitar las filas devueltas

Page 33: Microsoft SELECT Transact SQL 2014

Recomendamos utilizar las cláusulas OFFSET y FETCH en lugar de la cláusula TOP para implementar una solución de paginación de consulta y limitar el número de filasenviadas a una aplicación cliente.

Para utilizar OFFSET y FETCH como solución de paginación, es preciso ejecutar la consulta una vez por cada "página" de datos devuelta a la aplicación cliente. Por

ejemplo, para devolver los resultados de una consulta en incrementos de 10 filas, se debe ejecutar la consulta una vez para devolver las filas de 1 a 10, después otravez para devolver las filas de 11 a 20, y así sucesivamente. Cada consulta es independiente y no está relacionada con las demás de forma alguna. Esto significa que, a

diferencia de cuando se usa un cursor en que la consulta se ejecuta una vez y su estado se mantiene en el servidor, en este caso es la aplicación cliente la responsablede realizar el seguimiento del estado. Para lograr resultados estables entre las solicitudes de consultas donde se utilicen OFFSET y FETCH, se deben cumplir las

siguientes condiciones:

1. Los datos subyacentes que la consulta utilice no deben cambiar. Es decir, o bien las filas afectadas por la consulta no se actualizarán, o bien todas las solicitudescorrespondientes a las páginas de la consulta se ejecutarán en una transacción única utilizando el aislamiento de transacción serializable o de instantánea. Para

obtener información acerca de estos niveles de aislamiento de transacción, vea SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

2. Debe garantizarse que la columna o combinación de columnas contenidas en la cláusula ORDER BY sean únicas.

Vea el ejemplo que "Ejecutar varias consultas en una sola transacción" en la sección Ejemplos que aparece más adelante en este tema.

Si el hecho de que los planes de ejecución sean coherentes es importante para su solución de paginación, puede ser conveniente utilizar la sugerencia de consultaOPTIMIZE FOR para los parámetros de OFFSET y FETCH. Vea "Especificar expresiones para valores de OFFSET y FETCH" en la sección Ejemplos que aparece másadelante en este tema. Para obtener más información acerca de OPTIMIZE FOR, vea Sugerencias de consulta (Transact-SQL).

Ejemplos

Categoría Elementos de sintaxis ofrecidos

Sintaxis básica ORDER BY

Especificar orden ascendente y descendente DESC • ASC

Especificar una intercalación COLLATE

Especificar un orden condicional CASE, expresión

Usar ORDER BY en una función de categoría Funciones de categoría

Limitar el número de filas devueltas OFFSET • FETCH

Usar ORDER BY con UNION, EXCEPT e INTERSECT UNION

Sintaxis básicaEn los ejemplos de esta sección se muestra la funcionalidad básica de la cláusula ORDER BY utilizando la sintaxis mínima requerida.

A.Especificar una sola columna definida en la lista de selecciónEn el siguiente ejemplo se ordena el conjunto de resultados por la columna numérica ProductID. Dado que no se especifica un criterio de ordenación concreto, seutiliza el valor predeterminado (orden ascendente).

B.Especificar una columna que no está definida en la lista de selecciónEn el siguiente ejemplo se ordena el conjunto de resultados por una columna que no está incluida en la lista de selección, pero sí definida en la tabla especificada en lacláusula FROM.

C.Especificar un alias como columna de ordenaciónEn el ejemplo siguiente se especifica el alias de columna SchemaName como columna de criterio de ordenación.

USE AdventureWorks2012;

GO

SELECT ProductID, Name FROM Production.Product

WHERE Name LIKE 'Lock Washer%'

ORDER BY ProductID;

USE AdventureWorks2012;

GO

SELECT ProductID, Name, Color

FROM Production.Product

ORDER BY ListPrice;

USE AdventureWorks2012;

GO

SELECT name, SCHEMA_NAME(schema_id) AS SchemaName

FROM sys.objects

WHERE type = 'U'

ORDER BY SchemaName;

Page 34: Microsoft SELECT Transact SQL 2014

D.Especificar una expresión como columna de ordenaciónEn el ejemplo siguiente se utiliza una expresión como columna de ordenación. La expresión se define mediante la función DATEPART para ordenar el conjunto deresultados según el año de contratación de los empleados.

Especificar un criterio de ordenación ascendente y descendenteA.Especificar un orden descendente

En el siguiente ejemplo se ordena el conjunto de resultados en sentido descendente según la columna numérica ProductID.

B.Especificar un orden ascendente

En el siguiente ejemplo se ordena el conjunto de resultados en orden ascendente según la columna Name. Observe que los caracteres están ordenados alfabéticamente,no numéricamente. Es decir, 10 se ordena antes que 2.

C.Especificar orden ascendente y también descendenteEn el siguiente ejemplo se ordena el conjunto de resultados según dos columnas. El conjunto de resultados se ordena en primer lugar en sentido ascendente según lacolumna FirstName y, a continuación, en orden descendente según la columna LastName.

Especificar una intercalaciónEn el siguiente ejemplo se muestra cómo especificar una intercalación en la cláusula ORDER BY puede cambiar el orden en que se devuelven los resultados de laconsulta. Se crea una tabla que contiene una columna definida mediante una intercalación que no distingue entre mayúsculas y minúsculas, ni las tildes. Los valores se

insertan con diversas diferencias de uso de mayúsculas, minúsculas y tildes. Dado que no se especifica ninguna intercalación en la cláusula ORDER BY, la primeraconsulta utiliza la intercalación de la columna al ordenar los valores. En la segunda consulta, se especifica una intercalación que distingue entre mayúsculas y minúsculasy las tildes; en consecuencia, cambia el orden en el que se devuelven las filas.

Especificar un orden condicional

En los ejemplos siguientes se utiliza la expresión CASE en una cláusula ORDER BY para determinar de manera condicional el criterio de ordenación de las filas según elvalor de una columna dada. En el primer ejemplo se evalúe el valor de la columna SalariedFlag de la tabla HumanResources.Employee. Los empleados que tienen la

columna SalariedFlag establecida en 1 se devuelven en orden descendente según el BusinessEntityID. Los empleados que tienen la columna SalariedFlag

establecida en 0 se devuelven en orden ascendente según el BusinessEntityID. En el segundo ejemplo, el conjunto de resultados se ordena según la columnaTerritoryName cuando la columna CountryRegionName es igual a 'United States' y según la columna CountryRegionName en las demás filas.

USE AdventureWorks2012;

GO

SELECT BusinessEntityID, JobTitle, HireDate

FROM HumanResources.Employee

ORDER BY DATEPART(year, HireDate);

USE AdventureWorks2012;

GO

SELECT ProductID, Name FROM Production.Product

WHERE Name LIKE 'Lock Washer%'

ORDER BY ProductID DESC;

USE AdventureWorks2012;

GO

SELECT ProductID, Name FROM Production.Product

WHERE Name LIKE 'Lock Washer%'

ORDER BY Name ASC ;

USE AdventureWorks2012;

GO

SELECT LastName, FirstName FROM Person.Person

WHERE LastName LIKE 'R%'

ORDER BY FirstName ASC, LastName DESC ;

USE tempdb;

GO

CREATE TABLE #t1 (name nvarchar(15) COLLATE Latin1_General_CI_AI)

GO

INSERT INTO #t1 VALUES(N'Sánchez'),(N'Sanchez'),(N'sánchez'),(N'sanchez');

-- This query uses the collation specified for the column 'name' for sorting.

SELECT name

FROM #t1

ORDER BY name;

-- This query uses the collation specified in the ORDER BY clause for sorting.

SELECT name

FROM #t1

ORDER BY name COLLATE Latin1_General_CS_AS;

Page 35: Microsoft SELECT Transact SQL 2014

Usar ORDER BY en una función de categoríaEn el siguiente ejemplo se utiliza la cláusula ORDER BY en las funciones de categoría ROW_NUMBER, RANK, DENSE_RANK y NTILE.

Limitar el número de filas devueltasEn los siguientes ejemplos se utiliza OFFSET y FETCH para limitar el número de filas devueltas por una consulta.

Se aplica a: SQL Server 2012 a SQL Server 2014 y Windows Azure SQL Database.

A.Especificar constantes enteras para los valores de OFFSET y FETCH

En el siguiente ejemplo se especifica una constante entera como valor para las cláusulas OFFSET y FETCH. La primera consulta devuelve todas las filas ordenadas segúnla columna DepartmentID. Compare los resultados devueltos por esta consulta con los de las dos consultas siguientes. La consulta siguiente utiliza la cláusula OFFSET 5ROWS para omitir las primeras 5 filas y devolver todas las restantes. La última consulta utiliza la cláusula OFFSET 0 ROWS para comenzar por la primera fila y, a

continuación, utiliza FETCH NEXT 10 ROWS ONLY para limitar las filas devueltas a 10 filas del conjunto de resultados ordenado.

B.Especificar variables para los valores de OFFSET y FETCH

En el siguiente ejemplo se declaran las variables @StartingRowNumber y @FetchRows, y se especifican estas variables en las cláusulas OFFSET y FETCH.

SELECT BusinessEntityID, SalariedFlag

FROM HumanResources.Employee

ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC

,CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END;

GO

SELECT BusinessEntityID, LastName, TerritoryName, CountryRegionName

FROM Sales.vSalesPerson

WHERE TerritoryName IS NOT NULL

ORDER BY CASE CountryRegionName WHEN 'United States' THEN TerritoryName

ELSE CountryRegionName END;

USE AdventureWorks2012;

GO

SELECT p.FirstName, p.LastName

,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS "Row Number"

,RANK() OVER (ORDER BY a.PostalCode) AS "Rank"

,DENSE_RANK() OVER (ORDER BY a.PostalCode) AS "Dense Rank"

,NTILE(4) OVER (ORDER BY a.PostalCode) AS "Quartile"

,s.SalesYTD, a.PostalCode

FROM Sales.SalesPerson AS s

INNER JOIN Person.Person AS p

ON s.BusinessEntityID = p.BusinessEntityID

INNER JOIN Person.Address AS a

ON a.AddressID = p.BusinessEntityID

WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0;

USE AdventureWorks2012;

GO

-- Return all rows sorted by the column DepartmentID.

SELECT DepartmentID, Name, GroupName

FROM HumanResources.Department

ORDER BY DepartmentID;

-- Skip the first 5 rows from the sorted result set and return all remaining rows.

SELECT DepartmentID, Name, GroupName

FROM HumanResources.Department

ORDER BY DepartmentID OFFSET 5 ROWS;

-- Skip 0 rows and return only the first 10 rows from the sorted result set.

SELECT DepartmentID, Name, GroupName

FROM HumanResources.Department

ORDER BY DepartmentID

OFFSET 0 ROWS

FETCH NEXT 10 ROWS ONLY;

USE AdventureWorks2012;

GO

-- Specifying variables for OFFSET and FETCH values

DECLARE @StartingRowNumber tinyint = 1

, @FetchRows tinyint = 8;

SELECT DepartmentID, Name, GroupName

FROM HumanResources.Department

ORDER BY DepartmentID ASC

OFFSET @StartingRowNumber ROWS

Page 36: Microsoft SELECT Transact SQL 2014

C.Especificar expresiones para los valores de OFFSET y FETCH

En el siguiente ejemplo se utiliza la expresión @StartingRowNumber - 1 para especificar el valor de OFFSET y la expresión @EndingRowNumber - @StartingRowNumber+ 1 para especificar el valor de FETCH. Además, se especifica la sugerencia de consulta OPTIMIZE FOR. Esta sugerencia se puede usar para que se utilice un valor

concreto para una variable local al compilar y optimizar la consulta. El valor se utiliza solo durante la optimización de la consulta y no durante la ejecución de la misma.Para obtener más información, vea Sugerencias de consulta (Transact-SQL).

D.Especificar una subconsulta escalar constante para los valores de OFFSET y FETCH

En el siguiente ejemplo se utiliza una subconsulta escalar constante a fin de definir el valor para la cláusula FETCH. La subconsulta devuelve un valor único de la columnaPageSize de la tabla dbo.AppSettings.

E.Ejecutar varias consultas en una sola transacciónEn el siguiente ejemplo se muestra un método de implementar una solución de paginación que permite asegurarse de la devolución de resultados estables en todaslas solicitudes de la consulta. La consulta se ejecuta en una sola transacción utilizando el nivel de aislamiento de instantánea, mientras que la columna especificada en lacláusula ORDER BY asegura la singularidad de la columna.

FETCH NEXT @FetchRows ROWS ONLY;

USE AdventureWorks2012;

GO

-- Specifying expressions for OFFSET and FETCH values

DECLARE @StartingRowNumber tinyint = 1

, @EndingRowNumber tinyint = 8;

SELECT DepartmentID, Name, GroupName

FROM HumanResources.Department

ORDER BY DepartmentID ASC

OFFSET @StartingRowNumber - 1 ROWS

FETCH NEXT @EndingRowNumber - @StartingRowNumber + 1 ROWS ONLY

OPTION ( OPTIMIZE FOR (@StartingRowNumber = 1, @EndingRowNumber = 20) );

-- Specifying a constant scalar subquery

USE AdventureWorks2012;

GO

CREATE TABLE dbo.AppSettings (AppSettingID int NOT NULL, PageSize int NOT NULL);

GO

INSERT INTO dbo.AppSettings VALUES(1, 10);

GO

DECLARE @StartingRowNumber tinyint = 1;

SELECT DepartmentID, Name, GroupName

FROM HumanResources.Department

ORDER BY DepartmentID ASC

OFFSET @StartingRowNumber ROWS

FETCH NEXT (SELECT PageSize FROM dbo.AppSettings WHERE AppSettingID = 1) ROWS ONLY;

USE AdventureWorks2012;

GO

-- Ensure the database can support the snapshot isolation level set for the query.

IF (SELECT snapshot_isolation_state FROM sys.databases WHERE name = N'AdventureWorks2012') = 0

ALTER DATABASE AdventureWorks2012 SET ALLOW_SNAPSHOT_ISOLATION ON;

GO

-- Set the transaction isolation level to SNAPSHOT for this query.

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

GO

-- Beging the transaction

BEGIN TRANSACTION;

GO

-- Declare and set the variables for the OFFSET and FETCH values.

DECLARE @StartingRowNumber int = 1

, @RowCountPerPage int = 3;

-- Create the condition to stop the transaction after all rows have been returned.

WHILE (SELECT COUNT(*) FROM HumanResources.Department) >= @StartingRowNumber

BEGIN

-- Run the query until the stop condition is met.

SELECT DepartmentID, Name, GroupName

FROM HumanResources.Department

ORDER BY DepartmentID ASC

OFFSET @StartingRowNumber - 1 ROWS

FETCH NEXT @RowCountPerPage ROWS ONLY;

-- Increment @StartingRowNumber value.

SET @StartingRowNumber = @StartingRowNumber + @RowCountPerPage;

CONTINUE

END;

GO

Page 37: Microsoft SELECT Transact SQL 2014

Usar ORDER BY con UNION, EXCEPT e INTERSECT

Cuando una consulta utiliza los operadores UNION, EXCEPT o INTERSECT, la cláusula ORDER BY se debe especificar al final de la instrucción y se ordenan los resultadosde las consultas combinadas. En el siguiente ejemplo se devuelven todos los productos que son rojos o amarillos y la lista combinada se ordena según la columnaListPrice.

Vea también

ReferenciaExpresiones (Transact-SQL)

SELECT (Transact-SQL)

FROM (Transact-SQL)

Funciones de categoría ﴾Transact‐SQL﴿TOP (Transact-SQL)

Sugerencias de consulta (Transact-SQL)

EXCEPT e INTERSECT (Transact-SQL)

UNION (Transact-SQL)

CASE (Transact-SQL)

© 2014 Microsoft

COMMIT TRANSACTION;

GO

USE AdventureWorks2012;

GO

SELECT Name, Color, ListPrice

FROM Production.Product

WHERE Color = 'Red'

-- ORDER BY cannot be specified here.

UNION ALL

SELECT Name, Color, ListPrice

FROM Production.Product

WHERE Color = 'Yellow'

ORDER BY ListPrice ASC;

Page 38: Microsoft SELECT Transact SQL 2014

OVER ﴾cláusula de Transact‐SQL﴿

Determina las particiones y el orden de un conjunto de filas antes de que se aplique la función de ventana asociada. Es decir, la cláusula OVER define una ventana o unconjunto de filas definido por el usuario en un conjunto de resultados de la consulta. Una función de ventana calcula entonces un valor para cada fila de la ventana. Puede

utilizar la cláusula OVER con funciones para calcular valores agregados tales como medias móviles, agregados acumulados, totales acumulados o N elementos superiorespor resultados del grupo.

Se aplica a:

Funciones de categoría

Funciones de agregado

Funciones analíticas

Función NEXT VALUE FOR

Se aplica a: SQL Server (SQL Server 2008 a versión actual﴿, Windows Azure SQL Database ﴾Versión inicial a versión actual).

Convenciones de sintaxis de Transact-SQL

Sintaxis

Argumentos

PARTITION BY

Divide el conjunto de resultados de la consulta en particiones. La función se aplica a cada partición por separado y el cálculo se reinicia para cada partición.

SQL Server 2014

OVER (

[ <PARTITION BY clause> ]

[ <ORDER BY clause> ]

[ <ROW or RANGE clause> ]

)

<PARTITION BY clause> ::=

PARTITION BY value_expression , ... [ n ]

<ORDER BY clause> ::=

ORDER BY order_by_expression

[ COLLATE collation_name ]

[ ASC | DESC ]

[ ,...n ]

<ROW or RANGE clause> ::=

{ ROWS | RANGE } <window frame extent>

<window frame extent> ::=

{ <window frame preceding>

| <window frame between>

}

<window frame between> ::=

BETWEEN <window frame bound> AND <window frame bound>

<window frame bound> ::=

{ <window frame preceding>

| <window frame following>

}

<window frame preceding> ::=

{

UNBOUNDED PRECEDING

| <unsigned_value_specification> PRECEDING

| CURRENT ROW

}

<window frame following> ::=

{

UNBOUNDED FOLLOWING

| <unsigned_value_specification> FOLLOWING

| CURRENT ROW

}

<unsigned value specification> ::=

{ <unsigned integer literal> }

Page 39: Microsoft SELECT Transact SQL 2014

value_expression

Especifica la columna a partir de la cual se particiona el conjunto de filas. value_expression solo puede hacer referencia a columnas disponibles a través de lacláusula FROM. value_expression no puede hacer referencia a expresiones ni a alias de la lista de selección. value_expression puede ser una expresión de columna,una subconsulta escalar, una función escalar o una variable definida por el usuario.

<Cláusula ORDER BY>Define el orden lógico de las filas dentro de cada partición del conjunto de resultados. Es decir, especifica el orden lógico en el que se realiza el cálculo de lafunción de ventana.

order_by_expression

Especifica la columna o expresión según la cual se va a realizar la ordenación. order_by_expression solo puede hacer referencia a columnas disponibles a travésde la cláusula FROM. No se puede especificar un número entero para representar un nombre de columna o alias.

COLLATE collation_name

Especifica que la operación ORDER BY se debe realizar según la intercalación especificada en collation_name. collation_name puede ser un nombre de

intercalación de Windows o un nombre de intercalación de SQL. Para obtener más información, vea Compatibilidad con la intercalación y Unicode. COLLATE solo

se aplica a las columnas con tipos de datos char, varchar, nchar y nvarchar.

ASC | DESC

Indica que los valores de la columna especificada se deben ordenar en sentido ascendente o descendente. ASC es el criterio de ordenación predeterminado. Los

valores NULL se tratan como los valores más bajos posibles.

ROWS | RANGE

Se aplica a: SQL Server 2012 a SQL Server 2014.

Limita aún más las filas de la partición especificando los puntos inicial y final. Para ello, se especifica un rango de filas con respecto a la fila actual mediante

asociación lógica o asociación física. La asociación física se realiza mediante la cláusula ROWS.

La cláusula ROWS restringe las filas dentro de una partición especificando un número fijo de filas delante y detrás de la fila actual. La cláusula RANGE tambiénpuede restringir lógicamente las filas de una partición especificando un rango de valores con respecto al valor de la fila actual. Las filas precedentes y siguientes

se definen en función de la ordenación de la cláusula ORDER BY. El marco de ventana “RANGE … CURRENT ROW …” incluye todas las filas que tienen los mismosvalores en la expresión ORDER BY que la fila actual. Por ejemplo, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW indica que la ventana de filas en la que

opera la función tiene un tamaño de tres filas, con dos filas delante hasta e inclusive la fila actual.

Nota

ROWS o RANGE requieren que se especifique la cláusula ORDER BY. Si ORDER BY contiene varias expresiones de orden, CURRENT ROW FOR RANGE

considera todas las columnas de la lista ORDER BY al determinar la fila actual.

UNBOUNDED PRECEDING

Se aplica a: SQL Server 2012 a SQL Server 2014.

Especifica que la ventana comienza en la primera fila de la partición. UNBOUNDED PRECEDING solo se puede especificar como punto inicial de la ventana.

<especificación de valor sin signo> PRECEDINGSe especifica con <especificación de valor sin signo> para indicar el número de filas o valores que preceden a la fila actual. Esta especificación no se permitepara RANGE.

CURRENT ROW

Se aplica a: SQL Server 2012 a SQL Server 2014.

Especifica que la ventana comienza o termina en la fila actual cuando se utiliza con ROWS, o el valor actual cuando se utiliza con RANGE. CURRENT ROW se

puede especificar como punto inicial o final.

BETWEEN <límite del marco de ventana > AND <límite del marco de ventana >

Se aplica a: SQL Server 2012 a SQL Server 2014.

Se utiliza con ROWS o RANGE para especificar los puntos de límite inferior ﴾inicio﴿ y superior ﴾final﴿ de la ventana. <límite del marco de ventana> define el puntoinicial del límite y <límite del marco de ventana> define el punto final. El límite superior no puede ser menor que el límite inferior.

UNBOUNDED FOLLOWING

Se aplica a: SQL Server 2012 a SQL Server 2014.

Especifica que la ventana termina en la última fila de la partición. UNBOUNDED FOLLOWING solo se puede especificar como punto final de una ventana. Por

ejemplo, RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING define una ventana que empieza en la fila actual y termina en la última fila de lapartición.

<especificación de valor sin signo> FOLLOWINGSe especifica con <especificación de valor sin signo> para indicar el número de filas o valores detrás de la fila actual. Cuando <especificación de valor sinsigno> FOLLOWING se especifica como punto inicial de la ventana, el punto final debe ser <especificación de valor sin signo>FOLLOWING. Por ejemplo, ROWS

BETWEEN 2 FOLLOWING AND 10 FOLLOWING define una ventana que empieza en la segunda fila a partir de la fila actual y termina en la décima fila a partir de lafila actual. Esta especificación no se permite para RANGE.

literal entero sin signo

Se aplica a: SQL Server 2012 a SQL Server 2014.

Es un literal entero positivo ﴾incluido el 0﴿ que especifica el número de filas o de valores delante o detrás de la fila o el valor actual. Esta especificación es válidasolamente para ROWS.

Comentarios generales

Page 40: Microsoft SELECT Transact SQL 2014

Se pueden utilizar varias funciones de ventana en una sola consulta con una única cláusula FROM. La cláusula OVER de cada función puede diferir en particiones ytambién en orden.

Si no se especifica PARTITION BY, la función trata todas las filas del conjunto de resultados de la consulta como un único grupo.

Si no se especifica ORDER BY, se utiliza la partición completa para el marco de ventana. Esto se aplica únicamente a las funciones que no requieren la cláusula ORDERBY. Si no se especifica ROWS/RANGE pero sí ORDER BY, RANGE UNBOUNDED PRECEDING AND CURRENT ROW se utiliza como valor predeterminado para el marco deventana. Esto se aplica solamente a las funciones que pueden aceptar la especificación opcional de ROWS/RANGE. Por ejemplo, las funciones de clasificación nopueden aceptar ROWS/RANGE; por lo tanto, este marco de ventana no se aplica aunque se especifique ORDER BY y no se especifique ROWS/RANGE.

Si se especifica ROWS/RANGE y se usa <marco de ventana precedente> para <extensión de marco de ventana> ﴾sintaxis abreviada﴿, esta especificación se usa para elpunto inicial del límite del marco de ventana y CURRENT ROW se usa para el punto final. Por ejemplo, “ROWS 5 PRECEDING” es igual a “ROWS BETWEEN 5 PRECEDINGAND CURRENT ROW”.

Limitaciones y restricciones

No se puede utilizar la cláusula OVER con la función de agregado CHECKSUM.

No se puede utilizar RANGE con <especificación de valor sin signo> PRECEDING o <especificación de valor sin signo> FOLLOWING.

Dependiendo de la función de clasificación, de agregado o analítica utilizada con la cláusula OVER, puede que no se admitan la <cláusula ORDER BY> o la <cláusulaROWS y RANGE>.

Ejemplos

A.Utilizar la cláusula OVER con la función ROW_NUMBEREn el ejemplo siguiente se muestra cómo usar la cláusula OVER con la función ROW_NUMBER para mostrar un número de fila para cada fila de una partición. La

cláusula ORDER BY especificada en la cláusula OVER ordena las filas de cada partición por la columna SalesYTD. La cláusula ORDER BY en la instrucción SELECTdetermina el orden en que se devuelve el conjunto completo de resultados de la consulta.

El conjunto de resultados es el siguiente.

Row Number LastName SalesYTD PostalCode

--------------- ----------------------- --------------------- ----------

1 Mitchell 4251368.5497 98027

2 Blythe 3763178.1787 98027

3 Carson 3189418.3662 98027

4 Reiter 2315185.611 98027

5 Vargas 1453719.4653 98027

6 Ansman-Wolfe 1352577.1325 98027

1 Pak 4116871.2277 98055

2 Varkey Chudukatil 3121616.3202 98055

3 Saraiva 2604540.7172 98055

4 Ito 2458535.6169 98055

5 Valdez 1827066.7118 98055

6 Mensa-Annan 1576562.1966 98055

7 Campbell 1573012.9383 98055

8 Tsoflias 1421810.9242 98055

USE AdventureWorks2012;

GO

SELECT ROW_NUMBER() OVER(PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS "Row Number",

p.LastName, s.SalesYTD, a.PostalCode

FROM Sales.SalesPerson AS s

INNER JOIN Person.Person AS p

ON s.BusinessEntityID = p.BusinessEntityID

INNER JOIN Person.Address AS a

ON a.AddressID = p.BusinessEntityID

WHERE TerritoryID IS NOT NULL

AND SalesYTD <> 0

ORDER BY PostalCode;

GO

Page 41: Microsoft SELECT Transact SQL 2014

B.Utilizar la cláusula OVER con funciones de agregadoEn el ejemplo siguiente se utiliza la cláusula OVER con funciones de agregado en todas las filas devueltas por la consulta. En este ejemplo, el uso de OVER es más eficazque usar subconsultas para obtener los valores agregados.

El conjunto de resultados es el siguiente.

En el ejemplo siguiente se muestra el uso de la cláusula OVER con una función de agregado en un valor calculado.

El conjunto de resultados es el siguiente. Tenga en cuenta que los agregados se calculan mediante SalesOrderID y se calcula Percent by ProductID para cada líneade cada SalesOrderID.

USE AdventureWorks2012;

GO

SELECT SalesOrderID, ProductID, OrderQty

,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS Total

,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Avg"

,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Count"

,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Min"

,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Max"

FROM Sales.SalesOrderDetail

WHERE SalesOrderID IN(43659,43664);

GO

SalesOrderID ProductID OrderQty Total Avg Count Min Max

------------ ----------- -------- ----------- ----------- ----------- ------ ------

43659 776 1 26 2 12 1 6

43659 777 3 26 2 12 1 6

43659 778 1 26 2 12 1 6

43659 771 1 26 2 12 1 6

43659 772 1 26 2 12 1 6

43659 773 2 26 2 12 1 6

43659 774 1 26 2 12 1 6

43659 714 3 26 2 12 1 6

43659 716 1 26 2 12 1 6

43659 709 6 26 2 12 1 6

43659 712 2 26 2 12 1 6

43659 711 4 26 2 12 1 6

43664 772 1 14 1 8 1 4

43664 775 4 14 1 8 1 4

43664 714 1 14 1 8 1 4

43664 716 1 14 1 8 1 4

43664 777 2 14 1 8 1 4

43664 771 3 14 1 8 1 4

43664 773 1 14 1 8 1 4

43664 778 1 14 1 8 1 4

USE AdventureWorks2012;

GO

SELECT SalesOrderID, ProductID, OrderQty

,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS Total

,CAST(1. * OrderQty / SUM(OrderQty) OVER(PARTITION BY SalesOrderID)

*100 AS DECIMAL(5,2))AS "Percent by ProductID"

FROM Sales.SalesOrderDetail

WHERE SalesOrderID IN(43659,43664);

GO

SalesOrderID ProductID OrderQty Total Percent by ProductID

------------ ----------- -------- ----------- ---------------------------------------

43659 776 1 26 3.85

43659 777 3 26 11.54

43659 778 1 26 3.85

43659 771 1 26 3.85

43659 772 1 26 3.85

43659 773 2 26 7.69

43659 774 1 26 3.85

43659 714 3 26 11.54

43659 716 1 26 3.85

43659 709 6 26 23.08

43659 712 2 26 7.69

43659 711 4 26 15.38

43664 772 1 14 7.14

43664 775 4 14 28.57

43664 714 1 14 7.14

43664 716 1 14 7.14

43664 777 2 14 14.29

43664 771 3 14 21.4

Page 42: Microsoft SELECT Transact SQL 2014

C.Producir una media móvil y un total acumulativoEn el ejemplo siguiente se usan las funciones AVG y SUM con la cláusula OVER para proporcionar una media móvil y un total acumulado de ventas anuales para cadaterritorio de la tabla Sales.SalesPerson. Se crean particiones de los datos por TerritoryID y se ordenan lógicamente por SalesYTD. Esto significa que la funciónAVG se calcula para cada territorio en función del año de ventas. Observe que para TerritoryID 1, solo hay dos filas para el año de ventas 2005, que representan losdos vendedores con ventas durante ese año. Se calculan las ventas medias de estas dos filas y la tercera fila que representa las ventas durante el año 2006 se incluyeen el cálculo.

El conjunto de resultados es el siguiente.

En este ejemplo, la cláusula OVER no incluye PARTITION BY. Esto significa que la función se aplicará a todas las filas devueltas por la consulta. La cláusula ORDER BYespecificada en la cláusula OVER determina el orden lógico al que se aplica la función AVG. La consulta devuelve una media móvil de ventas por año para todos losterritorios de ventas especificados en la cláusula WHERE. La cláusula ORDER BY especificada en la instrucción SELECT determina el orden en que se muestran las filas dela consulta.

El conjunto de resultados es el siguiente.

43664 773 1 14 7.14

43664 778 1 14 7.14

(20 row(s) affected)

USE AdventureWorks2012;

GO

SELECT BusinessEntityID, TerritoryID

,DATEPART(yy,ModifiedDate) AS SalesYear

,CONVERT(varchar(20),SalesYTD,1) AS SalesYTD

,CONVERT(varchar(20),AVG(SalesYTD) OVER (PARTITION BY TerritoryID

ORDER BY DATEPART(yy,ModifiedDate)

),1) AS MovingAvg

,CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID

ORDER BY DATEPART(yy,ModifiedDate)

),1) AS CumulativeTotal

FROM Sales.SalesPerson

WHERE TerritoryID IS NULL OR TerritoryID < 5

ORDER BY TerritoryID,SalesYear;

BusinessEntityID TerritoryID SalesYear SalesYTD MovingAvg CumulativeTotal

---------------- ----------- ----------- -------------------- -------------------- --------------------

274 NULL 2005 559,697.56 559,697.56 559,697.56

287 NULL 2006 519,905.93 539,801.75 1,079,603.50

285 NULL 2007 172,524.45 417,375.98 1,252,127.95

283 1 2005 1,573,012.94 1,462,795.04 2,925,590.07

280 1 2005 1,352,577.13 1,462,795.04 2,925,590.07

284 1 2006 1,576,562.20 1,500,717.42 4,502,152.27

275 2 2005 3,763,178.18 3,763,178.18 3,763,178.18

277 3 2005 3,189,418.37 3,189,418.37 3,189,418.37

276 4 2005 4,251,368.55 3,354,952.08 6,709,904.17

281 4 2005 2,458,535.62 3,354,952.08 6,709,904.17

(10 row(s) affected)

SELECT BusinessEntityID, TerritoryID

,DATEPART(yy,ModifiedDate) AS SalesYear

,CONVERT(varchar(20),SalesYTD,1) AS SalesYTD

,CONVERT(varchar(20),AVG(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate)

),1) AS MovingAvg

,CONVERT(varchar(20),SUM(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate)

),1) AS CumulativeTotal

FROM Sales.SalesPerson

WHERE TerritoryID IS NULL OR TerritoryID < 5

ORDER BY SalesYear;

BusinessEntityID TerritoryID SalesYear SalesYTD MovingAvg CumulativeTotal

---------------- ----------- ----------- -------------------- -------------------- --------------------

274 NULL 2005 559,697.56 2,449,684.05 17,147,788.35

275 2 2005 3,763,178.18 2,449,684.05 17,147,788.35

276 4 2005 4,251,368.55 2,449,684.05 17,147,788.35

277 3 2005 3,189,418.37 2,449,684.05 17,147,788.35

280 1 2005 1,352,577.13 2,449,684.05 17,147,788.35

281 4 2005 2,458,535.62 2,449,684.05 17,147,788.35

283 1 2005 1,573,012.94 2,449,684.05 17,147,788.35

284 1 2006 1,576,562.20 2,138,250.72 19,244,256.47

287 NULL 2006 519,905.93 2,138,250.72 19,244,256.47

285 NULL 2007 172,524.45 1,941,678.09 19,416,780.93

(10 row(s) affected)

Page 43: Microsoft SELECT Transact SQL 2014

D.Especificar la cláusula ROWS

Se aplica a: SQL Server 2012 a SQL Server 2014.

En el ejemplo siguiente se utiliza la cláusula ROWS para definir una ventana de cuyas filas se calcula la fila actual y el número N de filas incluidas a continuación ﴾1 fila eneste ejemplo).

El conjunto de resultados es el siguiente.

En el ejemplo siguiente, la cláusula ROWS se especifica con UNBOUNDED PRECEDING. El resultado es que la ventana comienza en la primera fila de la partición.

El conjunto de resultados es el siguiente.

Vea también

ReferenciaFunciones de agregado (Transact-SQL)

ConceptosFunciones analíticas ﴾Transact‐SQL﴿

© 2014 Microsoft

SELECT BusinessEntityID, TerritoryID

,CONVERT(varchar(20),SalesYTD,1) AS SalesYTD

,DATEPART(yy,ModifiedDate) AS SalesYear

,CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID

ORDER BY DATEPART(yy,ModifiedDate)

ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ),1) AS CumulativeTotal

FROM Sales.SalesPerson

WHERE TerritoryID IS NULL OR TerritoryID < 5;

BusinessEntityID TerritoryID SalesYTD SalesYear CumulativeTotal

---------------- ----------- -------------------- ----------- --------------------

274 NULL 559,697.56 2005 1,079,603.50

287 NULL 519,905.93 2006 692,430.38

285 NULL 172,524.45 2007 172,524.45

283 1 1,573,012.94 2005 2,925,590.07

280 1 1,352,577.13 2005 2,929,139.33

284 1 1,576,562.20 2006 1,576,562.20

275 2 3,763,178.18 2005 3,763,178.18

277 3 3,189,418.37 2005 3,189,418.37

276 4 4,251,368.55 2005 6,709,904.17

281 4 2,458,535.62 2005 2,458,535.62

SELECT BusinessEntityID, TerritoryID

,CONVERT(varchar(20),SalesYTD,1) AS SalesYTD

,DATEPART(yy,ModifiedDate) AS SalesYear

,CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID

ORDER BY DATEPART(yy,ModifiedDate)

ROWS UNBOUNDED PRECEDING),1) AS CumulativeTotal

FROM Sales.SalesPerson

WHERE TerritoryID IS NULL OR TerritoryID < 5;

BusinessEntityID TerritoryID SalesYTD SalesYear CumulativeTotal

---------------- ----------- -------------------- ----------- --------------------

274 NULL 559,697.56 2005 559,697.56

287 NULL 519,905.93 2006 1,079,603.50

285 NULL 172,524.45 2007 1,252,127.95

283 1 1,573,012.94 2005 1,573,012.94

280 1 1,352,577.13 2005 2,925,590.07

284 1 1,576,562.20 2006 4,502,152.27

275 2 3,763,178.18 2005 3,763,178.18

277 3 3,189,418.37 2005 3,189,418.37

276 4 4,251,368.55 2005 4,251,368.55

281 4 2,458,535.62 2005 6,709,904.17

Page 44: Microsoft SELECT Transact SQL 2014

WITH common_table_expression (Transact-SQL)

Especifica un conjunto de resultados temporal con nombre, conocido como expresión de tabla común ﴾CTE﴿. Se deriva de una consulta simple y se define en el ámbito deejecución de una sola instrucción SELECT, INSERT, UPDATE o DELETE. Esta cláusula también se puede utilizar en una instrucción CREATE VIEW como parte de la instrucciónSELECT que la define. Una expresión de tabla común puede incluir referencias a ella misma. Esto se conoce como expresión de tabla común recursiva.

Se aplica a: SQL Server (SQL Server 2008 a versión actual﴿, Windows Azure SQL Database ﴾Versión inicial a versión actual).

Convenciones de sintaxis de Transact-SQL

Sintaxis

Argumentos

expression_name

Es un identificador válido de la expresión de tabla común. expression_name debe ser diferente del nombre de cualquier otra expresión de tabla común definidaen la misma cláusula WITH <common_table_expression>, pero expression_name puede coincidir con el nombre de una vista o tabla base. Cualquier referencia a

expression_name en la consulta utiliza la expresión de tabla común y no el objeto base.

column_name

Especifica un nombre de columna en la expresión de tabla común. No se permiten nombres duplicados en una misma definición de CTE. El número de nombresde columna especificado debe coincidir con el número de columnas del conjunto de resultados de CTE_query_definition. La lista de nombres de columna es

opcional solamente si en la definición de la consulta se suministran nombres diferentes para todas las columnas resultantes.

CTE_query_definition

Especifica una instrucción SELECT cuyo conjunto de resultados llena la expresión de tabla común. La instrucción SELECT de CTE_query_definition debe cumplir los

mismos requisitos que en la creación de una vista, excepto que una expresión CTE no puede definir otra expresión CTE. Para obtener más información, vea lasección Comentarios y el tema CREATE VIEW (Transact-SQL).

Si se definen varios parámetros CTE_query_definition, las definiciones de consulta deben combinarse mediante uno de estos operadores de conjunto: UNION ALL,

UNION, EXCEPT o INTERSECT.

Comentarios

Instrucciones para crear y utilizar expresiones de tabla comunes

Las instrucciones siguientes se aplican a expresiones de tabla comunes no recursivas. Para obtener instrucciones que se aplican a expresiones de tabla comunes

recursivas, vea "Instrucciones para definir y usar expresiones de tabla comunes recursivas" más adelante.

Una expresión CTE debe ir seguida de una única instrucción SELECT, INSERT, UPDATE o DELETE que haga referencia a una parte o a la totalidad de suscolumnas. Una expresión CTE también se puede especificar en una instrucción CREATE VIEW como parte de la instrucción SELECT de definición de la vista.

Se pueden especificar varias definiciones de consulta de CTE en una CTE no recursiva. Las definiciones deben combinarse mediante uno de estos operadores de

conjuntos: UNION ALL, UNION, INTERSECT o EXCEPT.

Una expresión CTE puede hacer referencia a ella misma y a otras expresiones CTE previamente definidas en la misma cláusula WITH. No se permite la referencia

adelantada.

No se permite especificar más de una cláusula WITH en una expresión CTE. Por ejemplo, si un argumento CTE_query_definition contiene una subconsulta, esta no

puede contener ninguna cláusula WITH anidada que defina otra expresión CTE.

No se pueden utilizar las cláusulas siguientes en la definición de CTE_query_definition:

ORDER BY ﴾excepto cuando se especifica una cláusula TOP﴿

INTO

Cláusula OPTION con sugerencias de consulta

FOR BROWSE

Cuando se utiliza una expresión CTE en una instrucción que forma parte de un lote, la instrucción que la precede debe ir seguida de punto y coma.

Una consulta que haga referencia a una CTE se puede utilizar para definir un cursor.

SQL Server 2014

[ WITH <common_table_expression> [ ,...n ] ]

<common_table_expression>::=

expression_name [ ( column_name [ ,...n ] ) ]

AS

( CTE_query_definition )

Page 45: Microsoft SELECT Transact SQL 2014

En la expresión CTE se puede hacer referencia a tablas de servidores remotos.

Cuando se ejecuta una CTE, todas las sugerencias que hagan referencia a ella pueden entrar en conflicto con otras sugerencias detectadas cuando la CTE tiene

acceso a sus tablas subyacentes, de la misma manera que las sugerencias que hacen referencia a vistas en las consultas. En ese caso, la consulta devuelve un

error.

Instrucciones para definir y usar expresiones de tabla comunes recursivas

Las instrucciones siguientes se aplican a la definición de una expresión de tabla común recursiva:

La definición de la CTE recursiva debe contener al menos dos definiciones de consulta de CTE, un miembro no recursivo y un miembro recursivo. Se pueden

definir varios miembros no recursivos y recursivos, aunque todas las definiciones de consultas de miembros no recursivos deben colocarse delante de la

primera definición de miembro recursivo. Todas las definiciones de consulta de CTE son miembros no recursivos a menos que hagan referencia a la propia CTE.

Los miembros no recursivos deben combinarse mediante uno de estos operadores de conjuntos: UNION ALL, UNION, INTERSECT o EXCEPT. UNION ALL es el

único operador de conjuntos permitido entre el último miembro no recursivo y el primer miembro recursivo, y si se combinan varios miembros recursivos.

El número de columnas de los miembros no recursivo y recursivo debe coincidir.

El tipo de datos de una columna del miembro recursivo debe ser igual al tipo de datos de la columna correspondiente en el miembro no recursivo.

La cláusula FROM de un miembro recursivo solo debe hacer referencia una vez a expression_name de CTE.

No se permiten los siguientes elementos en el parámetro CTE_query_definition de un miembro recursivo:

SELECT DISTINCT

GROUP BY

PIVOT (cuando el nivel de compatibilidad de la base de datos sea 110 o superior. Vea Cambios recientes en las características del Motor de base dedatos de SQL Server 2014).

HAVING

Agregación escalar

TOP

LEFT, RIGHT, OUTER JOIN (se permite INNER JOIN)

Subconsultas

Una sugerencia aplicada a una referencia recursiva a una CTE dentro de CTE_query_definition.

Las instrucciones siguientes se aplican al uso de una expresión de tabla común recursiva:

Todas las columnas devueltas por la expresión CTE recursiva aceptan valores NULL independientemente de la nulabilidad de las columnas devueltas por lasinstrucciones SELECT participantes.

Una expresión CTE formada incorrectamente puede generar un bucle infinito. Por ejemplo, si la definición de la consulta del miembro recursivo devuelve losmismos valores para las columnas primarias y secundarias, se crea un bucle infinito. Para evitar que se genere un bucle infinito, se puede limitar el número deniveles de recursividad permitidos para una instrucción determinada mediante el uso de la sugerencia MAXRECURSION y un valor de 0 a 32.767 en la cláusulaOPTION de la instrucción INSERT, UPDATE, DELETE o SELECT. De esta manera, se puede controlar la ejecución de la instrucción hasta que se resuelva elproblema de código que genera el bucle. El valor predeterminado de todo el servidor es 100. Cuando se especifica 0, no se aplica ningún límite. Solo se puede

especificar un valor de MAXRECURSION por instrucción. Para obtener más información, vea Sugerencias de consulta (Transact-SQL).

No se puede utilizar una vista que contenga una expresión de tabla común recursiva para actualizar datos.

Se pueden definir cursores en las consultas que utilicen expresiones CTE. La expresión CTE es el argumento de select_statement que define el conjunto de

resultados del cursor. En el caso de las CTE recursivas únicamente se permiten los cursores de solo avance rápido y estáticos ﴾de instantánea﴿. Si se especifica

otro tipo de cursor en una CTE recursiva, el tipo de cursor se convierte a estático.

En la expresión CTE se puede hacer referencia a tablas de servidores remotos. Si se hace referencia al servidor remoto en el miembro recursivo de la CTE, se

crea una cola para cada tabla remota de manera que se pueda tener acceso local a las tablas repetidas veces. Si es una consulta de CTE, aparecerá IndexSpool/Lazy Spools en el plan de consulta y tendrá el predicado adicional WITH STACK. Esta es una forma de confirmar la recursividad apropiada.

Las funciones analíticas y de agregado de la parte recursiva del CTE se aplican al conjunto para el nivel de recursividad actual y no al conjunto para el CTE. Las

funciones como ROW_NUMBER solo funcionan sobre el subconjunto de datos que les pasa el nivel de recursividad actual y no sobre todo el conjunto de datos

pasados a la parte recursiva de la CTE. Para obtener más información, vea el ejemplo K. Usar funciones analíticas en una CTE recursiva que sigue.

Ejemplos

A.Crear una expresión de tabla común simpleEn el siguiente ejemplo se muestra el número total de pedidos de venta por año para cada representante de ventas en Adventure Works Cycles.

-- Define the CTE expression name and column list.

WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)

AS

-- Define the CTE query.

Page 46: Microsoft SELECT Transact SQL 2014

B.Usar una expresión de tabla común para limitar recuentos y promedios de informesEn el siguiente ejemplo se muestra el número medio de pedidos de venta correspondiente a todos los años para los representantes de ventas.

C.Usar varias definiciones de CTE en una sola consulta

En el ejemplo siguiente se muestra cómo definir más de una CTE en una sola consulta. Observe que se usa una coma para separar las definiciones de consulta CTE. La

función FORMAT, utilizada para mostrar las cantidades de moneda en un formato de moneda, está disponible en SQL Server 2012 y versiones posteriores.

A continuación se muestra un conjunto de resultados parcial.

D.Usar una expresión de tabla común recursiva para mostrar varios niveles de recursividad

(

SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear

FROM Sales.SalesOrderHeader

WHERE SalesPersonID IS NOT NULL

)

-- Define the outer query referencing the CTE name.

SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear

FROM Sales_CTE

GROUP BY SalesYear, SalesPersonID

ORDER BY SalesPersonID, SalesYear;

GO

WITH Sales_CTE (SalesPersonID, NumberOfOrders)

AS

(

SELECT SalesPersonID, COUNT(*)

FROM Sales.SalesOrderHeader

WHERE SalesPersonID IS NOT NULL

GROUP BY SalesPersonID

)

SELECT AVG(NumberOfOrders) AS "Average Sales Per Person"

FROM Sales_CTE;

GO

WITH Sales_CTE (SalesPersonID, TotalSales, SalesYear)

AS

-- Define the first CTE query.

(

SELECT SalesPersonID, SUM(TotalDue) AS TotalSales, YEAR(OrderDate) AS SalesYear

FROM Sales.SalesOrderHeader

WHERE SalesPersonID IS NOT NULL

GROUP BY SalesPersonID, YEAR(OrderDate)

)

, -- Use a comma to separate multiple CTE definitions.

-- Define the second CTE query, which returns sales quota data by year for each sales person.

Sales_Quota_CTE (BusinessEntityID, SalesQuota, SalesQuotaYear)

AS

(

SELECT BusinessEntityID, SUM(SalesQuota)AS SalesQuota, YEAR(QuotaDate) AS SalesQuotaYear

FROM Sales.SalesPersonQuotaHistory

GROUP BY BusinessEntityID, YEAR(QuotaDate)

)

-- Define the outer query by referencing columns from both CTEs.

SELECT SalesPersonID

, SalesYear

, FORMAT(TotalSales,'C','en-us') AS TotalSales

, SalesQuotaYear

, FORMAT (SalesQuota,'C','en-us') AS SalesQuota

, FORMAT (TotalSales -SalesQuota, 'C','en-us') AS Amt_Above_or_Below_Quota

FROM Sales_CTE

JOIN Sales_Quota_CTE ON Sales_Quota_CTE.BusinessEntityID = Sales_CTE.SalesPersonID

AND Sales_CTE.SalesYear = Sales_Quota_CTE.SalesQuotaYear

ORDER BY SalesPersonID, SalesYear;

GO

SalesPersonID SalesYear TotalSales SalesQuotaYear SalesQuota Amt_Above_or_Below_Quota

------------- --------- ----------- -------------- ---------- ----------------------------------

274 2005 $32,567.92 2005 $35,000.00 ($2,432.08)

274 2006 $406,620.07 2006 $455,000.00 ($48,379.93)

274 2007 $515,622.91 2007 $544,000.00 ($28,377.09)

274 2008 $281,123.55 2008 $271,000.00 $10,123.55

Page 47: Microsoft SELECT Transact SQL 2014

En el ejemplo siguiente se muestra la lista jerárquica de los directivos y de los empleados que tienen a su cargo. En el ejemplo se empieza creando y rellenando la

tabla dbo.MyEmployees.

E.Usar una expresión de tabla común recursiva para mostrar dos niveles de recursividadEn el ejemplo siguiente se muestran los directivos y los empleados que tienen a su cargo. El número de niveles devueltos está limitado a dos.

F.Usar una expresión de tabla común recursiva para mostrar una lista jerárquicaEl ejemplo siguiente, que está basado en el ejemplo D, agrega los nombres del directivo y de los empleados, y sus cargos respectivos. La jerarquía de directivos yempleados se resalta más mediante la aplicación de sangrías a cada nivel.

-- Create an Employee table.

CREATE TABLE dbo.MyEmployees

(

EmployeeID smallint NOT NULL,

FirstName nvarchar(30) NOT NULL,

LastName nvarchar(40) NOT NULL,

Title nvarchar(50) NOT NULL,

DeptID smallint NOT NULL,

ManagerID int NULL,

CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC)

);

-- Populate the table with values.

INSERT INTO dbo.MyEmployees VALUES

 (1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL),(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)

,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)

,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)

,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)

,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)

,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)

,(16, N'David',N'Bradley', N'Marketing Manager', 4, 273)

,(23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);

USE AdventureWorks2012;

GO

WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS

(

SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel

FROM dbo.MyEmployees

WHERE ManagerID IS NULL

UNION ALL

SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1

FROM dbo.MyEmployees AS e

INNER JOIN DirectReports AS d

ON e.ManagerID = d.EmployeeID

)

SELECT ManagerID, EmployeeID, Title, EmployeeLevel

FROM DirectReports

ORDER BY ManagerID;

GO

USE AdventureWorks2012;

GO

WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS

(

SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel

FROM dbo.MyEmployees

WHERE ManagerID IS NULL

UNION ALL

SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1

FROM dbo.MyEmployees AS e

INNER JOIN DirectReports AS d

ON e.ManagerID = d.EmployeeID

)

SELECT ManagerID, EmployeeID, Title, EmployeeLevel

FROM DirectReports

WHERE EmployeeLevel <= 2 ;

GO

USE AdventureWorks2012;

GO

WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)

AS (SELECT CONVERT(varchar(255), e.FirstName + ' ' + e.LastName),

e.Title,

e.EmployeeID,

Page 48: Microsoft SELECT Transact SQL 2014

G.Usar MAXRECURSION para cancelar una instrucciónMAXRECURSION se puede utilizar para impedir que una CTE recursiva con formato incorrecto entre en un bucle infinito. En el ejemplo siguiente se crea un bucle infinito

intencionadamente y se utiliza la sugerencia MAXRECURSION para limitar el número de niveles de recursividad a dos.

Después de corregir el error de código, ya no se requiere MAXRECURSION. En el siguiente ejemplo se muestra el código corregido.

H.Usar una expresión de tabla común para recorrer selectivamente y paso a paso una relación recursiva en una instrucción SELECTEn el ejemplo siguiente se muestra la jerarquía de ensamblados y componentes de producto necesarios para fabricar la bicicleta correspondiente aProductAssemblyID = 800.

1,

CONVERT(varchar(255), e.FirstName + ' ' + e.LastName)

FROM dbo.MyEmployees AS e

WHERE e.ManagerID IS NULL

UNION ALL

SELECT CONVERT(varchar(255), REPLICATE ('| ' , EmployeeLevel) +

e.FirstName + ' ' + e.LastName),

e.Title,

e.EmployeeID,

EmployeeLevel + 1,

CONVERT (varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' +

LastName)

FROM dbo.MyEmployees AS e

JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID

)

SELECT EmployeeID, Name, Title, EmployeeLevel

FROM DirectReports

ORDER BY Sort;

GO

USE AdventureWorks2012;

GO

--Creates an infinite loop

WITH cte (EmployeeID, ManagerID, Title) as

(

SELECT EmployeeID, ManagerID, Title

FROM dbo.MyEmployees

WHERE ManagerID IS NOT NULL

UNION ALL

SELECT cte.EmployeeID, cte.ManagerID, cte.Title

FROM cte

JOIN dbo.MyEmployees AS e

ON cte.ManagerID = e.EmployeeID

)

--Uses MAXRECURSION to limit the recursive levels to 2

SELECT EmployeeID, ManagerID, Title

FROM cte

OPTION (MAXRECURSION 2);

GO

USE AdventureWorks2012;

GO

WITH cte (EmployeeID, ManagerID, Title)

AS

(

SELECT EmployeeID, ManagerID, Title

FROM dbo.MyEmployees

WHERE ManagerID IS NOT NULL

UNION ALL

SELECT e.EmployeeID, e.ManagerID, e.Title

FROM dbo.MyEmployees AS e

JOIN cte ON e.ManagerID = cte.EmployeeID

)

SELECT EmployeeID, ManagerID, Title

FROM cte;

GO

USE AdventureWorks2012;

GO

WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS

(

SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,

b.EndDate, 0 AS ComponentLevel

FROM Production.BillOfMaterials AS b

WHERE b.ProductAssemblyID = 800

AND b.EndDate IS NULL

UNION ALL

SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,

Page 49: Microsoft SELECT Transact SQL 2014

I.Usar una CTE recursiva en una instrucción UPDATEEn el siguiente ejemplo se actualiza el valor de PerAssemblyQty para todos los componentes que se utilizan para fabricar el producto 'Road-550-W Yellow, 44'

(ProductAssemblyID800). La expresión de tabla común devuelve una lista jerárquica de los elementos que se utilizan para fabricar ProductAssemblyID 800 y los

componentes que se utilizan para crear esos elementos, etc. Solo se modifican las filas devueltas por la expresión de tabla común.

J.Usar varios miembros no recursivos y recursivos

En el ejemplo siguiente se utilizan varios miembros no recursivos y recursivos para devolver todos los antecesores de una persona especificada. Se crea una tabla y se

insertan valores en ella para establecer la genealogía familiar devuelta por la CTE recursiva.

bom.EndDate, ComponentLevel + 1

FROM Production.BillOfMaterials AS bom

INNER JOIN Parts AS p

ON bom.ProductAssemblyID = p.ComponentID

AND bom.EndDate IS NULL

)

SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate,

ComponentLevel

FROM Parts AS p

INNER JOIN Production.Product AS pr

ON p.ComponentID = pr.ProductID

ORDER BY ComponentLevel, AssemblyID, ComponentID;

GO

USE AdventureWorks2012;

GO

WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS

(

SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,

b.EndDate, 0 AS ComponentLevel

FROM Production.BillOfMaterials AS b

WHERE b.ProductAssemblyID = 800

AND b.EndDate IS NULL

UNION ALL

SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,

bom.EndDate, ComponentLevel + 1

FROM Production.BillOfMaterials AS bom

INNER JOIN Parts AS p

ON bom.ProductAssemblyID = p.ComponentID

AND bom.EndDate IS NULL

)

UPDATE Production.BillOfMaterials

SET PerAssemblyQty = c.PerAssemblyQty * 2

FROM Production.BillOfMaterials AS c

JOIN Parts AS d ON c.ProductAssemblyID = d.AssemblyID

WHERE d.ComponentLevel = 0;

-- Genealogy table

IF OBJECT_ID('dbo.Person','U') IS NOT NULL DROP TABLE dbo.Person;

GO

CREATE TABLE dbo.Person(ID int, Name varchar(30), Mother int, Father int);

GO

INSERT dbo.Person

VALUES(1, 'Sue', NULL, NULL)

,(2, 'Ed', NULL, NULL)

,(3, 'Emma', 1, 2)

,(4, 'Jack', 1, 2)

,(5, 'Jane', NULL, NULL)

,(6, 'Bonnie', 5, 4)

,(7, 'Bill', 5, 4);

GO

-- Create the recursive CTE to find all of Bonnie's ancestors.

WITH Generation (ID) AS

(

-- First anchor member returns Bonnie's mother.

SELECT Mother

FROM dbo.Person

WHERE Name = 'Bonnie'

UNION

-- Second anchor member returns Bonnie's father.

SELECT Father

FROM dbo.Person

WHERE Name = 'Bonnie'

UNION ALL

-- First recursive member returns male ancestors of the previous generation.

SELECT Person.Father

FROM Generation, Person

WHERE Generation.ID=Person.ID

UNION ALL

-- Second recursive member returns female ancestors of the previous generation.

SELECT Person.Mother

Page 50: Microsoft SELECT Transact SQL 2014

K.Utilizar funciones analíticas en una CTE recursivaEn el siguiente ejemplo se muestra un error que puede producirse al utilizar una función analítica o de agregado en la parte recursiva de una CTE.

Los siguientes resultados son los esperados para la consulta.

Los siguientes resultados son los resultados reales de la consulta.

N devuelve 1 para cada paso de la parte recursiva del CTE, porque solo el subconjunto de datos para ese nivel de recursividad se pasa a ROWNUMBER. Por cada iteraciónde la parte recursiva de la consulta solo se pasa una fila a ROWNUMBER.

Vea también

FROM Generation, dbo.Person

WHERE Generation.ID=Person.ID

)

SELECT Person.ID, Person.Name, Person.Mother, Person.Father

FROM Generation, dbo.Person

WHERE Generation.ID = Person.ID;

GO

DECLARE @t1 TABLE (itmID int, itmIDComp int);

INSERT @t1 VALUES (1,10), (2,10);

DECLARE @t2 TABLE (itmID int, itmIDComp int);

INSERT @t2 VALUES (3,10), (4,10);

WITH vw AS

(

SELECT itmIDComp, itmID

FROM @t1

UNION ALL

SELECT itmIDComp, itmID

FROM @t2

)

,r AS

(

SELECT t.itmID AS itmIDComp

, NULL AS itmID

,CAST(0 AS bigint) AS N

,1 AS Lvl

FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS t (itmID)

UNION ALL

SELECT t.itmIDComp

, t.itmID

, ROW_NUMBER() OVER(PARTITION BY t.itmIDComp ORDER BY t.itmIDComp, t.itmID) AS N

, Lvl + 1

FROM r

JOIN vw AS t ON t.itmID = r.itmIDComp

)

SELECT Lvl, N FROM r;

Lvl N

1 0

1 0

1 0

1 0

2 4

2 3

2 2

2 1

Lvl N

1 0

1 0

1 0

1 0

2 1

2 1

2 1

2 1

Page 52: Microsoft SELECT Transact SQL 2014

SELECT ﴾cláusula de Transact‐SQL﴿

Especifica las columnas que la consulta debe devolver.

Se aplica a: SQL Server (SQL Server 2008 a versión actual﴿, Windows Azure SQL Database ﴾Versión inicial a versión actual).

Convenciones de sintaxis de Transact-SQL

Sintaxis

Argumentos

ALL

Especifica que el conjunto de resultados puede incluir filas duplicadas. ALL es el valor predeterminado.

DISTINCT

Especifica que el conjunto de resultados solo puede incluir filas únicas. Los valores NULL se consideran iguales desde el punto de vista de la palabra clave

DISTINCT.

TOP (expression ) [ PERCENT ] [ WITH TIES ]

Indica que el conjunto de resultados de la consulta solamente devolverá un primer conjunto o porcentaje de filas especificado. expression puede ser un número oun porcentaje de las filas.

Por compatibilidad con versiones anteriores, se permite el uso de TOP expression sin paréntesis en las instrucciones SELECT, aunque no se recomienda. Para

obtener más información, vea TOP (Transact-SQL).

< select_list >

Columnas que se seleccionarán para el conjunto de resultados. La lista de selección es una serie de expresiones separadas por comas. El número máximo deexpresiones que se puede especificar en la lista de selección es 4.096.

*

Especifica que se deben devolver todas las columnas de todas las tablas y vistas de la cláusula FROM. Las columnas se devuelven por tabla o vista, tal como se

especifique en la cláusula FROM, en el orden en que se encuentran en la tabla o vista.

table_name | view_name | table_alias.*

Limita el ámbito de * a la tabla o vista especificada.

column_name

Es el nombre de una columna que se va a devolver. Para impedir referencias ambiguas, como sucedería si dos tablas de la cláusula FROM tuvieran columnas connombres duplicados, se debe calificar column_name. Por ejemplo, las tablas SalesOrderHeader y SalesOrderDetail de la base de datos AdventureWorks2012

tienen ambas una columna denominada ModifiedDate. Si se combinan las dos tablas en una consulta, se puede especificar la fecha de modificación de lasentradas SalesOrderDetail en la lista de selección como SalesOrderDetail.ModifiedDate.

expression

Es una constante, una función o una combinación de nombres de columna, constantes y funciones conectados mediante un operador, varios operadores o unasubconsulta.

$IDENTITY

Devuelve la columna de identidad. Para obtener más información, vea IDENTITY (propiedad de Transact-SQL), ALTER TABLE (Transact-SQL) y CREATE TABLE (SQL

Server).

Si más de una tabla de la cláusula FROM contiene una columna con la propiedad IDENTITY, se debe calificar $IDENTITY con el nombre de tabla específico; porejemplo, T1.$IDENTITY.

$ROWGUID

Devuelve la columna GUID de fila.

Si más de una tabla de la cláusula FROM tiene la propiedad ROWGUIDCOL, se debe calificar $ROWGUID con el nombre de tabla específico; por ejemplo,

SQL Server 2014

SELECT [ ALL | DISTINCT ]

[ TOP ( expression ) [ PERCENT ] [ WITH TIES ] ]

<select_list>

<select_list> ::=

{

*

| { table_name | view_name | table_alias }.*

| {

[ { table_name | view_name | table_alias }. ]

{ column_name | $IDENTITY | $ROWGUID }

| udt_column_name [ { . | :: } { { property_name | field_name }

| method_name ( argument [ ,...n] ) } ]

| expression

[ [ AS ] column_alias ]

}

| column_alias = expression

} [ ,...n ]

Page 53: Microsoft SELECT Transact SQL 2014

T1.$ROWGUID.

udt_column_name

Es el nombre de la columna que tiene un tipo CLR (Common Language Runtime) definido por el usuario que se va a devolver.

Nota

SQL Server Management Studio devuelve los valores de los tipos definidos por el usuario en representación binaria. Para devolver los valores de los tipos

definidos por el usuario en formato XML o de cadena, use CAST o CONVERT.

{ . | :: }

Especifica un método, una propiedad o un campo de un tipo definido por el usuario CLR. Use . para métodos, propiedades o campos de instancia ﴾noestáticos﴿. Use :: para métodos, propiedades o campos estáticos. Para invocar un método, una propiedad o un campo de un tipo definido por el usuario CLR,debe disponer de permiso EXECUTE en el tipo.

property_name

Es una propiedad pública de udt_column_name.

field_name

Es un miembro de datos público de udt_column_name.

method_name

Es un método público udt_column_name que utiliza uno o varios argumentos. method_name no puede ser un método mutador.

En el ejemplo siguiente se seleccionan los valores de la columna Location, definida como de tipo point, de la tabla Cities, mediante la invocación de unmétodo del tipo denominado Distance:

column_ alias

Es un nombre alternativo que se utiliza para reemplazar el nombre de la columna en el conjunto de resultados de la consulta. Por ejemplo, se puede especificar

un alias como Quantity, Quantity to Date o Qty para una columna denominada quantity.

Los alias se emplean también para especificar nombres para los resultados de expresiones; por ejemplo:

USE AdventureWorks2012;

GO

SELECT AVG(UnitPrice) AS [Average Price]

FROM Sales.SalesOrderDetail;

column_alias se puede utilizar en una cláusula ORDER BY. Sin embargo, no puede utilizarse en una cláusula WHERE, GROUP BY o HAVING. Si la expresión de laconsulta forma parte de una instrucción DECLARE CURSOR, en la cláusula FOR UPDATE no se puede utilizar column_alias.

Comentarios

La longitud de los datos devueltos para las columnas text o ntext incluidas en la lista de selección se establece en el valor menor de los siguientes: el tamaño real dela columna text, la configuración predeterminada de TEXTSIZE para la sesión o el límite de la aplicación codificado de forma rígida. Para cambiar la longitud del texto

devuelto de la sesión, utilice la instrucción SET. De forma predeterminada, la longitud máxima de los datos de texto que se devuelven con una instrucción SELECT es de4.000 bytes.

El Motor de base de datos de SQL Server provoca la excepción 511 y revierte la instrucción que se está ejecutando en ese momento si se produce alguno de estoscomportamientos:

La instrucción SELECT produce una fila de resultados o una fila de la tabla de trabajo intermedia que supera los 8.060 bytes.

La instrucción DELETE, INSERT o UPDATE intenta realizar una acción en una fila que supera los 8.060 bytes.

Se produce un error si no se proporciona un nombre a una columna creada con una instrucción SELECT INTO o CREATE VIEW.

Vea también

ReferenciaEjemplos de SELECT (Transact-SQL)

Expresiones (Transact-SQL)

SELECT (Transact-SQL)

CREATE TABLE dbo.Cities (

Name varchar(20),

State varchar(20),

Location point );

GO

DECLARE @p point (32, 23), @distance float;

GO

SELECT Location.Distance (@p)

FROM Cities;

Page 54: Microsoft SELECT Transact SQL 2014

© 2014 Microsoft

Page 55: Microsoft SELECT Transact SQL 2014

INTO ﴾cláusula de Transact‐SQL﴿

SELECT...INTO crea una nueva tabla en el grupo de archivos predeterminado e inserta las filas resultantes de la consulta en ella. Para ver la sintaxis completa de SELECT,

vea SELECT (Transact-SQL).

Se aplica a: SQL Server (SQL Server 2008 a versión actual﴿, Windows Azure SQL Database ﴾Versión inicial a versión actual).

Convenciones de sintaxis de Transact-SQL

Sintaxis

Argumentos

new_table

Especifica el nombre de una nueva tabla que se va a crear en función de las columnas de la lista de selección y de las filas elegidas desde el origen de datos.

El formato de new_table se determina mediante la evaluación de las expresiones de la lista de selección. Las columnas de new_table se crean en el orden que

especifica la lista de selección. Cada columna de new_table tiene el mismo nombre, tipo de datos, nulabilidad y valor que la expresión correspondiente de la listade selección. La propiedad IDENTITY de una columna se transfiere excepto bajo las condiciones definidas en "Trabajar con columnas de identidad" en la secciónComentarios.

Para crear la tabla en otra base de datos en la misma instancia de SQL Server, especifique new_table como un nombre completo con formato

database.schema.table_name.

No puede crear new_table en un servidor remoto; sin embargo, puede rellenar new_table desde un origen de datos remoto. Para crear new_table a partir de una

tabla de origen remota, especifique la tabla de origen utilizando un nombre con cuatro partes con el formato linked_server.catalog.schema.object en la cláusulaFROM de la instrucción SELECT. También puede utilizar la función OPENQUERY o la función OPENDATASOURCE en la cláusula FROM para especificar el origen dedatos remoto.

Tipos de datos

El atributo FILESTREAM no transfiere a la nueva tabla. Los BLOB FILESTREAM se copian y se almacenan en la nueva tabla como BLOB varbinary(max). Sin el atributo

FILESTREAM, el tipo de datos varbinary(max) tiene una limitación de 2 GB. Si un FILESTREAM BLOB supera este valor, se produce el error 7119 y se detiene la

instrucción.

Cuando se selecciona una columna de identidad existente en una nueva tabla, la nueva columna hereda la propiedad IDENTITY, a menos que se cumpla una de las

siguientes condiciones:

La instrucción SELECT contiene una combinación.

Se han combinado varias instrucciones SELECT con UNION.

La columna de identidad aparece más de una vez en la lista de selección.

La columna de identidad forma parte de una expresión.

La columna de identidad es de un origen de datos remoto.

Si se cumple alguna de estas condiciones, la columna se crea como NOT NULL en lugar de heredar la propiedad IDENTITY. Si una columna de identidad se requiere en

la nueva tabla pero este tipo de columna no está disponible o desea un valor de inicialización o de incremento diferente de la columna de identidad de origen, definala columna en la lista de selección utilizando la función IDENTITY. Vea "Crear una columna de identidad utilizando la función IDENTITY" en la sección Ejemplos siguiente.

Limitaciones y restricciones

No puede especificar una variable de tabla o parámetro con valores de tabla como la nueva tabla.

No puede utilizar SELECT…INTO para crear una tabla con particiones, incluso si la partición se realiza sobre la tabla de origen. SELECT...INTO no usa el esquema de

partición de la tabla de origen; en su lugar, la nueva tabla se crea en el grupo de archivos predeterminado. Para insertar filas en una tabla con particiones, primero

debe crearse la tabla con particiones y, a continuación, utilizar la instrucción INSERT INTO...SELECT FROM.

Los índices, restricciones y desencadenadores definidos en la tabla de origen no se transfieren a la nueva tabla, ni se pueden especificar en la instrucción SELECT...INTO.Si se requieren estos objetos, puede crearlos después de ejecutar la instrucción SELECT...INTO.

Especificar una cláusula ORDER BY no garantiza que las filas se inserten en el orden especificado.

Cuando se incluye una columna dispersa en la lista de selección, la propiedad de la columna dispersa no se transfiere a la columna de la nueva tabla. Si esta propiedad

SQL Server 2014

[ INTO new_table ]

Page 56: Microsoft SELECT Transact SQL 2014

es necesaria en la nueva tabla, modifique la definición de columna después de ejecutar la instrucción SELECT...INTO para que incluya esta propiedad.

Cuando se incluye una columna calculada en la lista de selección, la columna correspondiente de la nueva tabla no es una columna calculada. Los valores de la nueva

columna son los que se calcularon en el momento en que se ejecutó SELECT...INTO.

Comportamiento del registro

La cantidad de registro para SELECT...INTO depende del modelo de recuperación en vigor para la base de datos. En el modelo de recuperación simple o en eloptimizado para cargas masivas de registros, las operaciones masivas se registran mínimamente. Con registro mínimo, utilizar la instrucción SELECT… INTO puede sermás eficaz que crear una tabla y rellenarla con una instrucción INSERT. Para obtener más información, vea El registro de transacciones (SQL Server).

Permissions

Requiere el permiso CREATE TABLE en la base de datos de destino.

Ejemplos

A.Crear una tabla especificando columnas de varios orígenesEn el ejemplo siguiente se crea la tabla dbo.EmployeeAddresses en la base de datos AdventureWorks2012 seleccionando siete columnas de varias tablas relacionadas

con empleados y direcciones.

B.Insertar las filas utilizando el registro mínimoEl ejemplo siguiente crea la tabla dbo.NewProducts e inserta filas de la tabla Production.Product. El ejemplo supone que el modelo de recuperación de la base dedatos AdventureWorks2012 está establecido en FULL. Para asegurarse de que se utiliza el registro mínimo, el modelo de recuperación de la base de datosAdventureWorks2012 se establece en BULK_LOGGED antes de que las filas se inserten y se restablece en FULL después de la instrucción SELECT...INTO. De esta manera

se asegura de que la instrucción SELECT...INTO use el espacio mínimo en el registro de transacciones y funcione eficazmente.

C.Crear una columna de identidad utilizando la función IDENTITYEn el ejemplo siguiente se utiliza la función IDENTITY para crear una columna de identidad en la nueva tabla Person.USAddress de la base de datos

AdventureWorks2012. Se requiere esto porque la instrucción SELECT que define la tabla contiene una unión, que hace que la propiedad IDENTITY no transfiera a lanueva tabla. Tenga en cuenta que los valores de inicialización e incremento especificados en la función IDENTITY son diferentes de los de la columna AddressID de la

tabla de origen Person.Address.

SELECT c.FirstName, c.LastName, e.JobTitle, a.AddressLine1, a.City,

sp.Name AS [State/Province], a.PostalCode

INTO dbo.EmployeeAddresses

FROM Person.Person AS c

JOIN HumanResources.Employee AS e

ON e.BusinessEntityID = c.BusinessEntityID

JOIN Person.BusinessEntityAddress AS bea

ON e.BusinessEntityID = bea.BusinessEntityID

JOIN Person.Address AS a

ON bea.AddressID = a.AddressID

JOIN Person.StateProvince as sp

ON sp.StateProvinceID = a.StateProvinceID;

GO

IF OBJECT_ID('dbo.NewProducts', 'U') IS NOT NULL

DROP TABLE dbo.NewProducts;

GO

ALTER DATABASE AdventureWorks2012 SET RECOVERY BULK_LOGGED;

GO

SELECT * INTO dbo.NewProducts

FROM Production.Product

WHERE ListPrice > $25

AND ListPrice < $100;

GO

ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL;

GO

IF OBJECT_ID ('Person.USAddress') IS NOT NULL

DROP TABLE Person.USAddress;

GO

-- Determine the IDENTITY status of the source column AddressID.

SELECT OBJECT_NAME(object_id) AS TableName, name AS column_name, is_identity, seed_value, increment_value

FROM sys.identity_columns

WHERE name = 'AddressID';

Page 57: Microsoft SELECT Transact SQL 2014

D.Crear una tabla especificando las columnas de un origen de datos remoto

El ejemplo siguiente muestra tres métodos para crear una nueva tabla en el servidor local desde un origen de datos remoto. En el ejemplo se comienza creando un

vínculo al origen de datos remoto. El nombre del servidor vinculado, MyLinkServer, se especifica en la cláusula FROM de la primera instrucción SELECT...INTO y en lafunción OPENQUERY de la segunda instrucción SELECT...INTO. La tercera instrucción SELECT...INTO utiliza la función OPENDATASOURCE, que especifica el origen dedatos remoto directamente en lugar de utilizar el nombre del servidor vinculado.

Se aplica desde SQL Server 2008 hasta SQL Server 2014.

Vea también

ReferenciaSELECT (Transact-SQL)

Ejemplos de SELECT (Transact-SQL)

INSERT (Transact-SQL)

IDENTITY ﴾Función﴿ ﴾Transact‐SQL﴿

© 2014 Microsoft

-- Create a new table with columns from the existing table Person.Address. A new IDENTITY

-- column is created by using the IDENTITY function.

SELECT IDENTITY (int, 100, 5) AS AddressID,

a.AddressLine1, a.City, b.Name AS State, a.PostalCode

INTO Person.USAddress

FROM Person.Address AS a

INNER JOIN Person.StateProvince AS b ON a.StateProvinceID = b.StateProvinceID

WHERE b.CountryRegionCode = N'US';

-- Verify the IDENTITY status of the AddressID columns in both tables.

SELECT OBJECT_NAME(object_id) AS TableName, name AS column_name, is_identity, seed_value, increment_value

FROM sys.identity_columns

WHERE name = 'AddressID';

USE master;

GO

-- Create a link to the remote data source.

-- Specify a valid server name for @datasrc as 'server_name' or 'server_name\instance_name'.

EXEC sp_addlinkedserver @server = N'MyLinkServer',

@srvproduct = N' ',

@provider = N'SQLNCLI',

@datasrc = N'server_name',

@catalog = N'AdventureWorks2012';

GO

USE AdventureWorks2012;

GO

-- Specify the remote data source in the FROM clause using a four-part name

-- in the form linked_server.catalog.schema.object.

SELECT DepartmentID, Name, GroupName, ModifiedDate

INTO dbo.Departments

FROM MyLinkServer.AdventureWorks2012.HumanResources.Department

GO

-- Use the OPENQUERY function to access the remote data source.

SELECT DepartmentID, Name, GroupName, ModifiedDate

INTO dbo.DepartmentsUsingOpenQuery

FROM OPENQUERY(MyLinkServer, 'SELECT *

FROM AdventureWorks2012.HumanResources.Department');

GO

-- Use the OPENDATASOURCE function to specify the remote data source.

-- Specify a valid server name for Data Source using the format server_name or server_name\instance_name.

SELECT DepartmentID, Name, GroupName, ModifiedDate

INTO dbo.DepartmentsUsingOpenDataSource

FROM OPENDATASOURCE('SQLNCLI',

'Data Source=server_name;Integrated Security=SSPI')

.AdventureWorks2012.HumanResources.Department;

GO

Page 58: Microsoft SELECT Transact SQL 2014

FROM (Transact-SQL)

Especifica las tablas, vistas, tablas derivadas y tablas combinadas que se utilizan en las instrucciones DELETE, SELECT y UPDATE en SQL Server 2014. En la instrucciónSELECT, la cláusula FROM es necesaria excepto cuando la lista de selección solo contiene constantes, variables y expresiones aritméticas ﴾sin nombres de columna﴿.

Se aplica a: SQL Server (desde SQL Server 2008 hasta la versión actual﴿, Windows Azure SQL Database ﴾desde la versión inicial hasta la versión actual).

Convenciones de sintaxis de Transact-SQL

Sintaxis

Argumentos

<table_source>

Especifica el origen de una tabla, una vista, una tabla variable o una tabla derivada, con o sin alias, para utilizarlo en la instrucción Transact‐SQL. Se pueden

utilizar hasta 256 orígenes de tabla en una instrucción, aunque el límite varía en función de la memoria disponible y de la complejidad del resto de lasexpresiones de la consulta. Las consultas individuales pueden no admitir un máximo de 256 orígenes de tabla.

Nota

El rendimiento de las consultas se puede ver afectado si se hace referencia a un número elevado de tablas en ellas. El tiempo de compilación y optimizacióntambién se puede ver afectado por factores adicionales. Dichos factores pueden ser la presencia de índices y vistas indizadas en cada <table_source> y eltamaño de <select_list> en la instrucción SELECT.

El orden de los orígenes de tabla después de la palabra clave FROM no afecta al conjunto de resultados devuelto. SQL Server devuelve errores si aparecen

nombres duplicados en la cláusula FROM.

SQL Server 2014

[ FROM { <table_source> } [ ,...n ] ]

<table_source> ::=

{

table_or_view_name [ [ AS ] table_alias ] [ <tablesample_clause> ]

[ WITH ( < table_hint > [ [ , ]...n ] ) ]

| rowset_function [ [ AS ] table_alias ]

[ ( bulk_column_alias [ ,...n ] ) ]

| user_defined_function [ [ AS ] table_alias ] ]

| OPENXML <openxml_clause>

| derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]

| <joined_table>

| <pivoted_table>

| <unpivoted_table>

| @variable [ [ AS ] table_alias ]

| @variable.function_call ( expression [ ,...n ] ) [ [ AS ] table_alias ] [ (column_alias [ ,...n ] ) ]

}

<tablesample_clause> ::=

TABLESAMPLE [SYSTEM] ( sample_number [ PERCENT | ROWS ] )

[ REPEATABLE ( repeat_seed ) ]

<joined_table> ::=

{

<table_source> <join_type> <table_source> ON <search_condition>

| <table_source> CROSS JOIN <table_source>

| left_table_source { CROSS | OUTER } APPLY right_table_source

| [ ( ] <joined_table> [ ) ]

}

<join_type> ::=

[ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } [ <join_hint> ] ]

JOIN

<pivoted_table> ::=

table_source PIVOT <pivot_clause> [ AS ] table_alias

<pivot_clause> ::=

( aggregate_function ( value_column [ [ , ]...n ])

FOR pivot_column

IN ( <column_list> )

)

<unpivoted_table> ::=

table_source UNPIVOT <unpivot_clause> [ AS ] table_alias

<unpivot_clause> ::=

( value_column FOR pivot_column IN ( <column_list> ) )

<column_list> ::=

column_name [ ,...n ]

Page 59: Microsoft SELECT Transact SQL 2014

table_or_view_name

Es el nombre de una tabla o una vista.

Si la tabla o la vista existen en otra base de datos de la misma instancia de SQL Server, utilice un nombre completo con el formato database.schema.object_name.

Si la tabla o la vista existen fuera de la instancia de SQL Serverl, utilice un nombre de cuatro partes con el formato linked_server.catalog.schema.object. Para

obtener más información, vea sp_addlinkedserver (Transact-SQL). El nombre de cuatro partes de la tabla o la vista creado con la función OPENDATASOURCE

como la parte de servidor del nombre también se puede utilizar para especificar el origen de tabla remoto. Cuando se especifica OPENDATASOURCE, es posible

que database_name y schema_name no se puedan aplicar a todos los orígenes de datos y dependan de las funciones del proveedor OLE DB que tiene acceso alobjeto remoto.

[AS] table_alias

Es un alias para table_source que se puede utilizar por comodidad o para distinguir una tabla o una vista en una autocombinación o una subconsulta. El alias

suele ser un nombre de tabla abreviado que se utiliza para hacer referencia a columnas específicas de las tablas en una combinación. Si el mismo nombre de

columna existe en más de una tabla en una combinación, SQL Server requiere que el nombre de columna sea calificado mediante un nombre de tabla, unnombre de vista o un alias. No se puede utilizar el nombre de la tabla si se ha definido un alias.

Si se utiliza una tabla derivada, una función de conjuntos de filas o con valores de tabla, o una cláusula de operador ﴾como PIVOT o UNPIVOT﴿, el parámetrotable_alias requerido al final de la cláusula es el nombre de tabla asociado para todas las columnas devueltas, incluidas las columnas de agrupación.

WITH (<sugerencia_de_tabla> )

Especifica que el optimizador de consultas utiliza una estrategia de optimización o bloqueo con esta tabla y para esta instrucción. Para obtener más información,vea Sugerencias de tabla (Transact-SQL).

rowset_function

Se aplica a: SQL Server 2008 a SQL Server 2014.

Especifica una de las funciones de conjuntos de filas, como OPENROWSET, que devuelve un objeto que se puede utilizar en lugar de una referencia de tabla.

Para obtener más información acerca de la lista de funciones de conjuntos de filas, vea Funciones de conjuntos de filas (Transact-SQL).

El uso de las funciones OPENROWSET y OPENQUERY para especificar que un objeto remoto depende de las capacidades del proveedor OLE DB que tiene

acceso al objeto.

bulk_column_alias

Se aplica a: SQL Server 2008 a SQL Server 2014.

Es un alias opcional para sustituir el nombre de una columna en el conjunto de resultados. Los alias de columna se permiten solo en las instrucciones SELECT

que utilizan la función OPENROWSET con la opción BULK. Si utiliza bulk_column_alias, especifique un alias para cada columna de tabla en el mismo orden que las

columnas del archivo.

Nota

Este alias invalida al atributo NAME de los elementos COLUMN de un archivo de formato XML si está presente.

user_defined_function

Especifica una función con valores de tabla.

OPENXML <openxml_clause>

Se aplica a: SQL Server 2008 a SQL Server 2014.

Proporciona una vista de un conjunto de filas en un documento XML. Para obtener más información, vea OPENXML (Transact-SQL).

derived_table

Es una subconsulta que recupera filas de la base de datos. derived_table se utiliza como entrada a la consulta externa.

derived _table puede utilizar la característica de constructor de valores de Transact‐SQL para especificar varias filas. Por ejemplo, SELECT * FROM (VALUES (1,

2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b);. Para obtener más información, vea Constructor con valores de tabla (Transact-SQL).

column_alias

Es un alias opcional para sustituir el nombre de una columna en el conjunto de resultados de la tabla derivada. Incluya un alias de columna para cada columna

de la lista de selección y delimite la lista de alias de columna con paréntesis.

<tablesample_clause>

Especifica que se devuelva un ejemplo de los datos de la tabla. El ejemplo puede ser aproximado. Esta cláusula se puede utilizar en cualquier tabla principal ocombinada de una instrucción SELECT, UPDATE o DELETE. TABLESAMPLE no se puede especificar con vistas.

Nota

Cuando se utiliza TABLESAMPLE en bases de datos que se actualizan a SQL Server, el nivel de compatibilidad de la base de datos se establece en 110 o

superior y no se permite PIVOT en una consulta de expresión de tabla común ﴾CTE﴿ recursiva. Para obtener más información, vea Nivel de compatibilidad de

ALTER DATABASE (Transact-SQL).

SYSTEM

Se trata de un método de muestreo dependiente de la implementación especificado por los estándares ISO. En SQL Server, es el único método de muestreodisponible y se aplica de forma predeterminada. SYSTEM aplica un método de muestreo basado en páginas en el que se elige un conjunto de páginas aleatoriode la tabla para el ejemplo y todas las filas de dichas páginas se devuelven como el subconjunto de ejemplo.

sample_number

Es una expresión numérica constante exacta o aproximada que representa el porcentaje o el número de filas. Si se especifica con PERCENT, sample_number se

convierte implícitamente a un valor float; en caso contrario, se convierte en bigint. PERCENT es el valor predeterminado.

PERCENT

Especifica que se debe recuperar de la tabla el porcentaje sample_number de filas de la tabla. Si se especifica PERCENT, SQL Server devuelve un valor

aproximado del porcentaje especificado. Si se especifica PERCENT, la expresión sample_number debe dar como resultado un valor comprendido entre 0 y 100.

ROWS

Page 60: Microsoft SELECT Transact SQL 2014

Especifica que se recupere aproximadamente el número sample_number de filas. Si se especifica ROWS, SQL Server devuelve una aproximación del número defilas especificado. Si se especifica ROWS, la expresión sample_number debe ser un valor entero mayor que cero.

REPEATABLE

Indica que el ejemplo seleccionado se puede devolver de nuevo. Si se especifica con el mismo valor de repeat_seed , SQL Server devuelve el mismo subconjunto

de filas siempre que no se hayan realizado cambios en las filas de la tabla. Si se especifica con otro valor de repeat_seed, es probable que SQL Server devuelva

un muestra distinto de filas de la tabla. Se consideran cambios en la tabla las siguientes acciones: insertar, actualizar, eliminar, volver a generar o desfragmentar

índices, y restaurar o adjuntar bases de datos.

repeat_seed

Es una expresión de tipo entero constante utilizada por SQL Server para generar un número aleatorio. repeat_seed es de tipo bigint. Si no se especifica

repeat_seed, SQL Server asigna un valor de forma aleatoria. Para un valor repeat_seed específico, el resultado del muestreo es siempre el mismo si no ha aplicadoningún cambio a la tabla. La expresión repeat_seed debe ser un entero mayor que cero.

<joined_table>

Es un conjunto de resultados producto de dos o más tablas. Para varias combinaciones, utilice paréntesis para cambiar el orden natural de las combinaciones.

<join_type>

Especifica el tipo de operación de combinación.

INNER

Especifica que se devuelvan todos los pares de filas coincidentes. Rechaza las filas no coincidentes de las dos tablas. Si no se especifica ningún tipo decombinación, éste es el valor predeterminado.

FULL [ OUTER ]

Especifica que una fila de la tabla de la derecha o de la izquierda, que no cumpla la condición de combinación, se incluya en el conjunto de resultados y que lascolumnas que correspondan a la otra tabla se establezcan en NULL. De esta forma se agregan más filas a las que se suelen devolver con INNER JOIN.

LEFT [ OUTER ]

Especifica que todas las filas de la tabla izquierda que no cumplan la condición de combinación se incluyan en el conjunto de resultados, con las columnas deresultados de la otra tabla establecidas en NULL, además de todas las filas devueltas por la combinación interna.

RIGHT [OUTER]

Especifica que todas las filas de la tabla derecha que no cumplan la condición de combinación se incluyan en el conjunto de resultados, con las columnas deresultados de la otra tabla establecidas en NULL, además de todas las filas devueltas por la combinación interna.

<join_hint>

Especifica que el optimizador de consultas de SQL Server debe utilizar una sugerencia de combinación o un algoritmo de ejecución por cada combinaciónespecificada en la cláusula FROM de la consulta. Para obtener más información, vea Sugerencias de combinación ﴾Transact‐SQL﴿.

JOIN

Indica que se va a ejecutar la operación de combinación especificada entre los orígenes de tabla o vistas indicados.

ON <search_condition>

Especifica la condición en la que se basa la combinación. La condición puede especificar cualquier predicado, aunque se suelen utilizar columnas y operadoresde comparación; por ejemplo:

Cuando la condición especifique columnas, no será necesario que tengan el mismo nombre o el mismo tipo de datos; sin embargo, si los tipos de datos no sonidénticos, deben ser compatibles o tratarse de tipos que SQL Server pueda convertir implícitamente. Si los tipos de datos no se pueden convertir implícitamente,la condición debe convertir de forma explícita el tipo de datos mediante la función CONVERT.

Puede haber predicados relacionados solamente con una de las tablas combinadas de la cláusula ON. Estos predicados también pueden estar en la cláusulaWHERE de la consulta. Aunque la posición de estos predicados no produce ninguna diferencia en el caso de combinaciones INNER, podría generar un resultadodiferente si estuvieran implicadas las combinaciones OUTER. La razón es que los predicados de la cláusula ON se aplican a la tabla antes de la combinación,mientras la cláusula WHERE se aplica de forma semántica al resultado de la combinación.

Para obtener más información acerca de los predicados y las condiciones de búsqueda, vea Condiciones de búsqueda ﴾Transact‐SQL﴿.

CROSS JOIN

Especifica el producto resultante de dos tablas. Devuelve las mismas filas que se devolverían si no se especificara la cláusula WHERE en una combinación deestilo antiguo distinta del estilo de SQL-92.

left_table_source{ CROSS | OUTER } APPLY right_table_source

Especifica que el argumento right_table_source del operador APPLY se evalúe con respecto a cada fila de left_table_source. Esta funcionalidad es útil siright_table_source contiene una función con valores de tabla que toma los valores de columna de left_table_source como uno de sus argumentos.

Se debe especificar OUTER o CROSS con APPLY. Si se especifica CROSS, no se genera ninguna fila cuando right_table_source se evalúa con respecto a una filaespecificada de left_table_source y se devuelve un conjunto de resultados vacío.

Si se especifica OUTER, se genera una fila para cada fila de left_table_source, incluso si right_table_source se evalúa con respecto a dicha fila y devuelve unconjunto de resultados vacío.

Para obtener más información, vea la sección Comentarios.

left_table_source

Es un origen de tabla según se ha definido en el argumento anterior. Para obtener más información, vea la sección Comentarios.

right_table_source

Es un origen de tabla según se ha definido en el argumento anterior. Para obtener más información, vea la sección Comentarios.

SELECT p.ProductID, v.BusinessEntityID

FROM Production.Product AS p

JOIN Purchasing.ProductVendor AS v

ON (p.ProductID = v.ProductID);

Page 61: Microsoft SELECT Transact SQL 2014

table_source PIVOT <pivot_clause>

Especifica que table_source se dinamiza según pivot_column. table_source es una tabla o expresión de tabla. La salida es una tabla que contiene todas las

columnas de table_source, excepto pivot_column y value_column. Las columnas de table_source, excepto pivot_column y value_column, se denominan columnas de

agrupamiento del operador dinámico.

PIVOT realiza una operación de agrupamiento en la tabla de entrada con respecto a las columnas de agrupamiento y devuelve una fila para cada grupo.Además, el resultado contiene una columna para cada valor especificado en column_list que aparece en el parámetro pivot_column de input_table.

Para obtener más información, vea la sección Comentarios a continuación.

aggregate_function

Es una función de agregado del sistema o definida por el usuario que acepta una o más entradas. La función de agregado no puede variar con respecto a losvalores NULL. Una función de agregado invariable con respecto a los valores NULL no tiene en cuenta los valores NULL del grupo mientras evalúa el valor delagregado.

No se permite la función de agregado del sistema COUNT﴾*﴿.

value_column

Es la columna de valores del operador PIVOT. Si se utiliza con UNPIVOT, value_column no puede ser el nombre de una columna existente en el parámetrotable_source de entrada.

FOR pivot_column

Es la columna dinámica del operador PIVOT. pivot_column debe ser de un tipo que se pueda convertir implícita o explícitamente a nvarchar(). Esta columna no

puede ser image o rowversion.

Si se utiliza UNPIVOT, pivot_column es el nombre de la columna de salida restringida a partir de table_source. No puede haber ninguna columna en table_source

con ese nombre.

IN (column_list )

En la cláusula PIVOT, se incluyen los valores de pivot_column que se van a convertir en los nombres de columnas de la tabla de salida. La lista no puede

especificar ningún nombre de columna que ya exista en el parámetro table_source de entrada que se está dinamizando.

En la cláusula UNPIVOT, se incluyen las columnas de table_source que se va a restringir en una sola pivot_column.

table_alias

Es el nombre de alias de la tabla de salida. pivot_table_alias debe especificarse.

UNPIVOT < unpivot_clause >

Especifica que la tabla de entrada se restringe a partir de varias columnas de column_list en una sola columna denominada pivot_column.

Comentarios

La cláusula FROM admite la sintaxis SQL‐92‐SQL para las tablas combinadas y las tablas derivadas. La sintaxis SQL‐92 proporciona los operadores de combinaciónINNER, LEFT OUTER, RIGHT OUTER, FULL OUTER y CROSS.

En las vistas, tablas derivadas y subconsultas se admiten las operaciones UNION y JOIN dentro de una cláusula FROM.

Una autocombinación es una tabla que se combina consigo misma. Las inserciones o actualizaciones basadas en una autocombinación siguen el orden de la cláusulaFROM.

Puesto que SQL Server considera las estadísticas de cardinalidad y distribución de servidores vinculados que proporcionan estadísticas de distribución de columnas, noes necesaria la sugerencia de combinación REMOTE para exigir la evaluación de una combinación de forma remota. El procesador de consultas de SQL Server

considera las estadísticas remotas y determina si es apropiada una estrategia de combinación remota. La sugerencia de combinación REMOTE es útil para losproveedores que no proporcionan estadísticas de distribución de columnas.

Usar APPLY

Los operandos izquierdo y derecho del operador APPLY son expresiones de tabla. La diferencia principal entre estos operandos es que right_table_source puede utilizar

una función con valores de tabla que tome una columna de left_table_source como uno de los argumentos de la función. left_table_source puede incluir funciones con

valores de tabla, pero no puede contener argumentos que sean columnas de right_table_source.

El operador APPLY funciona del siguiente modo para generar el origen de tabla para la cláusula FROM:

1. Evalúa right_table_source con respecto a cada fila de left_table_source para generar conjuntos de filas.

Los valores en right_table_source dependen de left_table_source. right_table_source se pueden representar aproximadamente de esta manera:

TVF(left_table_source.row), donde TVF es una función con valores de tabla.

2. Combina los conjuntos de resultados generados para cada fila en la evaluación de right_table_source con left_table_source, mediante una operación UNION ALL.

La lista de columnas que genera el resultado del operador APPLY es el conjunto de columnas de left_table_source combinado con la lista de columnas de

right_table_source.

Usar PIVOT y UNPIVOT

pivot_column y value_column son columnas de agrupamiento utilizadas por el operador PIVOT. Para obtener el conjunto de resultados de salida, PIVOT aplica el

siguiente proceso:

1. Realiza una operación GROUP BY en input_table para las columnas de agrupamiento y genera una fila de resultados para cada grupo.

Las columnas de agrupamiento de la fila de salida obtienen los valores de columna correspondientes para dicho grupo en input_table.

2. Genera valores para las columnas de la lista de columnas para cada fila de resultados mediante las siguientes operaciones:

Page 62: Microsoft SELECT Transact SQL 2014

a. Agrupación adicional de las filas generadas en GROUP BY en el paso anterior para pivot_column.

Para cada columna de salida de column_list, se selecciona un subgrupo que cumple la condición:

pivot_column = CONVERT(<data type of pivot_column>, 'output_column')

b. aggregate_function se evalúa con respecto a value_column en este subgrupo y su resultado se devuelve como un valor de output_column correspondiente.

Si el subgrupo está vacío, SQL Server genera un valor NULL para output_column. Si la función de agregado es COUNT y el subgrupo está vacío, sedevuelve cero (0).

Permisos

Requiere los permisos para la instrucción DELETE, SELECT o UPDATE.

Ejemplos

A.Usar una cláusula FROM sencillaEn el siguiente ejemplo se recuperan las columnas TerritoryID y Name de la tabla SalesTerritory de la base de datos de ejemplo AdventureWorks2012.

El conjunto de resultados es el siguiente.

B.Usar las sugerencias del optimizador TABLOCK y HOLDLOCK

En la siguiente transacción parcial se muestra cómo colocar un bloqueo explícito de tabla compartida en Employee y cómo leer el índice. El bloqueo se mantiene

durante toda la transacción.

C.Usar la sintaxis CROSS JOIN de SQL-92

Este ejemplo devuelve el producto resultante de las tablas Employee y Department de la base de datos AdventureWorks2012. Se devuelve la lista de todas las

combinaciones posibles de las filas de BusinessEntityID y todas las filas con el nombre Department .

D.Usar la sintaxis FULL OUTER JOIN de SQL-92

En el siguiente ejemplo se devuelve el nombre del producto y los pedidos de venta correspondientes de la tabla SalesOrderDetail de la base de datos

AdventureWorks2012. Además, se devuelven todos los pedidos de venta que no incluyen ningún producto en la tabla Product y todos los productos con un pedido de

venta distinto del especificado en la tabla Product.

SELECT TerritoryID, Name

FROM Sales.SalesTerritory

ORDER BY TerritoryID ;

TerritoryID Name

----------- ------------------------------

1 Northwest

2 Northeast

3 Central

4 Southwest

5 Southeast

6 Canada

7 France

8 Germany

9 Australia

10 United Kingdom

(10 row(s) affected)

BEGIN TRAN

SELECT COUNT(*)

FROM HumanResources.Employee WITH (TABLOCK, HOLDLOCK) ;

SELECT e.BusinessEntityID, d.Name AS Department

FROM HumanResources.Employee AS e

CROSS JOIN HumanResources.Department AS d

ORDER BY e.BusinessEntityID, d.Name ;

-- The OUTER keyword following the FULL keyword is optional.

SELECT p.Name, sod.SalesOrderID

FROM Production.Product AS p

Transact-SQL

Transact-SQL

Transact-SQL

Page 63: Microsoft SELECT Transact SQL 2014

E.Usar la sintaxis LEFT FULL OUTER de SQL-92

Este ejemplo combina dos tablas en ProductID y mantiene las filas no coincidentes de la tabla izquierda. La tabla Product coincide con la tabla SalesOrderDetail en

las columnas ProductID de cada tabla. Todos los productos, ordenados y no ordenados, aparecen en el conjunto de resultados.

F.Usar la sintaxis INNER JOIN de SQL-92

En el siguiente ejemplo se devuelven todos los nombres de productos e identificadores de pedidos de venta.

G.Usar la sintaxis RIGHT OUTER JOIN de SQL-92

Este ejemplo combina dos tablas en TerritoryID y mantiene las filas no coincidentes de la tabla derecha. La tabla SalesTerritory coincide con la tabla SalesPerson

en la columna TerritoryID de cada tabla. Todos los vendedores aparecen en el conjunto de resultados con independencia de que tengan un territorio asignado.

H.Usar las sugerencias de combinación HASH y MERGEEn el siguiente ejemplo se realiza una combinación de tres tablas entre las tablas Product, ProductVendor y Vendor para generar una lista de productos y sus

proveedores. El optimizador de consultas combina Product y ProductVendor (p y pv﴿ mediante una combinación MERGE. A continuación, los resultados de lacombinación MERGE de Product y ProductVendor (p y pv) se combinan mediante HASH con la tabla Vendor para generar (p y pv) y v.

Importante

Después de especificar una sugerencia de combinación, la palabra clave INNER ya no es opcional y se tiene que incluir explícitamente para hacer combinacionesINNER JOIN.

I.Usar una tabla derivada

En el siguiente ejemplo se utiliza una tabla derivada y una instrucción SELECT después de la cláusula FROM para devolver los nombres y apellidos de todos los

empleados y las ciudades en que residen.

FULL OUTER JOIN Sales.SalesOrderDetail AS sod

ON p.ProductID = sod.ProductID

WHERE p.ProductID IS NULL OR sod.ProductID IS NULL

ORDER BY p.Name ;

SELECT p.Name, sod.SalesOrderID

FROM Production.Product AS p

LEFT OUTER JOIN Sales.SalesOrderDetail AS sod

ON p.ProductID = sod.ProductID

ORDER BY p.Name ;

-- By default, SQL Server performs an INNER JOIN if only the JOIN

-- keyword is specified.

SELECT p.Name, sod.SalesOrderID

FROM Production.Product AS p

INNER JOIN Sales.SalesOrderDetail AS sod

ON p.ProductID = sod.ProductID

ORDER BY p.Name ;

SELECT st.Name AS Territory, sp.BusinessEntityID

FROM Sales.SalesTerritory AS st

RIGHT OUTER JOIN Sales.SalesPerson AS sp

ON st.TerritoryID = sp.TerritoryID ;

SELECT p.Name AS ProductName, v.Name AS VendorName

FROM Production.Product AS p

INNER MERGE JOIN Purchasing.ProductVendor AS pv

ON p.ProductID = pv.ProductID

INNER HASH JOIN Purchasing.Vendor AS v

ON pv.BusinessEntityID = v.BusinessEntityID

ORDER BY p.Name, v.Name ;

SELECT RTRIM(p.FirstName) + ' ' + LTRIM(p.LastName) AS Name, d.City

FROM Person.Person AS p

INNER JOIN HumanResources.Employee e ON p.BusinessEntityID = e.BusinessEntityID

INNER JOIN

(SELECT bea.BusinessEntityID, a.City

FROM Person.Address AS a

INNER JOIN Person.BusinessEntityAddress AS bea

ON a.AddressID = bea.AddressID) AS d

ON p.BusinessEntityID = d.BusinessEntityID

ORDER BY p.LastName, p.FirstName;

Transact-SQL

Transact-SQL

Transact-SQL

Transact-SQL

Transact-SQL

Page 64: Microsoft SELECT Transact SQL 2014

J.Usar TABLESAMPLE para leer datos de un ejemplo de filas de una tabla

En el siguiente ejemplo se utiliza TABLESAMPLE en la cláusula FROM para devolver aproximadamente el 10 por ciento de todas las filas de la tabla Customer.

K.Usar APPLY

En el siguiente ejemplo se da por supuesto que las siguientes tablas con el esquema que se indica existen en la base de datos:

Departments: DeptID, DivisionID, DeptName, DeptMgrID

EmpMgr: MgrID, EmpID

Employees: EmpID, EmpLastName, EmpFirstName, EmpSalary

Se incluye además una función con valores de tabla, GetReports(MgrID), que devuelve la lista de todos los empleados (EmpID, EmpLastName, EmpSalary) que

dependen directa o indirectamente del MgrID especificado.

En el ejemplo se utiliza APPLY para devolver todos los departamentos y todos los empleados de cada departamento. Si un departamento concreto no tiene ningúnempleado, no se devuelve ninguna fila para dicho departamento.

Si desea que la consulta genere filas para los departamentos sin empleados, lo que genera valores NULL para las columnas EmpID, EmpLastName y EmpSalary, utilice

OUTER APPLY.

L.Usar PIVOT y UNPIVOT

En el siguiente ejemplo se devuelve el número de pedidos de compra realizados por los empleados con los identificadores 164, 198, 223, 231 y 233, clasificados en

categorías por identificador de proveedor.

A continuación se muestra un conjunto de resultados parcial:

VendorID Emp1 Emp2 Emp3 Emp4 Emp5

----------------------------------------------------------------

1 4 3 5 4 4

2 4 1 5 5 5

3 4 3 5 4 4

4 4 2 5 5 4

5 5 1 5 5 5

Para anular la dinamización de la tabla, debe dar por supuesto que el conjunto de resultados generado en el ejemplo anterior se almacena como pvt. La consulta seríala siguiente.

SELECT *

FROM Sales.Customer TABLESAMPLE SYSTEM (10 PERCENT) ;

SELECT DeptID, DeptName, DeptMgrID, EmpID, EmpLastName, EmpSalary

FROM Departments d CROSS APPLY dbo.GetReports(d.DeptMgrID) ;

SELECT DeptID, DeptName, DeptMgrID, EmpID, EmpLastName, EmpSalary

FROM Departments d OUTER APPLY dbo.GetReports(d.DeptMgrID) ;

SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5

FROM

(SELECT PurchaseOrderID, EmployeeID, VendorID

FROM Purchasing.PurchaseOrderHeader) AS p

PIVOT

(

COUNT (PurchaseOrderID)

FOR EmployeeID IN

( [250], [251], [256], [257], [260] )

) AS pvt

ORDER BY VendorID;

--Create the table and insert values as portrayed in the previous example.

CREATE TABLE dbo.pvt (VendorID int, Emp1 int, Emp2 int,

Emp3 int, Emp4 int, Emp5 int);

GO

INSERT INTO dbo.pvt VALUES

Transact-SQL

Transact-SQL

Transact-SQL

Page 65: Microsoft SELECT Transact SQL 2014

A continuación se muestra un conjunto de resultados parcial:

VendorID Employee Orders

------------------------------

1 Emp1 4

1 Emp2 3

1 Emp3 5

1 Emp4 4

1 Emp5 4

2 Emp1 4

2 Emp2 1

2 Emp3 5

2 Emp4 5

2 Emp5 5

M.Usar CROSS APPLY

En el ejemplo siguiente se recupera una instantánea de todos los planes de consulta que residen en la memoria caché del plan mediante una consulta a la vista deadministración dinámica sys.dm_exec_cached_plans para recuperar los identificadores de todos los planes de consulta almacenados en la memoria caché. Acontinuación se especifica el operador CROSS APPLY para pasar los identificadores del plan a sys.dm_exec_query_plan. La salida del plan de presentación XML detodos los planes almacenados actualmente en la caché del plan se muestra en la columna query_plan de la tabla devuelta.

Vea también

ReferenciaCONTAINSTABLE (Transact-SQL)

DELETE (Transact-SQL)

FREETEXTTABLE (Transact-SQL)

INSERT (Transact-SQL)

OPENQUERY (Transact-SQL)

OPENROWSET (Transact-SQL)

Operadores (Transact-SQL)

UPDATE (Transact-SQL)

WHERE (Transact-SQL)

© 2014 Microsoft

(1,4,3,5,4,4)

,(2,4,1,5,5,5)

,(3,4,3,5,4,4)

,(4,4,2,5,5,4)

,(5,5,1,5,5,5);

GO

--Unpivot the table.

SELECT VendorID, Employee, Orders

FROM

(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5

FROM dbo.pvt) AS p

UNPIVOT

(Orders FOR Employee IN

(Emp1, Emp2, Emp3, Emp4, Emp5)

)AS unpvt

GO

USE master;

GO

SELECT dbid, object_id, query_plan

FROM sys.dm_exec_cached_plans AS cp

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle);

GO

Page 66: Microsoft SELECT Transact SQL 2014

WHERE (Transact-SQL)

Especifica la condición de búsqueda de las filas devueltas por la consulta.

Se aplica a: SQL Server (SQL Server 2008 a versión actual﴿, Windows Azure SQL Database ﴾Versión inicial a versión actual).

Convenciones de sintaxis de Transact-SQL

Sintaxis

Argumentos

< search_condition >

Define la condición que se debe cumplir para que se devuelvan las filas. No hay límite en cuanto al número de predicados que se puede incluir en una condiciónde búsqueda. Para obtener más información acerca de los predicados y las condiciones de búsqueda, vea Condiciones de búsqueda ﴾Transact‐SQL﴿.

Ejemplos

En los ejemplos siguientes se muestra cómo utilizar algunas condiciones de búsqueda comunes en la cláusula WHERE.

A.Buscar una fila utilizando una igualdad simple

B.Buscar las filas que contienen un valor como una parte de una cadena

C.Buscar filas utilizando un operador de comparación

D.Buscar las filas que cumplen alguna de tres condiciones

E.Buscar las filas que deben cumplir varias condiciones

SQL Server 2014

[ WHERE <search_condition> ]

USE AdventureWorks2012

GO

SELECT ProductID, Name

FROM Production.Product

WHERE Name = 'Blade' ;

GO

SELECT ProductID, Name, Color

FROM Production.Product

WHERE Name LIKE ('%Frame%');

GO

SELECT ProductID, Name

FROM Production.Product

WHERE ProductID <= 12 ;

GO

SELECT ProductID, Name

FROM Production.Product

WHERE ProductID = 2

OR ProductID = 4

OR Name = 'Spokes' ;

GO

SELECT ProductID, Name, Color

FROM Production.Product

WHERE Name LIKE ('%Frame%')

Page 67: Microsoft SELECT Transact SQL 2014

F.Buscar las filas que están en una lista de valores

G.Buscar las filas que tienen un valor comprendido entre dos valores

Vea también

ReferenciaDELETE (Transact-SQL)

Predicados (Transact-SQL)

Condiciones de búsqueda ﴾Transact‐SQL﴿SELECT (Transact-SQL)

UPDATE (Transact-SQL)

MERGE (Transact-SQL)

© 2014 Microsoft

AND Name LIKE ('HL%')

AND Color = 'Red' ;

GO

SELECT ProductID, Name, Color

FROM Production.Product

WHERE Name IN ('Blade', 'Crown Race', 'Spokes');

GO

SELECT ProductID, Name, Color

FROM Production.Product

WHERE ProductID BETWEEN 725 AND 734;

GO

Page 68: Microsoft SELECT Transact SQL 2014

GROUP BY (Transact-SQL)

Agrupa un conjunto de filas seleccionado en un conjunto de filas de resumen de acuerdo con los valores de una o más columnas o expresiones en SQL Server 2014. Se

devuelve una fila para cada grupo. Las funciones de agregado de la lista <select> de la cláusula SELECT proporcionan información de cada grupo en lugar de filasindividuales.

La cláusula GROUP BY tiene una sintaxis que cumple la norma ISO y otra sintaxis que no cumple dicha norma. Solo se puede usar un estilo de sintaxis en cada instrucciónSELECT. Use la sintaxis que cumple la norma ISO para todos los trabajos nuevos. La sintaxis que no cumple dicha norma solo se incluye por motivos de compatibilidad

con versiones anteriores.

En este tema, una cláusula GROUP BY se puede describir como general o como simple:

Una cláusula GROUP BY general incluye los elementos GROUPING SETS, CUBE, ROLLUP, WITH CUBE o WITH ROLLUP.

Una cláusula GROUP BY simple no incluye GROUPING SETS, CUBE, ROLLUP, WITH CUBE ni WITH ROLLUP. GROUP BY ﴾﴿, total general, se considera una cláusulaGROUP BY simple.

Se aplica a: SQL Server (desde SQL Server 2008 hasta la versión actual﴿, Windows Azure SQL Database ﴾desde la versión inicial hasta la versión actual).

Convenciones de sintaxis de Transact-SQL (Transact-SQL)

Sintaxis

SQL Server 2014

ISO-Compliant Syntax

GROUP BY <group by spec>

<group by spec> ::=

<group by item> [ ,...n ]

<group by item> ::=

<simple group by item>

| <rollup spec>

| <cube spec>

| <grouping sets spec>

| <grand total>

<simple group by item> ::=

<column_expression>

<rollup spec> ::=

ROLLUP ( <composite element list> )

<cube spec> ::=

CUBE ( <composite element list> )

<composite element list> ::=

<composite element> [ ,...n ]

<composite element> ::=

<simple group by item>

| ( <simple group by item list> )

<simple group by item list> ::=

<simple group by item> [ ,...n ]

<grouping sets spec> ::=

GROUPING SETS ( <grouping set list> )

<grouping set list> ::=

<grouping set> [ ,...n ]

<grouping set> ::=

<grand total>

| <grouping set item>

| ( <grouping set item list> )

<empty group> ::=

( )

<grouping set item> ::=

<simple group by item>

| <rollup spec>

| <cube spec>

<grouping set item list> ::=

<grouping set item> [ ,...n ]

Page 69: Microsoft SELECT Transact SQL 2014

Argumentos

< column_expression>

Es la expresión en la que se realiza la operación de agrupamiento.

ROLLUP ( )

Genera filas de agregado mediante la cláusula GROUP BY simple, más filas de subtotal o de superagregado, así como una fila de total general.

El número de agrupaciones que se devuelve es igual al número de expresiones de la <lista de elementos compuestos> más uno. Considere, por ejemplo, la

siguiente instrucción:

Se genera una fila con un subtotal para cada combinación única de valores de (a, b, c), (a, b) y (a). También se calcula una fila de total general.

Las columnas se acumulan de derecha a izquierda. El orden de las columnas influye en las agrupaciones de salida de ROLLUP y también puede afectar al númerode filas del conjunto de resultados.

CUBE ( )

Genera filas de agregado mediante la cláusula GROUP BY simple, filas de superagregado mediante la instrucción ROLLUP y filas de tabulación cruzada.

CUBE genera una agrupación para todas las permutaciones de expresiones de la <lista de elementos compuestos>.

El número de agrupaciones generado es igual a ﴾2n﴿, donde n es el número de expresiones de la <lista de elementos compuestos>. Considere, por ejemplo, la

siguiente instrucción:

Se genera una fila para cada combinación única de valores de (a, b, c), (a, b), (a, c), (b, c), (a), (b) y (c), con un subtotal para cada fila y una fila de

total general.

El orden de las columnas no afecta a la salida de CUBE.

GROUPING SETS ( )

Especifica varias agrupaciones de datos en una consulta. Solo se agregan los grupos especificados, en lugar del conjunto completo de agregaciones generado

por CUBE o ROLLUP. Los resultados son equivalentes a usar la instrucción UNION ALL en los grupos especificados. GROUPING SETS puede contener un únicoelemento o una lista de elementos. GROUPING SETS puede especificar agrupaciones equivalentes a las devueltas por ROLLUP o CUBE. La <lista de elementos de

conjunto de agrupación> puede contener ROLLUP o CUBE.

( )

El grupo vacío genera un total.

Sintaxis que no cumple la norma ISO

ALL

Esta característica se quitará en una versión futura de Microsoft SQL Server. Evite utilizar esta característica en nuevos trabajos de desarrollo y tenga previstomodificar las aplicaciones que actualmente la utilizan.Incluye todos los grupos y conjuntos de resultados, incluso aquellos en los que no hay filas que cumplan la

condición de búsqueda especificada en la cláusula WHERE. Cuando se especifica ALL, se devuelven valores NULL para las columnas de resumen de los grupos

que no cumplen la condición de búsqueda. No puede especificar ALL con los operadores CUBE y ROLLUP.

GROUP BY ALL no se admite en consultas que tienen acceso a tablas remotas si también hay una cláusula WHERE en la consulta. GROUP BY ALL generará erroren las columnas que tengan el atributo FILESTREAM.

group_by_expression

Es una expresión según la cual se realiza la agrupación. group_by_expression también se conoce como columna de agrupación. group_by expression puede ser una

columna o una expresión no agregada que haga referencia a una columna devuelta por la cláusula FROM. Un alias de columna que esté definido en la listaSELECT no puede usarse para especificar una columna de agrupamiento.

Nota

Las columnas de tipo text, ntext e image no se pueden utilizar en group_by_expression.

En las cláusulas GROUP BY que no contengan CUBE o ROLLUP, el número de elementos group_by_expression está limitado por los tamaños de columna de GROUPBY, las columnas de agregado y los valores de agregado que participan en la consulta. Este límite procede del límite de 8.060 bytes de la tabla de trabajointermedia que se necesita para contener los resultados intermedios de la consulta. Se permite un máximo de 12 expresiones de agrupamiento cuando se

Non-ISO-Compliant Syntax

[ GROUP BY [ ALL ] group_by_expression [ ,...n ]

[ WITH { CUBE | ROLLUP } ]

]

SELECT a, b, c, SUM ( <expression> )

FROM T

GROUP BY ROLLUP (a,b,c);

SELECT a, b, c, SUM (<expression>)

FROM T

GROUP BY CUBE (a,b,c);

Page 70: Microsoft SELECT Transact SQL 2014

especifica CUBE o ROLLUP.

No se pueden especificar métodos de tipos de datos xml directamente en group_by_expression. En su lugar, haga referencia a una función definida por el usuarioque incluya métodos de tipos de datos xml, o haga referencia a una columna calculada que los utilice.

WITH CUBE

Esta característica se quitará en una versión futura de Microsoft SQL Server. Evite utilizar esta característica en nuevos trabajos de desarrollo y tenga previstomodificar las aplicaciones que actualmente la utilizan. Especifica que, además de las filas que normalmente proporciona GROUP BY, deben incluirse filas deresumen en el conjunto de resultados. Se devuelve una fila de resumen GROUP BY por cada posible combinación de grupo y subgrupo del conjunto deresultados. Utilice la función GROUPING para determinar si los valores NULL del conjunto de resultados son valores de resumen GROUP BY.

El número de filas de resumen del conjunto de resultados se determina mediante el número de columnas que contiene la cláusula GROUP BY. Debido a que

CUBE devuelve todas las combinaciones posibles de grupo y subgrupo, el número de filas es el mismo, independientemente del orden en que se especifiquenlas columnas de agrupamiento.

WITH ROLLUP

Esta característica se quitará en una versión futura de Microsoft SQL Server. Evite utilizar esta característica en nuevos trabajos de desarrollo y tenga previstomodificar las aplicaciones que actualmente la utilizan. Especifica que, además de las filas que normalmente proporciona GROUP BY, deben incluirse filas deresumen en el conjunto de resultados. Los grupos se resumen en un orden jerárquico, desde el nivel inferior del grupo hasta el superior. La jerarquía del grupose determina por el orden en que se especifican las columnas de agrupamiento. Cambiar el orden de las columnas de agrupamiento puede afectar al númerode filas generadas en el conjunto de resultados.

Importante

Cuando se utiliza CUBE o ROLLUP no se admiten agregados Distinct, como AVG (DISTINCT column_name), COUNT (DISTINCT column_name) y SUM (DISTINCT

column_name). Si se utilizan, Motor de base de datos de SQL Server devuelve un mensaje de error y cancela la consulta.

Comentarios

Las expresiones de la cláusula GROUP BY pueden contener columnas de las tablas, de las tablas derivadas o de las vistas de la cláusula FROM. No es necesario que

aparezcan las columnas en la lista de <selección> de la cláusula SELECT.

Deben incluirse en la lista GROUP BY todas las columnas de la tabla o la vista de cualquier expresión no agregada de la lista de <selección>:

Están permitidas las siguientes instrucciones:

No están permitidas las siguientes instrucciones:

Si se incluyen funciones de agregado en la <lista de selección> de la cláusula SELECT, GROUP BY calcula un valor de resumen para cada grupo. Se conocen como

agregados vectoriales.

Las filas que no cumplen las condiciones especificadas en la cláusula WHERE se quitan antes de realizar ninguna operación de agrupación.

La cláusula HAVING se usa junto con la cláusula GROUP BY para filtrar los grupos en el conjunto de resultados.

La cláusula GROUP BY no ordena el conjunto de resultados. En su lugar, use la cláusula ORDER BY para ordenarlo.

Si una columna de agrupamiento contiene varios valores NULL, todos ellos se consideran equivalentes y se colocan en un grupo individual.

No es posible usar GROUP BY con un alias para reemplazar el nombre de una columna en la cláusula AS, a menos que dicho alias sustituya a un nombre de columna enuna tabla derivada de la cláusula FROM.

Los conjuntos de agrupación duplicados de una lista GROUPING SETS no se eliminan. Los conjuntos de agrupación duplicados se pueden generar especificando unaexpresión de columna más de una vez o incluyendo una expresión de columna también generada por una instrucción CUBE o ROLLUP en la lista GROUPING SETS.

Cuando se utiliza ROLLUP, CUBE y GROUPING SETS se admiten agregados Distinct, como AVG (DISTINCT column_name), COUNT (DISTINCT column_name) y SUM

(DISTINCT column_name).

ROLLUP, CUBE y GROUPING SETS no se pueden especificar en una vista indexada.

GROUP BY o HAVING no se pueden usar directamente en columnas de ntext, text o image. Estas columnas se pueden usar como argumentos en funciones que

devuelven un valor de otro tipo de datos, como SUBSTRING() y CAST().

No se pueden especificar métodos de tipos de datos xml directamente en <column_expression>. En su lugar, haga referencia a una función definida por el usuario queincluya métodos de tipos de datos xml o haga referencia a una columna calculada que los utilice.

Limitaciones de GROUP BY para GROUPING SETS, ROLLUP y CUBE

Limitaciones de la sintaxis

Los operadores GROUPING SETS no se pueden usar en la cláusula GROUP BY a menos que formen parte de una lista GROUPING SETS. Por ejemplo, no se admite

SELECT ColumnA, ColumnB FROM T GROUP BY ColumnA, ColumnB;

SELECT ColumnA + ColumnB FROM T GROUP BY ColumnA, ColumnB;

SELECT ColumnA + ColumnB FROM T GROUP BY ColumnA + ColumnB;

SELECT ColumnA + ColumnB + constant FROM T GROUP BY ColumnA, ColumnB;

SELECT ColumnA, ColumnB FROM T GROUP BY ColumnA + ColumnB;

SELECT ColumnA + constant + ColumnB FROM T GROUP BY ColumnA + ColumnB;

Page 71: Microsoft SELECT Transact SQL 2014

GROUP BY C1, (C2,..., Cn) pero sí GROUP BY GROUPING SETS (C1, (C2, ..., Cn)).

Los operadores GROUPING SETS no se pueden usar dentro de GROUPING SETS. Por ejemplo, no se admite GROUP BY GROUPING SETS (C1, GROUPING SETS (C2,

C3)).

Las palabras clave que no cumplen la norma ISO ALL, WITH CUBE y WITH ROLLUP no se pueden usar en una cláusula GROUP BY con las palabras clave ROLLUP, CUBE oGROUPING SETS.

Limitaciones de tamañoEn la cláusula simple GROUP BY, no hay ningún límite en cuanto al número de expresiones.

En una cláusula GROUP BY que utilice ROLLUP, CUBE o GROUPING SETS, el número máximo de expresiones es 32, y el número máximo de conjuntos de agrupación que

se pueden generar es 4.096 (212). Los ejemplos siguientes producen un error debido a que la cláusula GROUP BY es demasiado compleja:

En los ejemplos siguientes se generan 8.192 (213) conjuntos de agrupamiento.

En el ejemplo siguiente se generan 4.097 (212 + 1) conjuntos de agrupamiento.

En el ejemplo siguiente también se generan 4.097 ﴾212 + 1) conjuntos de agrupamiento. Los conjuntos de agrupación CUBE () y () generan una fila de total

general y los conjuntos de agrupación duplicados no se eliminan.

Compatibilidad con las características GROUP BY de ISO y ANSI SQL‐2006En SQL Server 2014, la cláusula GROUP BY no puede contener una subconsulta en una expresión que se use para la lista de agrupación. Se devuelve el error 144.

SQL Server 2014 admite todas las características GROUP BY incluidas en el estándar SQL‐2006 con las excepciones de sintaxis siguientes:

Los conjuntos de agrupamiento no se pueden usar en la cláusula GROUP BY a menos que formen parte de una lista GROUPING SETS explícita. Por ejemplo,

GROUP BY Column1, (Column2, ...ColumnN﴿ se admite en el estándar pero no en SQL Server. Se permite GROUP BY C1, GROUPING SETS ((Column2,

...ColumnN)) o GROUP BY Column1, Column2, ... ColumnN. Éstos son equivalentes semánticamente al ejemplo de GROUP BY anterior. Con ello se evita la

posibilidad de que GROUP BY Column1, (Column2, ...ColumnN) se pueda malinterpretar como GROUP BY C1, GROUPING SETS ((Column2, ...ColumnN)).

Éste no es equivalente semánticamente.

No se pueden usar conjuntos de agrupamiento dentro de conjuntos de agrupamiento. Por ejemplo, GROUP BY GROUPING SETS (A1, A2,…An, GROUPING SETS(C1, C2, ...Cn)) se admite en el estándar SQL‐2006 pero no en SQL Server. SQL Server 2014 permite GROUP BY GROUPING SETS( A1, A2,...An, C1, C2,

...Cn ) o GROUP BY GROUPING SETS( (A1), (A2), ... (An), (C1), (C2), ... (Cn) ). Estos ejemplos son equivalentes semánticamente al primer ejemplode GROUP BY y tienen una sintaxis más clara.

No se admite GROUP BY [ALL/DISTINCT] en una cláusula GROUP BY general ni con las construcciones GROUPING SETS, ROLLUP, CUBE, WITH CUBE o WITHROLLUP. ALL es el valor predeterminado y es implícito.

Comparación de las características GROUP BY compatiblesEn la tabla siguiente se describen las características de GROUP BY que son compatibles dependiendo de la versión de SQL Server y del nivel de compatibilidad de labase de datos.

Característica SQL Server Integration ServicesNivel de compatibilidad 100 o superior

con SQL Server

SQL Server 2008 o posterior con el nivel

de compatibilidad 90.

Agregados DISTINCT No se admite en WITH CUBE ni en WITH

ROLLUP.

Se admite en WITH CUBE, WITH ROLLUP,

GROUPING SETS, CUBE o ROLLUP.

Igual que el nivel de compatibilidad 100.

Función definida por elusuario con un nombre

CUBE o ROLLUP en la

cláusula GROUP BY

Se admite la función definida por el usuariodbo.cube(arg1,...argN) o

dbo.rollup(arg1,...argN) en la cláusulaGROUP BY.

Por ejemplo:

No se admite la función definida por elusuario dbo.cube (arg1,...argN) o

dbo.rollup(arg1,...argN) en la cláusulaGROUP BY.

Por ejemplo:

Se devuelve el mensaje de error siguiente:

Se admite la función definida por el usuariodbo.cube (arg1,...argN) o

dbo.rollup(arg1,...argN) en la cláusulaGROUP BY

Por ejemplo:

GROUP BY CUBE (a1, ..., a13)

GROUP BY a1, ..., a13 WITH CUBE

GROUP BY GROUPING SETS( CUBE(a1, ..., a12), b )

GROUP BY GROUPING SETS( CUBE(a1, ..., a12), ())

SELECT SUM (x)

FROM T

GROUP BY dbo.cube(y);

SELECT SUM (x)

FROM T

GROUP BY dbo.cube(y);

SELECT SUM (x)

FROM T

GROUP BY dbo.cube(y);

Page 72: Microsoft SELECT Transact SQL 2014

"Sintaxis incorrecta cerca de la palabra clave

'cube'|'rollup'."

Para evitar este problema, reemplace

dbo.cube por [dbo].[cube] o dbo.rollup

por [dbo].[rollup].

Se admite el siguiente ejemplo:

GROUPING SETS No compatible Compatible Compatible

CUBE No compatible Compatible No compatible

ROLLUP No compatible Compatible No compatible

Total general, como

GROUP BY ()

No compatible Compatible Compatible

Función GROUPING_ID No compatible Compatible Compatible

Función GROUPING Compatible Compatible Compatible

WITH CUBE Compatible Compatible Compatible

WITH ROLLUP Compatible Compatible Compatible

Eliminación de gruposduplicados de WITH

CUBE o WITH ROLLUP

Compatible Compatible Compatible

Ejemplos

A.Utilizar una cláusula GROUP BY simpleEn el ejemplo siguiente se recupera el total de cada SalesOrderID de la tabla SalesOrderDetail de la base de datos AdventureWorks2012.

B.Utilizar una cláusula GROUP BY con varias tablasEn el ejemplo siguiente se recupera el número de empleados de cada City de la tabla Address combinada con la tabla EmployeeAddress de la base de datos

AdventureWorks2012.

C.Utilizar una cláusula GROUP BY con una expresiónEn el ejemplo siguiente se recuperan las ventas totales de cada año con la función DATEPART. Debe incluirse la misma expresión en la lista SELECT y en la cláusula GROUPBY.

D.Utilizar una cláusula GROUP BY con una cláusula HAVINGEn el ejemplo siguiente se usa la cláusula HAVING para especificar cuáles de los grupos generados en la cláusula GROUP BY deben incluirse en el conjunto de resultados.

SELECT SUM (x)

FROM T

GROUP BY [dbo].[cube](y);

SELECT SalesOrderID, SUM(LineTotal) AS SubTotal

FROM Sales.SalesOrderDetail AS sod

GROUP BY SalesOrderID

ORDER BY SalesOrderID;

SELECT a.City, COUNT(bea.AddressID) EmployeeCount

FROM Person.BusinessEntityAddress AS bea

INNER JOIN Person.Address AS a

ON bea.AddressID = a.AddressID

GROUP BY a.City

ORDER BY a.City;

SELECT DATEPART(yyyy,OrderDate) AS N'Year'

,SUM(TotalDue) AS N'Total Order Amount'

FROM Sales.SalesOrderHeader

GROUP BY DATEPART(yyyy,OrderDate)

ORDER BY DATEPART(yyyy,OrderDate);

Page 73: Microsoft SELECT Transact SQL 2014

Vea también

ReferenciaGROUPING_ID (Transact-SQL)

GROUPING (Transact-SQL)

SELECT (Transact-SQL)

SELECT ﴾cláusula de Transact‐SQL﴿

© 2014 Microsoft

SELECT DATEPART(yyyy,OrderDate) AS N'Year'

,SUM(TotalDue) AS N'Total Order Amount'

FROM Sales.SalesOrderHeader

GROUP BY DATEPART(yyyy,OrderDate)

HAVING DATEPART(yyyy,OrderDate) >= N'2003'

ORDER BY DATEPART(yyyy,OrderDate);

Page 74: Microsoft SELECT Transact SQL 2014

HAVING (Transact-SQL)

Especifica una condición de búsqueda para un grupo o agregado. HAVING solo se puede utilizar con la instrucción SELECT. Normalmente, HAVING se utiliza en una

cláusula GROUP BY. Cuando no se utiliza GROUP BY, HAVING se comporta como una cláusula WHERE.

Se aplica a: SQL Server (SQL Server 2008 a versión actual﴿, Windows Azure SQL Database ﴾Versión inicial a versión actual).

Convenciones de sintaxis de Transact-SQL

Sintaxis

Argumentos

<search_condition>

Especifica la condición de búsqueda del grupo o del agregado que se debe cumplir.

No se pueden utilizar los tipos de datos text, image y ntext en una cláusula HAVING.

Ejemplos

En el ejemplo siguiente, donde se utiliza una cláusula HAVING simple, se recupera el total de cada SalesOrderID de la tabla SalesOrderDetail que exceda

$100000.00.

Vea también

ReferenciaGROUP BY (Transact-SQL)

WHERE (Transact-SQL)

© 2014 Microsoft

SQL Server 2014

[ HAVING <search condition> ]

USE AdventureWorks2012 ;

GO

SELECT SalesOrderID, SUM(LineTotal) AS SubTotal

FROM Sales.SalesOrderDetail

GROUP BY SalesOrderID

HAVING SUM(LineTotal) > 100000.00

ORDER BY SalesOrderID ;

Page 75: Microsoft SELECT Transact SQL 2014

UNION (Transact-SQL)

Combina los resultados de dos o más consultas en un solo conjunto de resultados que incluye todas las filas que pertenecen a las consultas de la unión. La operaciónUNION es distinta de la utilización de combinaciones de columnas de dos tablas.

A continuación se muestran las reglas básicas para combinar los conjuntos de resultados de dos consultas con UNION:

El número y el orden de las columnas debe ser el mismo en todas las consultas.

Los tipos de datos deben ser compatibles.

Se aplica a: SQL Server (SQL Server 2008 a versión actual﴿, Windows Azure SQL Database ﴾Versión inicial a versión actual).

Convenciones de sintaxis de Transact-SQL

Sintaxis

Argumentos

<query_specification> | ( <query_expression> )

Es una especificación o expresión de consulta que devuelve datos que se van a combinar con los datos de otra especificación o expresión de consulta. No es

preciso que las definiciones de las columnas que forman parte de una operación UNION sean iguales, pero deben ser compatibles a través de una conversiónimplícita. Cuando los tipos de datos difieren, el tipo de datos resultante se determina según las reglas de prioridad de tipos de datos. Cuando los tipos son los

mismos pero varían en cuanto a precisión, escala o longitud, el resultado se determina según las mismas reglas para combinar expresiones. Para obtener másinformación, vea Precisión, escala y longitud ﴾Transact‐SQL﴿.

Las columnas del tipo de datos xml deben ser equivalentes. Todas las columnas deben tener un tipo de esquema XML o no tener tipo. Si tienen tipo, debe ser el

de la misma colección de esquemas XML.

UNION

Especifica que se deben combinar varios conjuntos de resultados para ser devueltos como un solo conjunto de resultados.

ALL

Agrega todas las filas a los resultados. Incluye las filas duplicadas. Si no se especifica, las filas duplicadas se quitan.

Ejemplos

A.Usar una instrucción UNION simpleEn el siguiente ejemplo, el conjunto de resultados incluye el contenido de las columnas ProductModelID y Name de las tablas ProductModel y Gloves.

SQL Server 2014

{ <query_specification> | ( <query_expression> ) }

UNION [ ALL ]

<query_specification | ( <query_expression> )

[ UNION [ ALL ] <query_specification> | ( <query_expression> )

[ ...n ] ]

USE AdventureWorks2012;

GO

IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL

DROP TABLE dbo.Gloves;

GO

-- Create Gloves table.

SELECT ProductModelID, Name

INTO dbo.Gloves

FROM Production.ProductModel

WHERE ProductModelID IN (3, 4);

GO

-- Here is the simple union.

USE AdventureWorks2012;

GO

SELECT ProductModelID, Name

FROM Production.ProductModel

WHERE ProductModelID NOT IN (3, 4)

UNION

SELECT ProductModelID, Name

FROM dbo.Gloves

ORDER BY Name;

Page 76: Microsoft SELECT Transact SQL 2014

B.Usar SELECT INTO con UNION

En el siguiente ejemplo, la cláusula INTO de la segunda instrucción SELECT especifica que la tabla denominada ProductResults contiene el conjunto final de resultados

de la unión de las columnas designadas de las tablas ProductModel y Gloves. Tenga en cuenta que la tabla Gloves se crea en la primera instrucción SELECT.

C.Usar UNION con dos instrucciones SELECT y ORDER BY

El orden de algunos parámetros empleados con la cláusula UNION es importante. En el siguiente ejemplo se muestra el uso correcto e incorrecto de UNION en dos

instrucciones SELECT en las que se va a cambiar el nombre de una columna en el resultado.

D.Usar UNION de tres instrucciones SELECT para mostrar los efectos de ALL y los paréntesisEn los siguientes ejemplos se utiliza UNION para combinar los resultados de tres tablas que tienen las mismas 5 filas de datos. En el primer ejemplo se utiliza UNION ALL

para mostrar los registros duplicados y se devuelven las 15 filas. En el segundo ejemplo se utiliza UNION sin ALL para eliminar las filas duplicadas de los resultados

combinados de las tres instrucciones SELECT y se devuelven 5 filas.

GO

USE AdventureWorks2012;

GO

IF OBJECT_ID ('dbo.ProductResults', 'U') IS NOT NULL

DROP TABLE dbo.ProductResults;

GO

IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL

DROP TABLE dbo.Gloves;

GO

-- Create Gloves table.

SELECT ProductModelID, Name

INTO dbo.Gloves

FROM Production.ProductModel

WHERE ProductModelID IN (3, 4);

GO

USE AdventureWorks2012;

GO

SELECT ProductModelID, Name

INTO dbo.ProductResults

FROM Production.ProductModel

WHERE ProductModelID NOT IN (3, 4)

UNION

SELECT ProductModelID, Name

FROM dbo.Gloves;

GO

SELECT ProductModelID, Name

FROM dbo.ProductResults;

USE AdventureWorks2012;

GO

IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL

DROP TABLE dbo.Gloves;

GO

-- Create Gloves table.

SELECT ProductModelID, Name

INTO dbo.Gloves

FROM Production.ProductModel

WHERE ProductModelID IN (3, 4);

GO

/* INCORRECT */

USE AdventureWorks2012;

GO

SELECT ProductModelID, Name

FROM Production.ProductModel

WHERE ProductModelID NOT IN (3, 4)

ORDER BY Name

UNION

SELECT ProductModelID, Name

FROM dbo.Gloves;

GO

/* CORRECT */

USE AdventureWorks2012;

GO

SELECT ProductModelID, Name

FROM Production.ProductModel

WHERE ProductModelID NOT IN (3, 4)

UNION

SELECT ProductModelID, Name

FROM dbo.Gloves

ORDER BY Name;

GO

Page 77: Microsoft SELECT Transact SQL 2014

En el tercer ejemplo se utiliza ALL con el primer UNION y los paréntesis incluyen al segundo UNION que no utiliza ALL. El segundo UNION se procesa en primer lugar

porque se encuentra entre paréntesis. Devuelve 5 filas porque no se utiliza la opción ALL y se quitan los duplicados. Estas 5 filas se combinan con los resultados del

primer SELECT mediante las palabras clave UNION ALL. Esto no quita los duplicados entre los dos conjuntos de 5 filas. El resultado final es de 10 filas.

Vea también

ReferenciaSELECT (Transact-SQL)

Ejemplos de SELECT (Transact-SQL)

© 2014 Microsoft

USE AdventureWorks2012;

GO

IF OBJECT_ID ('dbo.EmployeeOne', 'U') IS NOT NULL

DROP TABLE dbo.EmployeeOne;

GO

IF OBJECT_ID ('dbo.EmployeeTwo', 'U') IS NOT NULL

DROP TABLE dbo.EmployeeTwo;

GO

IF OBJECT_ID ('dbo.EmployeeThree', 'U') IS NOT NULL

DROP TABLE dbo.EmployeeThree;

GO

SELECT pp.LastName, pp.FirstName, e.JobTitle

INTO dbo.EmployeeOne

FROM Person.Person AS pp JOIN HumanResources.Employee AS e

ON e.BusinessEntityID = pp.BusinessEntityID

WHERE LastName = 'Johnson';

GO

SELECT pp.LastName, pp.FirstName, e.JobTitle

INTO dbo.EmployeeTwo

FROM Person.Person AS pp JOIN HumanResources.Employee AS e

ON e.BusinessEntityID = pp.BusinessEntityID

WHERE LastName = 'Johnson';

GO

SELECT pp.LastName, pp.FirstName, e.JobTitle

INTO dbo.EmployeeThree

FROM Person.Person AS pp JOIN HumanResources.Employee AS e

ON e.BusinessEntityID = pp.BusinessEntityID

WHERE LastName = 'Johnson';

GO

-- Union ALL

SELECT LastName, FirstName, JobTitle

FROM dbo.EmployeeOne

UNION ALL

SELECT LastName, FirstName ,JobTitle

FROM dbo.EmployeeTwo

UNION ALL

SELECT LastName, FirstName,JobTitle

FROM dbo.EmployeeThree;

GO

SELECT LastName, FirstName,JobTitle

FROM dbo.EmployeeOne

UNION

SELECT LastName, FirstName, JobTitle

FROM dbo.EmployeeTwo

UNION

SELECT LastName, FirstName, JobTitle

FROM dbo.EmployeeThree;

GO

SELECT LastName, FirstName,JobTitle

FROM dbo.EmployeeOne

UNION ALL

(

SELECT LastName, FirstName, JobTitle

FROM dbo.EmployeeTwo

UNION

SELECT LastName, FirstName, JobTitle

FROM dbo.EmployeeThree

);

GO

Page 78: Microsoft SELECT Transact SQL 2014

EXCEPT e INTERSECT (Transact-SQL)

Devuelven valores distintos al comparar los resultados de dos consultas.

EXCEPT devuelve los valores distintos de la consulta izquierda que no se encuentran en la consulta derecha.

INTERSECT devuelve los valores distintos devueltos por las consultas situadas a los lados izquierdo y derecho del operando INTERSECT.

Las reglas básicas para combinar los conjuntos de resultados de dos consultas que utilizan EXCEPT o INTERSECT son las siguientes:

El número y el orden de las columnas debe ser el mismo en todas las consultas.

Los tipos de datos deben ser compatibles.

Se aplica a: SQL Server (SQL Server 2008 a versión actual﴿, Windows Azure SQL Database ﴾Versión inicial a versión actual).

Convenciones de sintaxis de Transact-SQL

Sintaxis

Argumentos

<query_specification> | ( <query_expression> )

Es una especificación o expresión de consulta que devuelve datos que se van a comparar con los de otra especificación o expresión de consulta. No es preciso

que las definiciones de las columnas que forman parte de una operación EXCEPT o INTERSECT sean idénticas, pero deben ser comparables por medio de unaconversión implícita. Cuando los tipos de datos difieren, el tipo que se utiliza para realizar la comparación y devolver los resultados se determina según lasreglas de prioridad de tipo de datos.

Cuando los tipos son los mismos pero varían en cuanto a precisión, escala o longitud, el resultado se determina según las mismas reglas para combinarexpresiones. Para obtener más información, vea Precisión, escala y longitud ﴾Transact‐SQL﴿.

La especificación o expresión de consulta no puede devolver columnas de tipo xml, text, ntext, image o no binario definido por el usuario CLR, ya que estos

tipos de datos no son comparables.

EXCEPT

Devuelve los valores distintos de la consulta situada a la izquierda del operando EXCEPT que no se devuelven desde la consulta derecha.

INTERSECT

Devuelve los valores distintos devueltos por las consultas situadas a los lados izquierdo y derecho del operando INTERSECT.

Comentarios

Cuando los tipos de datos de columnas comparables devueltos por las consultas situadas a la izquierda y a la derecha de los operandos EXCEPT o INTERSECT son

tipos de datos de caracteres con intercalaciones diferentes, la comparación requerida se realiza conforme a las reglas de prioridad de intercalación. Si no es posible

realizar esta conversión, Motor de base de datos de SQL Server devuelve un error.

Cuando se comparan filas para determinar valores distintos, dos valores NULL se consideran equivalentes.

Los nombres de columna del conjunto de resultados devueltos por EXCEPT o INTERSECT son los mismos que han sido devueltos por la consulta situada en el lado

izquierdo del operando.

Los nombres o alias de columna de las cláusulas ORDER BY deben hacer referencia a los nombres de columna devueltos por la consulta del lado izquierdo.

La nulabilidad de cualquier columna del conjunto de resultados devueltos por EXCEPT o INTERSECT es la misma que la de la columna correspondiente devuelta por la

consulta situada en el lado izquierdo del operando.

Si EXCEPT o INTERSECT se utilizan con otros operadores en una expresión, ésta se evalúa en el contexto de la siguiente prioridad:

1. Expresiones entre paréntesis

2. El operando INTERSECT

3. EXCEPT y UNION se evalúan de izquierda a derecha según su posición en la expresión

Si EXCEPT o INTERSECT se utilizan para comparar más de dos conjuntos de consultas, la conversión del tipo de datos se determina al comparar dos consultas a la vezy mediante las reglas mencionadas de evaluación de expresiones.

SQL Server 2014

{ <query_specification> | ( <query_expression> ) }

{ EXCEPT | INTERSECT }

{ <query_specification> | ( <query_expression> ) }

Page 79: Microsoft SELECT Transact SQL 2014

EXCEPT e INTERSECT no se pueden usar en definiciones de vistas distribuidas con particiones ni en notificaciones de consultas.

EXCEPT e INTERSECT se pueden utilizar en consultas distribuidas, pero solo se ejecutan en el servidor local y no se insertan en el servidor vinculado. Por lo tanto, el uso

de EXCEPT e INTERSECT en consultas distribuidas puede afectar al rendimiento.

Los cursores de solo avance rápido o estáticos son completamente compatibles con el conjunto de resultados si se utilizan con una operación EXCEPT o INTERSECT. Si

un cursor controlado por conjunto de claves o dinámico se utiliza con una operación EXCEPT o INTERSECT, el cursor del conjunto de resultados de la operación seconvierte en un cursor estático.

Cuando una operación EXCEPT se muestra mediante la característica Plan de presentación gráfico de SQL Server Management Studio, la operación aparece como unoperador left anti semi join y la operación INTERSECT aparece como un operador left semi join.

Ejemplos

En los ejemplos siguientes se muestra cómo utilizar los operandos INTERSECT y EXCEPT. La primera consulta devuelve todos los valores de la tabla

Production.Product para comparar los resultados con INTERSECT y EXCEPT.

La siguiente consulta devuelve los valores distintos devueltos por las consultas situadas a los lados izquierdo y derecho del operando INTERSECT.

La siguiente consulta devuelve los valores distintos de la consulta situados a la izquierda del operando EXCEPT que no se encuentran en la consulta derecha.

La siguiente consulta devuelve los valores distintos de la consulta situados a la izquierda del operando EXCEPT que no se encuentran en la consulta derecha. Las tablas

se invierten respecto al ejemplo anterior.

© 2014 Microsoft

USE AdventureWorks2012;

GO

SELECT ProductID

FROM Production.Product ;

--Result: 504 Rows

USE AdventureWorks2012;

GO

SELECT ProductID

FROM Production.Product

INTERSECT

SELECT ProductID

FROM Production.WorkOrder ;

--Result: 238 Rows (products that have work orders)

USE AdventureWorks2012;

GO

SELECT ProductID

FROM Production.Product

EXCEPT

SELECT ProductID

FROM Production.WorkOrder ;

--Result: 266 Rows (products without work orders)

USE AdventureWorks2012;

GO

SELECT ProductID

FROM Production.WorkOrder

EXCEPT

SELECT ProductID

FROM Production.Product ;

--Result: 0 Rows (work orders without products)

Page 80: Microsoft SELECT Transact SQL 2014

ORDER BY ﴾cláusula de Transact‐SQL﴿

Ordenar los datos devueltos por una consulta en SQL Server. Use esta cláusula para:

Ordenar el conjunto de resultados de una consulta por la lista de columnas especificada y, opcionalmente, limitar las filas devueltas a un intervalo especificado. El

orden en que se devuelven las filas en un conjunto de resultados no se puede garantizar, a menos que se especifique una cláusula ORDER BY.

Determinar el orden en que se aplican los valores de la función de categoría al conjunto de resultados.

Se aplica a: SQL Server (SQL Server 2008 a versión actual﴿, Windows Azure SQL Database ﴾Versión inicial a versión actual).

Convenciones de sintaxis de Transact-SQL

Sintaxis

Argumentos

order_by_expression

Especifica una columna o expresión según la que se ordenará el conjunto de resultados de la consulta. Una columna de ordenación se puede especificar comoun nombre o un alias de columna, o un entero no negativo que representa la posición de la columna en la lista de selección.

Es posible especificar varias columnas de ordenación. Los nombres de columna tienen que ser únicos. La secuencia de las columnas de ordenación de la cláusulaORDER BY define la organización del conjunto de resultados ordenado. Es decir, el conjunto de resultados se ordena conforme a la primera columna y, a

continuación, esa lista ordenada se ordena según la segunda columna, y así sucesivamente.

Los nombres de columna a los que se hace referencia en la cláusula ORDER BY deben corresponderse con una columna de la lista de selección o con unacolumna definida en la tabla especificada en la cláusula FROM sin ambigüedades.

COLLATE collation_name

Especifica que la operación ORDER BY debe realizarse conforme a la intercalación especificada en collation_name y no conforme a la intercalación de la columnadefinida en la tabla o vista. collation_name puede ser un nombre de intercalación de Windows o un nombre de intercalación de SQL. Para obtener másinformación, vea Compatibilidad con la intercalación y Unicode. COLLATE solo se aplica a las columnas con tipos de datos char, varchar, nchar y nvarchar.

ASC | DESC

Indica que los valores de la columna especificada se deben ordenar en sentido ascendente o descendente. ASC ordena del valor mínimo al valor máximo. DESC

ordena del valor máximo al valor mínimo. ASC es el criterio de ordenación predeterminado. Los valores NULL se tratan como los valores más bajos posibles.

OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }

Especifica el número de filas que se deben omitir antes de comenzar a devolver filas de la expresión de consulta. El valor puede ser una expresión o constanteentera mayor o igual que cero.

Se aplica a: SQL Server 2012 a SQL Server 2014 y Windows Azure SQL Database.

offset_row_count_expression puede ser una variable, un parámetro o una subconsulta escalar constante. Cuando se utiliza una subconsulta, no puede hacer

referencia a ninguna columna definida en el ámbito de la consulta externa. Es decir, no se puede poner en correlación con la consulta externa.

ROW y ROWS son sinónimos y se proporcionan para ofrecer compatibilidad con ANSI.

En los planes de ejecución de consultas, el valor de recuento de filas de desplazamiento se muestra en el atributo Offset del operador de consulta TOP.

FETCH { FIRST | NEXT } { integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY

Especifica el número de filas que se devolverán después de procesar la cláusula OFFSET. El valor puede ser una expresión o constante entera mayor o igual queuno.

Se aplica a: SQL Server 2012 a SQL Server 2014 y Windows Azure SQL Database.

fetch_row_count_expression puede ser una variable, un parámetro o una subconsulta escalar constante. Cuando se utiliza una subconsulta, no puede hacer

referencia a ninguna columna definida en el ámbito de la consulta externa. Es decir, no se puede poner en correlación con la consulta externa.

SQL Server 2014

ORDER BY order_by_expression

[ COLLATE collation_name ]

[ ASC | DESC ]

[ ,...n ]

[ <offset_fetch> ]

<offset_fetch> ::=

{

OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }

[

FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY

]

}

Transact-SQL

Page 81: Microsoft SELECT Transact SQL 2014

FIRST y NEXT son sinónimos y se proporcionan para ofrecer compatibilidad con ANSI.

ROW y ROWS son sinónimos y se proporcionan para ofrecer compatibilidad con ANSI.

En los planes de ejecución de consultas, el valor de recuento de filas de desplazamiento se muestra en el atributo Rows o Top del operador de consulta TOP.

Prácticas recomendadas

Evite especificar enteros en la cláusula ORDER BY como representaciones posicionales de las columnas en la lista de selección. Por ejemplo, aunque una instruccióncomo SELECT ProductID, Name FROM Production.Production ORDER BY 2 es válida, otros usuarios no la entenderán tan bien como si especificase el nombre de lacolumna real. Además, para realizar cambios en la lista de selección, como modificar el orden de las columnas o agregar otras nuevas, será preciso modificar lacláusula ORDER BY a fin de evitar resultados inesperados.

En una instrucción SELECT TOP ﴾N﴿, utilice siempre una cláusula ORDER BY. Esta es la única manera de indicar previsiblemente a qué filas afecta TOP. Para obtener másinformación, vea TOP (Transact-SQL).

Interoperabilidad

Cuando se utiliza con una instrucción SELECT...INTO para insertar filas de otro origen, la cláusula ORDER BY no garantiza la inserción de las filas en el ordenespecificado.

Al usar OFFSET y FETCH en una vista no se cambia la propiedad Updateability de la vista.

Limitaciones y restricciones

No hay ningún límite en cuanto al número de columnas de la cláusula ORDER BY; sin embargo, el tamaño total de las columnas especificadas en una cláusula ORDER BYno puede superar los 8.060 bytes.

Las columnas de tipo ntext, text, image, geography, geometry y xml no se pueden usar en una cláusula ORDER BY.

No puede especificarse un entero o una constante cuando en una función de categoría aparece order_by_expression . Para obtener más información, vea OVER ﴾cláusulade Transact-SQL).

Si los nombres de tabla están asociados a un alias en la cláusula FROM, solo pueden usarse los nombres de alias para calificar sus columnas en la cláusula ORDER BY.

Los nombres y alias de columna especificados en la cláusula ORDER BY deben estar definidos en la lista de selección si la instrucción SELECT contiene uno de losoperadores o cláusulas siguientes:

UNION, operador

EXCEPT, operador

INTERSECT, operador

SELECT DISTINCT

Además, cuando la instrucción incluye un operador UNION, EXCEPT o INTERSECT, los nombres o los alias de columna deben ser los especificados en la lista deselección de la primera consulta ﴾lado izquierdo﴿.

En una consulta que utiliza los operadores UNION, INTERSECT o EXCEPT, ORDER BY se permite únicamente al final de la instrucción. Esta restricción se aplicaúnicamente cuando se especifica UNION, EXCEPT e INTERSECT en una consulta de nivel superior y no en una subconsulta. Vea la sección Ejemplos que aparece másadelante.

La cláusula ORDER BY no es válida en vistas, funciones insertadas, tablas derivadas y subconsultas, a menos que se especifiquen también las cláusulas TOP u OFFSET yFETCH. Cuando ORDER BY se utiliza en estos objetos, la cláusula únicamente se utiliza para determinar las filas devueltas por la cláusula TOP o las cláusulas OFFSET YFETCH. La cláusula ORDER BY no garantiza resultados ordenados cuando se consulten estos constructores, a menos que también se especifique ORDER BY en la mismaconsulta.

OFFSET y FETCH no se admiten en vistas indizadas ni en vistas definidas mediante la cláusula CHECK OPTION.

OFFSET y FETCH se pueden utilizar en cualquier consulta que permita TOP y ORDER BY con las siguientes limitaciones:

La cláusula OVER no admite OFFSET ni FETCH.

OFFSET y FETCH no se pueden especificar directamente en las instrucciones INSERT, UPDATE, MERGE ni DELETE, pero sí en una subconsulta definida en ellas. Por

ejemplo, en la instrucción INSERT INTO SELECT, se pueden especificar OFFSET y FETCH en la instrucción SELECT.

En una consulta que utiliza los operadores UNION, EXCEPT o INTERSECT, OFFSET y FETCH únicamente se pueden utilizar en la consulta final que especifica elorden de los resultados de la consulta.

TOP no se puede combinar con OFFSET y FETCH en la misma expresión de consulta ﴾en el mismo ámbito de la consulta﴿.

Utilizar OFFSET y FETCH para limitar las filas devueltas

Page 82: Microsoft SELECT Transact SQL 2014

Recomendamos utilizar las cláusulas OFFSET y FETCH en lugar de la cláusula TOP para implementar una solución de paginación de consulta y limitar el número de filasenviadas a una aplicación cliente.

Para utilizar OFFSET y FETCH como solución de paginación, es preciso ejecutar la consulta una vez por cada "página" de datos devuelta a la aplicación cliente. Por

ejemplo, para devolver los resultados de una consulta en incrementos de 10 filas, se debe ejecutar la consulta una vez para devolver las filas de 1 a 10, después otravez para devolver las filas de 11 a 20, y así sucesivamente. Cada consulta es independiente y no está relacionada con las demás de forma alguna. Esto significa que, a

diferencia de cuando se usa un cursor en que la consulta se ejecuta una vez y su estado se mantiene en el servidor, en este caso es la aplicación cliente la responsablede realizar el seguimiento del estado. Para lograr resultados estables entre las solicitudes de consultas donde se utilicen OFFSET y FETCH, se deben cumplir las

siguientes condiciones:

1. Los datos subyacentes que la consulta utilice no deben cambiar. Es decir, o bien las filas afectadas por la consulta no se actualizarán, o bien todas las solicitudescorrespondientes a las páginas de la consulta se ejecutarán en una transacción única utilizando el aislamiento de transacción serializable o de instantánea. Para

obtener información acerca de estos niveles de aislamiento de transacción, vea SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

2. Debe garantizarse que la columna o combinación de columnas contenidas en la cláusula ORDER BY sean únicas.

Vea el ejemplo que "Ejecutar varias consultas en una sola transacción" en la sección Ejemplos que aparece más adelante en este tema.

Si el hecho de que los planes de ejecución sean coherentes es importante para su solución de paginación, puede ser conveniente utilizar la sugerencia de consultaOPTIMIZE FOR para los parámetros de OFFSET y FETCH. Vea "Especificar expresiones para valores de OFFSET y FETCH" en la sección Ejemplos que aparece másadelante en este tema. Para obtener más información acerca de OPTIMIZE FOR, vea Sugerencias de consulta (Transact-SQL).

Ejemplos

Categoría Elementos de sintaxis ofrecidos

Sintaxis básica ORDER BY

Especificar orden ascendente y descendente DESC • ASC

Especificar una intercalación COLLATE

Especificar un orden condicional CASE, expresión

Usar ORDER BY en una función de categoría Funciones de categoría

Limitar el número de filas devueltas OFFSET • FETCH

Usar ORDER BY con UNION, EXCEPT e INTERSECT UNION

Sintaxis básicaEn los ejemplos de esta sección se muestra la funcionalidad básica de la cláusula ORDER BY utilizando la sintaxis mínima requerida.

A.Especificar una sola columna definida en la lista de selecciónEn el siguiente ejemplo se ordena el conjunto de resultados por la columna numérica ProductID. Dado que no se especifica un criterio de ordenación concreto, seutiliza el valor predeterminado (orden ascendente).

B.Especificar una columna que no está definida en la lista de selecciónEn el siguiente ejemplo se ordena el conjunto de resultados por una columna que no está incluida en la lista de selección, pero sí definida en la tabla especificada en lacláusula FROM.

C.Especificar un alias como columna de ordenaciónEn el ejemplo siguiente se especifica el alias de columna SchemaName como columna de criterio de ordenación.

USE AdventureWorks2012;

GO

SELECT ProductID, Name FROM Production.Product

WHERE Name LIKE 'Lock Washer%'

ORDER BY ProductID;

USE AdventureWorks2012;

GO

SELECT ProductID, Name, Color

FROM Production.Product

ORDER BY ListPrice;

USE AdventureWorks2012;

GO

SELECT name, SCHEMA_NAME(schema_id) AS SchemaName

FROM sys.objects

WHERE type = 'U'

ORDER BY SchemaName;

Page 83: Microsoft SELECT Transact SQL 2014

D.Especificar una expresión como columna de ordenaciónEn el ejemplo siguiente se utiliza una expresión como columna de ordenación. La expresión se define mediante la función DATEPART para ordenar el conjunto deresultados según el año de contratación de los empleados.

Especificar un criterio de ordenación ascendente y descendenteA.Especificar un orden descendente

En el siguiente ejemplo se ordena el conjunto de resultados en sentido descendente según la columna numérica ProductID.

B.Especificar un orden ascendente

En el siguiente ejemplo se ordena el conjunto de resultados en orden ascendente según la columna Name. Observe que los caracteres están ordenados alfabéticamente,no numéricamente. Es decir, 10 se ordena antes que 2.

C.Especificar orden ascendente y también descendenteEn el siguiente ejemplo se ordena el conjunto de resultados según dos columnas. El conjunto de resultados se ordena en primer lugar en sentido ascendente según lacolumna FirstName y, a continuación, en orden descendente según la columna LastName.

Especificar una intercalaciónEn el siguiente ejemplo se muestra cómo especificar una intercalación en la cláusula ORDER BY puede cambiar el orden en que se devuelven los resultados de laconsulta. Se crea una tabla que contiene una columna definida mediante una intercalación que no distingue entre mayúsculas y minúsculas, ni las tildes. Los valores se

insertan con diversas diferencias de uso de mayúsculas, minúsculas y tildes. Dado que no se especifica ninguna intercalación en la cláusula ORDER BY, la primeraconsulta utiliza la intercalación de la columna al ordenar los valores. En la segunda consulta, se especifica una intercalación que distingue entre mayúsculas y minúsculasy las tildes; en consecuencia, cambia el orden en el que se devuelven las filas.

Especificar un orden condicional

En los ejemplos siguientes se utiliza la expresión CASE en una cláusula ORDER BY para determinar de manera condicional el criterio de ordenación de las filas según elvalor de una columna dada. En el primer ejemplo se evalúe el valor de la columna SalariedFlag de la tabla HumanResources.Employee. Los empleados que tienen la

columna SalariedFlag establecida en 1 se devuelven en orden descendente según el BusinessEntityID. Los empleados que tienen la columna SalariedFlag

establecida en 0 se devuelven en orden ascendente según el BusinessEntityID. En el segundo ejemplo, el conjunto de resultados se ordena según la columnaTerritoryName cuando la columna CountryRegionName es igual a 'United States' y según la columna CountryRegionName en las demás filas.

USE AdventureWorks2012;

GO

SELECT BusinessEntityID, JobTitle, HireDate

FROM HumanResources.Employee

ORDER BY DATEPART(year, HireDate);

USE AdventureWorks2012;

GO

SELECT ProductID, Name FROM Production.Product

WHERE Name LIKE 'Lock Washer%'

ORDER BY ProductID DESC;

USE AdventureWorks2012;

GO

SELECT ProductID, Name FROM Production.Product

WHERE Name LIKE 'Lock Washer%'

ORDER BY Name ASC ;

USE AdventureWorks2012;

GO

SELECT LastName, FirstName FROM Person.Person

WHERE LastName LIKE 'R%'

ORDER BY FirstName ASC, LastName DESC ;

USE tempdb;

GO

CREATE TABLE #t1 (name nvarchar(15) COLLATE Latin1_General_CI_AI)

GO

INSERT INTO #t1 VALUES(N'Sánchez'),(N'Sanchez'),(N'sánchez'),(N'sanchez');

-- This query uses the collation specified for the column 'name' for sorting.

SELECT name

FROM #t1

ORDER BY name;

-- This query uses the collation specified in the ORDER BY clause for sorting.

SELECT name

FROM #t1

ORDER BY name COLLATE Latin1_General_CS_AS;

Page 84: Microsoft SELECT Transact SQL 2014

Usar ORDER BY en una función de categoríaEn el siguiente ejemplo se utiliza la cláusula ORDER BY en las funciones de categoría ROW_NUMBER, RANK, DENSE_RANK y NTILE.

Limitar el número de filas devueltasEn los siguientes ejemplos se utiliza OFFSET y FETCH para limitar el número de filas devueltas por una consulta.

Se aplica a: SQL Server 2012 a SQL Server 2014 y Windows Azure SQL Database.

A.Especificar constantes enteras para los valores de OFFSET y FETCH

En el siguiente ejemplo se especifica una constante entera como valor para las cláusulas OFFSET y FETCH. La primera consulta devuelve todas las filas ordenadas segúnla columna DepartmentID. Compare los resultados devueltos por esta consulta con los de las dos consultas siguientes. La consulta siguiente utiliza la cláusula OFFSET 5ROWS para omitir las primeras 5 filas y devolver todas las restantes. La última consulta utiliza la cláusula OFFSET 0 ROWS para comenzar por la primera fila y, a

continuación, utiliza FETCH NEXT 10 ROWS ONLY para limitar las filas devueltas a 10 filas del conjunto de resultados ordenado.

B.Especificar variables para los valores de OFFSET y FETCH

En el siguiente ejemplo se declaran las variables @StartingRowNumber y @FetchRows, y se especifican estas variables en las cláusulas OFFSET y FETCH.

SELECT BusinessEntityID, SalariedFlag

FROM HumanResources.Employee

ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC

,CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END;

GO

SELECT BusinessEntityID, LastName, TerritoryName, CountryRegionName

FROM Sales.vSalesPerson

WHERE TerritoryName IS NOT NULL

ORDER BY CASE CountryRegionName WHEN 'United States' THEN TerritoryName

ELSE CountryRegionName END;

USE AdventureWorks2012;

GO

SELECT p.FirstName, p.LastName

,ROW_NUMBER() OVER (ORDER BY a.PostalCode) AS "Row Number"

,RANK() OVER (ORDER BY a.PostalCode) AS "Rank"

,DENSE_RANK() OVER (ORDER BY a.PostalCode) AS "Dense Rank"

,NTILE(4) OVER (ORDER BY a.PostalCode) AS "Quartile"

,s.SalesYTD, a.PostalCode

FROM Sales.SalesPerson AS s

INNER JOIN Person.Person AS p

ON s.BusinessEntityID = p.BusinessEntityID

INNER JOIN Person.Address AS a

ON a.AddressID = p.BusinessEntityID

WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0;

USE AdventureWorks2012;

GO

-- Return all rows sorted by the column DepartmentID.

SELECT DepartmentID, Name, GroupName

FROM HumanResources.Department

ORDER BY DepartmentID;

-- Skip the first 5 rows from the sorted result set and return all remaining rows.

SELECT DepartmentID, Name, GroupName

FROM HumanResources.Department

ORDER BY DepartmentID OFFSET 5 ROWS;

-- Skip 0 rows and return only the first 10 rows from the sorted result set.

SELECT DepartmentID, Name, GroupName

FROM HumanResources.Department

ORDER BY DepartmentID

OFFSET 0 ROWS

FETCH NEXT 10 ROWS ONLY;

USE AdventureWorks2012;

GO

-- Specifying variables for OFFSET and FETCH values

DECLARE @StartingRowNumber tinyint = 1

, @FetchRows tinyint = 8;

SELECT DepartmentID, Name, GroupName

FROM HumanResources.Department

ORDER BY DepartmentID ASC

OFFSET @StartingRowNumber ROWS

Page 85: Microsoft SELECT Transact SQL 2014

C.Especificar expresiones para los valores de OFFSET y FETCH

En el siguiente ejemplo se utiliza la expresión @StartingRowNumber - 1 para especificar el valor de OFFSET y la expresión @EndingRowNumber - @StartingRowNumber+ 1 para especificar el valor de FETCH. Además, se especifica la sugerencia de consulta OPTIMIZE FOR. Esta sugerencia se puede usar para que se utilice un valor

concreto para una variable local al compilar y optimizar la consulta. El valor se utiliza solo durante la optimización de la consulta y no durante la ejecución de la misma.Para obtener más información, vea Sugerencias de consulta (Transact-SQL).

D.Especificar una subconsulta escalar constante para los valores de OFFSET y FETCH

En el siguiente ejemplo se utiliza una subconsulta escalar constante a fin de definir el valor para la cláusula FETCH. La subconsulta devuelve un valor único de la columnaPageSize de la tabla dbo.AppSettings.

E.Ejecutar varias consultas en una sola transacciónEn el siguiente ejemplo se muestra un método de implementar una solución de paginación que permite asegurarse de la devolución de resultados estables en todaslas solicitudes de la consulta. La consulta se ejecuta en una sola transacción utilizando el nivel de aislamiento de instantánea, mientras que la columna especificada en lacláusula ORDER BY asegura la singularidad de la columna.

FETCH NEXT @FetchRows ROWS ONLY;

USE AdventureWorks2012;

GO

-- Specifying expressions for OFFSET and FETCH values

DECLARE @StartingRowNumber tinyint = 1

, @EndingRowNumber tinyint = 8;

SELECT DepartmentID, Name, GroupName

FROM HumanResources.Department

ORDER BY DepartmentID ASC

OFFSET @StartingRowNumber - 1 ROWS

FETCH NEXT @EndingRowNumber - @StartingRowNumber + 1 ROWS ONLY

OPTION ( OPTIMIZE FOR (@StartingRowNumber = 1, @EndingRowNumber = 20) );

-- Specifying a constant scalar subquery

USE AdventureWorks2012;

GO

CREATE TABLE dbo.AppSettings (AppSettingID int NOT NULL, PageSize int NOT NULL);

GO

INSERT INTO dbo.AppSettings VALUES(1, 10);

GO

DECLARE @StartingRowNumber tinyint = 1;

SELECT DepartmentID, Name, GroupName

FROM HumanResources.Department

ORDER BY DepartmentID ASC

OFFSET @StartingRowNumber ROWS

FETCH NEXT (SELECT PageSize FROM dbo.AppSettings WHERE AppSettingID = 1) ROWS ONLY;

USE AdventureWorks2012;

GO

-- Ensure the database can support the snapshot isolation level set for the query.

IF (SELECT snapshot_isolation_state FROM sys.databases WHERE name = N'AdventureWorks2012') = 0

ALTER DATABASE AdventureWorks2012 SET ALLOW_SNAPSHOT_ISOLATION ON;

GO

-- Set the transaction isolation level to SNAPSHOT for this query.

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

GO

-- Beging the transaction

BEGIN TRANSACTION;

GO

-- Declare and set the variables for the OFFSET and FETCH values.

DECLARE @StartingRowNumber int = 1

, @RowCountPerPage int = 3;

-- Create the condition to stop the transaction after all rows have been returned.

WHILE (SELECT COUNT(*) FROM HumanResources.Department) >= @StartingRowNumber

BEGIN

-- Run the query until the stop condition is met.

SELECT DepartmentID, Name, GroupName

FROM HumanResources.Department

ORDER BY DepartmentID ASC

OFFSET @StartingRowNumber - 1 ROWS

FETCH NEXT @RowCountPerPage ROWS ONLY;

-- Increment @StartingRowNumber value.

SET @StartingRowNumber = @StartingRowNumber + @RowCountPerPage;

CONTINUE

END;

GO

Page 86: Microsoft SELECT Transact SQL 2014

Usar ORDER BY con UNION, EXCEPT e INTERSECT

Cuando una consulta utiliza los operadores UNION, EXCEPT o INTERSECT, la cláusula ORDER BY se debe especificar al final de la instrucción y se ordenan los resultadosde las consultas combinadas. En el siguiente ejemplo se devuelven todos los productos que son rojos o amarillos y la lista combinada se ordena según la columnaListPrice.

Vea también

ReferenciaExpresiones (Transact-SQL)

SELECT (Transact-SQL)

FROM (Transact-SQL)

Funciones de categoría ﴾Transact‐SQL﴿TOP (Transact-SQL)

Sugerencias de consulta (Transact-SQL)

EXCEPT e INTERSECT (Transact-SQL)

UNION (Transact-SQL)

CASE (Transact-SQL)

© 2014 Microsoft

COMMIT TRANSACTION;

GO

USE AdventureWorks2012;

GO

SELECT Name, Color, ListPrice

FROM Production.Product

WHERE Color = 'Red'

-- ORDER BY cannot be specified here.

UNION ALL

SELECT Name, Color, ListPrice

FROM Production.Product

WHERE Color = 'Yellow'

ORDER BY ListPrice ASC;

Page 87: Microsoft SELECT Transact SQL 2014

FOR ﴾cláusula de Transact‐SQL﴿

La cláusula FOR se utiliza para especificar la opción BROWSE o XML. BROWSE y XML son opciones no relacionadas.

Importante

La directiva XMLDATA para la opción FOR XML ha quedado desusada. Utilice la XSD generación en los modos RAW y AUTO. No hay sustitución para la directivaXMLDATA en modo EXPLICIT. Esta característica se quitará en una versión futura de Microsoft SQL Server. Evite utilizar esta característica en nuevos trabajos dedesarrollo y tenga previsto modificar las aplicaciones que actualmente la utilizan.

Se aplica a: SQL Server (SQL Server 2008 a versión actual﴿, Windows Azure SQL Database ﴾Versión inicial a versión actual).

Convenciones de sintaxis de Transact-SQL

Sintaxis

Argumentos

BROWSE

Especifica que se permiten las actualizaciones mientras se visualizan los datos en el cursor del modo de exploración de DB‐Library. Una tabla se puede explorar

en una aplicación si la tabla incluye una columna timestamp, la tabla tiene un índice único y la opción FOR BROWSE está al final de las instrucciones SELECTenviadas a una instancia de SQL Server.

Nota

No se puede utilizar <lock_hint> HOLDLOCK en una instrucción SELECT que incluya la opción FOR BROWSE.

FOR BROWSE no puede aparecer en instrucciones SELECT combinadas mediante el operador UNION.

Nota

Cuando las columnas de clave de índice único de una tabla pueden aceptar valores NULL, y la tabla está en la parte interna de la combinación externa, elíndice no se admite en el modo de exploración.

El modo de exploración permite examinar las filas de la tabla de SQL Server y actualizar los datos de la tabla fila por fila. Para tener acceso a una tabla de SQL

Server en una aplicación en el modo de exploración, debe utilizar una de las dos opciones siguientes:

La instrucción SELECT que utiliza para tener acceso a los datos de la tabla de SQL Server debe finalizar con las palabras clave FOR BROWSE. Al activar la

opción FOR BROWSE para utilizar el modo de exploración, se crean tablas temporales.

Debe ejecutar la instrucción de Transact‐SQL siguiente para activar el modo de exploración utilizando la opción NO_BROWSETABLE:

SQL Server 2014

[ FOR { BROWSE | <XML> } ]

<XML> ::=

XML

{

{ RAW [ ( 'ElementName' ) ] | AUTO }

[

<CommonDirectives>

[ , { XMLDATA | XMLSCHEMA [ ( 'TargetNameSpaceURI' ) ] } ]

[ , ELEMENTS [ XSINIL | ABSENT ]

]

| EXPLICIT

[

<CommonDirectives>

[ , XMLDATA ]

]

| PATH [ ( 'ElementName' ) ]

[

<CommonDirectives>

[ , ELEMENTS [ XSINIL | ABSENT ] ]

]

}

<CommonDirectives> ::=

[ , BINARY BASE64 ]

[ , TYPE ]

[ , ROOT [ ( 'RootName' ) ] ]

SET NO_BROWSETABLE ON

Page 88: Microsoft SELECT Transact SQL 2014

Al activar la opción NO_BROWSETABLE, todas las instrucciones SELECT se comportan como si la opción FOR BROWSE se anexara a las instrucciones. Sin

embargo, la opción NO_BROWSETABLE no crea las tablas temporales que la opción FOR BROWSE utiliza generalmente para enviar los resultados a la

aplicación.

Cuando se intenta tener acceso a los datos de las tablas de SQL Server en modo de exploración utilizando una consulta SELECT que implica una instrucción decombinación externa, y cuando se define un índice único en la tabla que está presente en el lado interno de una instrucción de combinación externa, el modo deexploración no admite el índice único. El modo de exploración únicamente admite el índice único cuando todas las columnas de clave de índice único puedenaceptar valores NULL. El modo de exploración no admite el índice único si se cumplen las condiciones siguientes:

Intenta tener acceso a los datos de las tablas de SQL Server en modo de exploración utilizando una consulta SELECT que implica una instrucción decombinación externa.

Un índice único se define en la tabla que está presente en el lado interno de una instrucción de combinación externa.

Para reproducir este comportamiento en el modo de exploración, siga estos pasos:

1. En SQL Server Management Studio, cree una base de datos denominada SampleDB.

2. En la base de datos SampleDB, cree una tabla tleft y una tabla tright que contengan ambas una única columna que se denomine c1. Defina un índice únicoen la columna c1 de la tabla tleft y establezca la columna para aceptar valores NULL. Para ello, ejecute las instrucciones de Transact-SQL siguientes en una

ventana de consulta adecuada:

3. Inserte varios valores en las tablas tleft y tright. Asegúrese de insertar un valor NULL en la tabla tleft. Para ello, ejecute las instrucciones de Transact-SQL

siguientes en la ventana de consulta:

4. Active la opción NO_BROWSETABLE. Para ello, ejecute las instrucciones de Transact-SQL siguientes en la ventana de consulta:

5. Obtenga acceso a los datos de las tablas tleft y tright utilizando una instrucción de combinación externa en la consulta SELECT. Asegúrese de que la tablatleft está en el lado interno de la instrucción de combinación externa. Para ello, ejecute las instrucciones de Transact-SQL siguientes en la ventana de

consulta:

Observe la salida siguiente en el panel Resultados:

c1

----

NULL

NULL

Después de ejecutar la consulta SELECT para obtener acceso a las tablas en el modo de exploración, el conjunto de resultados de la consulta SELCT contienedos valores NULL para la columna c1 de la tabla tleft debido a la definición de la instrucción de combinación externa derecha. Por consiguiente, en el conjunto

de resultados no puede distinguir entre los valores NULL que procedían de la tabla y los incluidos por la instrucción de combinación externa derecha. Puede

recibir resultados incorrectos si debe omitir los valores NULL del conjunto de resultados.

Nota

Si las columnas que están incluidas en el índice único no aceptan valores NULL, todos los valores NULL en el conjunto de resultados fueron incluidos por lainstrucción de combinación externa.

CREATE TABLE tleft(c1 INT NULL UNIQUE) ;

GO

CREATE TABLE tright(c1 INT NULL) ;

GO

INSERT INTO tleft VALUES(2) ;

INSERT INTO tleft VALUES(NULL) ;

INSERT INTO tright VALUES(1) ;

INSERT INTO tright VALUES(3) ;

INSERT INTO tright VALUES(NULL) ;

GO

SET NO_BROWSETABLE ON ;

GO

SELECT tleft.c1

FROM tleft

RIGHT JOIN tright

ON tleft.c1 = tright.c1

WHERE tright.c1 <> 2 ;

Page 89: Microsoft SELECT Transact SQL 2014

XML

Especifica que el resultado de una consulta se devolverá como documento XML. Debe especificarse uno de los siguientes modos XML: RAW, AUTO, EXPLICIT.

Para obtener más información acerca de datos XML y SQL Server, vea FOR XML (SQL Server).

RAW [ ('ElementName') ]

Obtiene el resultado de la consulta y transforma cada fila del conjunto de resultados en un elemento XML con un identificador genérico <row /> como etiquetadel elemento. Opcionalmente, puede especificar un nombre para el elemento de fila. La salida XML resultante utiliza el parámetro ElementName especificado

como el elemento de fila generado para cada fila. Para obtener más información, vea Usar el modo RAW con FOR XML y Usar el modo RAW con FOR XML.

AUTO

Devuelve los resultados de la consulta en un árbol anidado XML sencillo. Cada tabla de la cláusula FROM, para la que al menos se presenta una columna en lacláusula SELECT, se representa como elemento XML. A las columnas presentadas en la cláusula SELECT se les asignan los atributos de elemento apropiados.Para obtener más información, vea Usar el modo AUTO con FOR XML.

EXPLICIT

Especifica que la forma del árbol XML resultante está definida explícitamente. Con este modo, es necesario escribir las consultas de una cierta manera, de modo

que se pueda especificar explícitamente información adicional acerca de la anidación deseada. Para obtener más información, vea Usar el modo EXPLICIT con

FOR XML.

XMLDATA

Devuelve el esquema XDR insertado, pero no agrega el elemento raíz al resultado. Si se especifica XMLDATA, el esquema XDR se agrega al documento.

XMLSCHEMA [ ('TargetNameSpaceURI') ]

Devuelve el esquema XSD insertado. Opcionalmente puede especificar un URI de espacio de nombres de destino al especificar esta directiva, que devuelve el

espacio de nombres especificado en el esquema. Para obtener más información, vea Generar un esquema XSD insertado.

ELEMENTS

Especifica que las columnas se devuelven como subelementos. Sin embargo, se les asignan atributos XML. Esta opción solo se admite en los modos RAW, AUTOy PATH. Para obtener más información, vea Usar el modo RAW con FOR XML.

XSINIL

Especifica que se va a crear un elemento con el atributo xsi:nil establecido en True para los valores de columna NULL. Esta opción solo se puede especificarcon la directiva ELEMENTS. Para obtener más información, vea Generar elementos para valores NULL mediante el parámetro XSINIL.

ABSENT

Indica que para los valores de columna NULL, no se agregarán los elementos XML correspondientes en el resultado XML. Especifique esta opción solo conELEMENTS.

PATH [ ('ElementName') ]

Genera un contenedor del elemento de <row> para cada fila en el conjunto de resultados. Opcionalmente, especifique un nombre de elemento para el

contenedor del elemento <row>. Si se proporciona una cadena vacía, como FOR XML PATH ﴾'') , no se genera un elemento contenedor. El uso de PATH puede

proporcionar una alternativa más sencilla a consultas escritas con la directiva EXPLICIT. Para obtener más información, vea Usar el modo PATH con FOR XML.

BINARY BASE64

Especifica que la consulta devuelve los datos binarios en el formato codificado BINARY BASE64. Al recuperar datos binarios mediante el modo RAW y EXPLICIT,

se debe especificar esta opción. Éste es el valor predeterminado en el modo AUTO.

TYPE

Especifica que la consulta devuelve un resultado de tipo xml. Para obtener más información, vea Directiva TYPE en consultas FOR XML.

ROOT [ ('RootName') ]

Especifica que se va a agregar un solo elemento de nivel superior al XML resultante. Opcionalmente puede especificar el nombre del elemento raíz que se va agenerar. Si no se especifica el nombre de raíz opcional, se agrega el elemento <root> predeterminado.

Ejemplos

En el siguiente ejemplo se especifica FOR XML AUTO con las opciones TYPE y XMLSCHEMA. Debido a la opción TYPE, el conjunto de resultados que se devuelve al cliente

es de tipo xml. La opción XMLSCHEMA especifica que el esquema XSD insertado está incluido en los datos XML devueltos y la opción ELEMENTS especifica que el

resultado XML está centrado en elementos.

Vea también

ReferenciaSELECT (Transact-SQL)

FOR XML (SQL Server)

© 2014 Microsoft

USE AdventureWorks2012;

GO

SELECT p.BusinessEntityID, FirstName, LastName, PhoneNumber AS Phone

FROM Person.Person AS p

JOIN Person.PersonPhone AS pph ON p.BusinessEntityID = pph.BusinessEntityID

WHERE LastName LIKE 'G%'

ORDER BY LastName, FirstName

FOR XML AUTO, TYPE, XMLSCHEMA, ELEMENTS XSINIL;

Page 90: Microsoft SELECT Transact SQL 2014

OPTION ﴾cláusula de Transact‐SQL﴿

Especifica que en toda la consulta se debe utilizar la sugerencia de consulta especificada. Solo se puede especificar cada sugerencia de consulta una vez, aunque se

permiten varias sugerencias de consulta. Solo se puede especificar una cláusula OPTION con la instrucción.

Esta cláusula se puede especificar en las instrucciones SELECT, DELETE, UPDATE y MERGE.

Se aplica a: SQL Server (SQL Server 2008 a versión actual﴿, Windows Azure SQL Database ﴾Versión inicial a versión actual).

Convenciones de sintaxis de Transact-SQL

Sintaxis

Argumentos

query_hint

Palabras clave que indican qué sugerencias del optimizador se emplean para personalizar la forma en que el Motor de base de datos procesa la instrucción.Para obtener más información, vea Sugerencias de consulta (Transact-SQL).

Ejemplos

En el ejemplo siguiente se muestra cómo se usa la cláusula OPTION con una cláusula GROUP BY.

Vea también

ReferenciaSugerencias (Transact-SQL)

SELECT (Transact-SQL)

UPDATE (Transact-SQL)

MERGE (Transact-SQL)

DELETE (Transact-SQL)

© 2014 Microsoft

SQL Server 2014

[ OPTION ( <query_hint> [ ,...n ] ) ]

USE AdventureWorks2012;

GO

SELECT ProductID, OrderQty, SUM(LineTotal) AS Total

FROM Sales.SalesOrderDetail

WHERE UnitPrice < $5.00

GROUP BY ProductID, OrderQty

ORDER BY ProductID, OrderQty

OPTION (HASH GROUP, FAST 10);

GO

Page 91: Microsoft SELECT Transact SQL 2014

OVER ﴾cláusula de Transact‐SQL﴿

Determina las particiones y el orden de un conjunto de filas antes de que se aplique la función de ventana asociada. Es decir, la cláusula OVER define una ventana o unconjunto de filas definido por el usuario en un conjunto de resultados de la consulta. Una función de ventana calcula entonces un valor para cada fila de la ventana. Puede

utilizar la cláusula OVER con funciones para calcular valores agregados tales como medias móviles, agregados acumulados, totales acumulados o N elementos superiorespor resultados del grupo.

Se aplica a:

Funciones de categoría

Funciones de agregado

Funciones analíticas

Función NEXT VALUE FOR

Se aplica a: SQL Server (SQL Server 2008 a versión actual﴿, Windows Azure SQL Database ﴾Versión inicial a versión actual).

Convenciones de sintaxis de Transact-SQL

Sintaxis

Argumentos

PARTITION BY

Divide el conjunto de resultados de la consulta en particiones. La función se aplica a cada partición por separado y el cálculo se reinicia para cada partición.

SQL Server 2014

OVER (

[ <PARTITION BY clause> ]

[ <ORDER BY clause> ]

[ <ROW or RANGE clause> ]

)

<PARTITION BY clause> ::=

PARTITION BY value_expression , ... [ n ]

<ORDER BY clause> ::=

ORDER BY order_by_expression

[ COLLATE collation_name ]

[ ASC | DESC ]

[ ,...n ]

<ROW or RANGE clause> ::=

{ ROWS | RANGE } <window frame extent>

<window frame extent> ::=

{ <window frame preceding>

| <window frame between>

}

<window frame between> ::=

BETWEEN <window frame bound> AND <window frame bound>

<window frame bound> ::=

{ <window frame preceding>

| <window frame following>

}

<window frame preceding> ::=

{

UNBOUNDED PRECEDING

| <unsigned_value_specification> PRECEDING

| CURRENT ROW

}

<window frame following> ::=

{

UNBOUNDED FOLLOWING

| <unsigned_value_specification> FOLLOWING

| CURRENT ROW

}

<unsigned value specification> ::=

{ <unsigned integer literal> }

Page 92: Microsoft SELECT Transact SQL 2014

value_expression

Especifica la columna a partir de la cual se particiona el conjunto de filas. value_expression solo puede hacer referencia a columnas disponibles a través de lacláusula FROM. value_expression no puede hacer referencia a expresiones ni a alias de la lista de selección. value_expression puede ser una expresión de columna,una subconsulta escalar, una función escalar o una variable definida por el usuario.

<Cláusula ORDER BY>Define el orden lógico de las filas dentro de cada partición del conjunto de resultados. Es decir, especifica el orden lógico en el que se realiza el cálculo de lafunción de ventana.

order_by_expression

Especifica la columna o expresión según la cual se va a realizar la ordenación. order_by_expression solo puede hacer referencia a columnas disponibles a travésde la cláusula FROM. No se puede especificar un número entero para representar un nombre de columna o alias.

COLLATE collation_name

Especifica que la operación ORDER BY se debe realizar según la intercalación especificada en collation_name. collation_name puede ser un nombre de

intercalación de Windows o un nombre de intercalación de SQL. Para obtener más información, vea Compatibilidad con la intercalación y Unicode. COLLATE solo

se aplica a las columnas con tipos de datos char, varchar, nchar y nvarchar.

ASC | DESC

Indica que los valores de la columna especificada se deben ordenar en sentido ascendente o descendente. ASC es el criterio de ordenación predeterminado. Los

valores NULL se tratan como los valores más bajos posibles.

ROWS | RANGE

Se aplica a: SQL Server 2012 a SQL Server 2014.

Limita aún más las filas de la partición especificando los puntos inicial y final. Para ello, se especifica un rango de filas con respecto a la fila actual mediante

asociación lógica o asociación física. La asociación física se realiza mediante la cláusula ROWS.

La cláusula ROWS restringe las filas dentro de una partición especificando un número fijo de filas delante y detrás de la fila actual. La cláusula RANGE tambiénpuede restringir lógicamente las filas de una partición especificando un rango de valores con respecto al valor de la fila actual. Las filas precedentes y siguientes

se definen en función de la ordenación de la cláusula ORDER BY. El marco de ventana “RANGE … CURRENT ROW …” incluye todas las filas que tienen los mismosvalores en la expresión ORDER BY que la fila actual. Por ejemplo, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW indica que la ventana de filas en la que

opera la función tiene un tamaño de tres filas, con dos filas delante hasta e inclusive la fila actual.

Nota

ROWS o RANGE requieren que se especifique la cláusula ORDER BY. Si ORDER BY contiene varias expresiones de orden, CURRENT ROW FOR RANGE

considera todas las columnas de la lista ORDER BY al determinar la fila actual.

UNBOUNDED PRECEDING

Se aplica a: SQL Server 2012 a SQL Server 2014.

Especifica que la ventana comienza en la primera fila de la partición. UNBOUNDED PRECEDING solo se puede especificar como punto inicial de la ventana.

<especificación de valor sin signo> PRECEDINGSe especifica con <especificación de valor sin signo> para indicar el número de filas o valores que preceden a la fila actual. Esta especificación no se permitepara RANGE.

CURRENT ROW

Se aplica a: SQL Server 2012 a SQL Server 2014.

Especifica que la ventana comienza o termina en la fila actual cuando se utiliza con ROWS, o el valor actual cuando se utiliza con RANGE. CURRENT ROW se

puede especificar como punto inicial o final.

BETWEEN <límite del marco de ventana > AND <límite del marco de ventana >

Se aplica a: SQL Server 2012 a SQL Server 2014.

Se utiliza con ROWS o RANGE para especificar los puntos de límite inferior ﴾inicio﴿ y superior ﴾final﴿ de la ventana. <límite del marco de ventana> define el puntoinicial del límite y <límite del marco de ventana> define el punto final. El límite superior no puede ser menor que el límite inferior.

UNBOUNDED FOLLOWING

Se aplica a: SQL Server 2012 a SQL Server 2014.

Especifica que la ventana termina en la última fila de la partición. UNBOUNDED FOLLOWING solo se puede especificar como punto final de una ventana. Por

ejemplo, RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING define una ventana que empieza en la fila actual y termina en la última fila de lapartición.

<especificación de valor sin signo> FOLLOWINGSe especifica con <especificación de valor sin signo> para indicar el número de filas o valores detrás de la fila actual. Cuando <especificación de valor sinsigno> FOLLOWING se especifica como punto inicial de la ventana, el punto final debe ser <especificación de valor sin signo>FOLLOWING. Por ejemplo, ROWS

BETWEEN 2 FOLLOWING AND 10 FOLLOWING define una ventana que empieza en la segunda fila a partir de la fila actual y termina en la décima fila a partir de lafila actual. Esta especificación no se permite para RANGE.

literal entero sin signo

Se aplica a: SQL Server 2012 a SQL Server 2014.

Es un literal entero positivo ﴾incluido el 0﴿ que especifica el número de filas o de valores delante o detrás de la fila o el valor actual. Esta especificación es válidasolamente para ROWS.

Comentarios generales

Page 93: Microsoft SELECT Transact SQL 2014

Se pueden utilizar varias funciones de ventana en una sola consulta con una única cláusula FROM. La cláusula OVER de cada función puede diferir en particiones ytambién en orden.

Si no se especifica PARTITION BY, la función trata todas las filas del conjunto de resultados de la consulta como un único grupo.

Si no se especifica ORDER BY, se utiliza la partición completa para el marco de ventana. Esto se aplica únicamente a las funciones que no requieren la cláusula ORDERBY. Si no se especifica ROWS/RANGE pero sí ORDER BY, RANGE UNBOUNDED PRECEDING AND CURRENT ROW se utiliza como valor predeterminado para el marco deventana. Esto se aplica solamente a las funciones que pueden aceptar la especificación opcional de ROWS/RANGE. Por ejemplo, las funciones de clasificación nopueden aceptar ROWS/RANGE; por lo tanto, este marco de ventana no se aplica aunque se especifique ORDER BY y no se especifique ROWS/RANGE.

Si se especifica ROWS/RANGE y se usa <marco de ventana precedente> para <extensión de marco de ventana> ﴾sintaxis abreviada﴿, esta especificación se usa para elpunto inicial del límite del marco de ventana y CURRENT ROW se usa para el punto final. Por ejemplo, “ROWS 5 PRECEDING” es igual a “ROWS BETWEEN 5 PRECEDINGAND CURRENT ROW”.

Limitaciones y restricciones

No se puede utilizar la cláusula OVER con la función de agregado CHECKSUM.

No se puede utilizar RANGE con <especificación de valor sin signo> PRECEDING o <especificación de valor sin signo> FOLLOWING.

Dependiendo de la función de clasificación, de agregado o analítica utilizada con la cláusula OVER, puede que no se admitan la <cláusula ORDER BY> o la <cláusulaROWS y RANGE>.

Ejemplos

A.Utilizar la cláusula OVER con la función ROW_NUMBEREn el ejemplo siguiente se muestra cómo usar la cláusula OVER con la función ROW_NUMBER para mostrar un número de fila para cada fila de una partición. La

cláusula ORDER BY especificada en la cláusula OVER ordena las filas de cada partición por la columna SalesYTD. La cláusula ORDER BY en la instrucción SELECTdetermina el orden en que se devuelve el conjunto completo de resultados de la consulta.

El conjunto de resultados es el siguiente.

Row Number LastName SalesYTD PostalCode

--------------- ----------------------- --------------------- ----------

1 Mitchell 4251368.5497 98027

2 Blythe 3763178.1787 98027

3 Carson 3189418.3662 98027

4 Reiter 2315185.611 98027

5 Vargas 1453719.4653 98027

6 Ansman-Wolfe 1352577.1325 98027

1 Pak 4116871.2277 98055

2 Varkey Chudukatil 3121616.3202 98055

3 Saraiva 2604540.7172 98055

4 Ito 2458535.6169 98055

5 Valdez 1827066.7118 98055

6 Mensa-Annan 1576562.1966 98055

7 Campbell 1573012.9383 98055

8 Tsoflias 1421810.9242 98055

USE AdventureWorks2012;

GO

SELECT ROW_NUMBER() OVER(PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS "Row Number",

p.LastName, s.SalesYTD, a.PostalCode

FROM Sales.SalesPerson AS s

INNER JOIN Person.Person AS p

ON s.BusinessEntityID = p.BusinessEntityID

INNER JOIN Person.Address AS a

ON a.AddressID = p.BusinessEntityID

WHERE TerritoryID IS NOT NULL

AND SalesYTD <> 0

ORDER BY PostalCode;

GO

Page 94: Microsoft SELECT Transact SQL 2014

B.Utilizar la cláusula OVER con funciones de agregadoEn el ejemplo siguiente se utiliza la cláusula OVER con funciones de agregado en todas las filas devueltas por la consulta. En este ejemplo, el uso de OVER es más eficazque usar subconsultas para obtener los valores agregados.

El conjunto de resultados es el siguiente.

En el ejemplo siguiente se muestra el uso de la cláusula OVER con una función de agregado en un valor calculado.

El conjunto de resultados es el siguiente. Tenga en cuenta que los agregados se calculan mediante SalesOrderID y se calcula Percent by ProductID para cada líneade cada SalesOrderID.

USE AdventureWorks2012;

GO

SELECT SalesOrderID, ProductID, OrderQty

,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS Total

,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Avg"

,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Count"

,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Min"

,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Max"

FROM Sales.SalesOrderDetail

WHERE SalesOrderID IN(43659,43664);

GO

SalesOrderID ProductID OrderQty Total Avg Count Min Max

------------ ----------- -------- ----------- ----------- ----------- ------ ------

43659 776 1 26 2 12 1 6

43659 777 3 26 2 12 1 6

43659 778 1 26 2 12 1 6

43659 771 1 26 2 12 1 6

43659 772 1 26 2 12 1 6

43659 773 2 26 2 12 1 6

43659 774 1 26 2 12 1 6

43659 714 3 26 2 12 1 6

43659 716 1 26 2 12 1 6

43659 709 6 26 2 12 1 6

43659 712 2 26 2 12 1 6

43659 711 4 26 2 12 1 6

43664 772 1 14 1 8 1 4

43664 775 4 14 1 8 1 4

43664 714 1 14 1 8 1 4

43664 716 1 14 1 8 1 4

43664 777 2 14 1 8 1 4

43664 771 3 14 1 8 1 4

43664 773 1 14 1 8 1 4

43664 778 1 14 1 8 1 4

USE AdventureWorks2012;

GO

SELECT SalesOrderID, ProductID, OrderQty

,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS Total

,CAST(1. * OrderQty / SUM(OrderQty) OVER(PARTITION BY SalesOrderID)

*100 AS DECIMAL(5,2))AS "Percent by ProductID"

FROM Sales.SalesOrderDetail

WHERE SalesOrderID IN(43659,43664);

GO

SalesOrderID ProductID OrderQty Total Percent by ProductID

------------ ----------- -------- ----------- ---------------------------------------

43659 776 1 26 3.85

43659 777 3 26 11.54

43659 778 1 26 3.85

43659 771 1 26 3.85

43659 772 1 26 3.85

43659 773 2 26 7.69

43659 774 1 26 3.85

43659 714 3 26 11.54

43659 716 1 26 3.85

43659 709 6 26 23.08

43659 712 2 26 7.69

43659 711 4 26 15.38

43664 772 1 14 7.14

43664 775 4 14 28.57

43664 714 1 14 7.14

43664 716 1 14 7.14

43664 777 2 14 14.29

43664 771 3 14 21.4

Page 95: Microsoft SELECT Transact SQL 2014

C.Producir una media móvil y un total acumulativoEn el ejemplo siguiente se usan las funciones AVG y SUM con la cláusula OVER para proporcionar una media móvil y un total acumulado de ventas anuales para cadaterritorio de la tabla Sales.SalesPerson. Se crean particiones de los datos por TerritoryID y se ordenan lógicamente por SalesYTD. Esto significa que la funciónAVG se calcula para cada territorio en función del año de ventas. Observe que para TerritoryID 1, solo hay dos filas para el año de ventas 2005, que representan losdos vendedores con ventas durante ese año. Se calculan las ventas medias de estas dos filas y la tercera fila que representa las ventas durante el año 2006 se incluyeen el cálculo.

El conjunto de resultados es el siguiente.

En este ejemplo, la cláusula OVER no incluye PARTITION BY. Esto significa que la función se aplicará a todas las filas devueltas por la consulta. La cláusula ORDER BYespecificada en la cláusula OVER determina el orden lógico al que se aplica la función AVG. La consulta devuelve una media móvil de ventas por año para todos losterritorios de ventas especificados en la cláusula WHERE. La cláusula ORDER BY especificada en la instrucción SELECT determina el orden en que se muestran las filas dela consulta.

El conjunto de resultados es el siguiente.

43664 773 1 14 7.14

43664 778 1 14 7.14

(20 row(s) affected)

USE AdventureWorks2012;

GO

SELECT BusinessEntityID, TerritoryID

,DATEPART(yy,ModifiedDate) AS SalesYear

,CONVERT(varchar(20),SalesYTD,1) AS SalesYTD

,CONVERT(varchar(20),AVG(SalesYTD) OVER (PARTITION BY TerritoryID

ORDER BY DATEPART(yy,ModifiedDate)

),1) AS MovingAvg

,CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID

ORDER BY DATEPART(yy,ModifiedDate)

),1) AS CumulativeTotal

FROM Sales.SalesPerson

WHERE TerritoryID IS NULL OR TerritoryID < 5

ORDER BY TerritoryID,SalesYear;

BusinessEntityID TerritoryID SalesYear SalesYTD MovingAvg CumulativeTotal

---------------- ----------- ----------- -------------------- -------------------- --------------------

274 NULL 2005 559,697.56 559,697.56 559,697.56

287 NULL 2006 519,905.93 539,801.75 1,079,603.50

285 NULL 2007 172,524.45 417,375.98 1,252,127.95

283 1 2005 1,573,012.94 1,462,795.04 2,925,590.07

280 1 2005 1,352,577.13 1,462,795.04 2,925,590.07

284 1 2006 1,576,562.20 1,500,717.42 4,502,152.27

275 2 2005 3,763,178.18 3,763,178.18 3,763,178.18

277 3 2005 3,189,418.37 3,189,418.37 3,189,418.37

276 4 2005 4,251,368.55 3,354,952.08 6,709,904.17

281 4 2005 2,458,535.62 3,354,952.08 6,709,904.17

(10 row(s) affected)

SELECT BusinessEntityID, TerritoryID

,DATEPART(yy,ModifiedDate) AS SalesYear

,CONVERT(varchar(20),SalesYTD,1) AS SalesYTD

,CONVERT(varchar(20),AVG(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate)

),1) AS MovingAvg

,CONVERT(varchar(20),SUM(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate)

),1) AS CumulativeTotal

FROM Sales.SalesPerson

WHERE TerritoryID IS NULL OR TerritoryID < 5

ORDER BY SalesYear;

BusinessEntityID TerritoryID SalesYear SalesYTD MovingAvg CumulativeTotal

---------------- ----------- ----------- -------------------- -------------------- --------------------

274 NULL 2005 559,697.56 2,449,684.05 17,147,788.35

275 2 2005 3,763,178.18 2,449,684.05 17,147,788.35

276 4 2005 4,251,368.55 2,449,684.05 17,147,788.35

277 3 2005 3,189,418.37 2,449,684.05 17,147,788.35

280 1 2005 1,352,577.13 2,449,684.05 17,147,788.35

281 4 2005 2,458,535.62 2,449,684.05 17,147,788.35

283 1 2005 1,573,012.94 2,449,684.05 17,147,788.35

284 1 2006 1,576,562.20 2,138,250.72 19,244,256.47

287 NULL 2006 519,905.93 2,138,250.72 19,244,256.47

285 NULL 2007 172,524.45 1,941,678.09 19,416,780.93

(10 row(s) affected)

Page 96: Microsoft SELECT Transact SQL 2014

D.Especificar la cláusula ROWS

Se aplica a: SQL Server 2012 a SQL Server 2014.

En el ejemplo siguiente se utiliza la cláusula ROWS para definir una ventana de cuyas filas se calcula la fila actual y el número N de filas incluidas a continuación ﴾1 fila eneste ejemplo).

El conjunto de resultados es el siguiente.

En el ejemplo siguiente, la cláusula ROWS se especifica con UNBOUNDED PRECEDING. El resultado es que la ventana comienza en la primera fila de la partición.

El conjunto de resultados es el siguiente.

Vea también

ReferenciaFunciones de agregado (Transact-SQL)

ConceptosFunciones analíticas ﴾Transact‐SQL﴿

© 2014 Microsoft

SELECT BusinessEntityID, TerritoryID

,CONVERT(varchar(20),SalesYTD,1) AS SalesYTD

,DATEPART(yy,ModifiedDate) AS SalesYear

,CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID

ORDER BY DATEPART(yy,ModifiedDate)

ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ),1) AS CumulativeTotal

FROM Sales.SalesPerson

WHERE TerritoryID IS NULL OR TerritoryID < 5;

BusinessEntityID TerritoryID SalesYTD SalesYear CumulativeTotal

---------------- ----------- -------------------- ----------- --------------------

274 NULL 559,697.56 2005 1,079,603.50

287 NULL 519,905.93 2006 692,430.38

285 NULL 172,524.45 2007 172,524.45

283 1 1,573,012.94 2005 2,925,590.07

280 1 1,352,577.13 2005 2,929,139.33

284 1 1,576,562.20 2006 1,576,562.20

275 2 3,763,178.18 2005 3,763,178.18

277 3 3,189,418.37 2005 3,189,418.37

276 4 4,251,368.55 2005 6,709,904.17

281 4 2,458,535.62 2005 2,458,535.62

SELECT BusinessEntityID, TerritoryID

,CONVERT(varchar(20),SalesYTD,1) AS SalesYTD

,DATEPART(yy,ModifiedDate) AS SalesYear

,CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID

ORDER BY DATEPART(yy,ModifiedDate)

ROWS UNBOUNDED PRECEDING),1) AS CumulativeTotal

FROM Sales.SalesPerson

WHERE TerritoryID IS NULL OR TerritoryID < 5;

BusinessEntityID TerritoryID SalesYTD SalesYear CumulativeTotal

---------------- ----------- -------------------- ----------- --------------------

274 NULL 559,697.56 2005 559,697.56

287 NULL 519,905.93 2006 1,079,603.50

285 NULL 172,524.45 2007 1,252,127.95

283 1 1,573,012.94 2005 1,573,012.94

280 1 1,352,577.13 2005 2,925,590.07

284 1 1,576,562.20 2006 4,502,152.27

275 2 3,763,178.18 2005 3,763,178.18

277 3 3,189,418.37 2005 3,189,418.37

276 4 4,251,368.55 2005 4,251,368.55

281 4 2,458,535.62 2005 6,709,904.17

Page 97: Microsoft SELECT Transact SQL 2014

Ejemplos de SELECT (Transact-SQL)

En este tema se proporcionan ejemplos del uso de la instrucción SELECT.

A.Usar SELECT para recuperar filas y columnas

En el siguiente ejemplo se muestran tres fragmentos de código. En el primer ejemplo de código, se devuelven todas las filas ﴾no se especifica la cláusula WHERE﴿ ytodas las columnas (con *) de la tabla Product de la base de datos AdventureWorks2012 .

En este ejemplo se devuelven todas las filas ﴾no se ha especificado la cláusula WHERE﴿ y solo un subconjunto de las columnas ﴾Name, ProductNumber, ListPrice) de la

tabla Product de la base de datos AdventureWorks2012 . Además, se agrega un encabezado de columna.

En este ejemplo solo se devuelven las filas de Product que tienen una línea de productos de R y cuyo valor correspondiente a los días para fabricar es inferior a 4.

B.Usar SELECT con encabezados de columna y cálculos

En los siguientes ejemplos se devuelven todas las filas de la tabla Product. En el primer ejemplo se devuelven las ventas totales y los descuentos de cada producto. En

el segundo ejemplo se calculan los beneficios totales de cada producto.

Ésta es la consulta que calcula el beneficio de cada producto de cada pedido de venta.

SQL Server 2014

USE AdventureWorks2012;

GO

SELECT *

FROM Production.Product

ORDER BY Name ASC;

-- Alternate way.

USE AdventureWorks2012;

GO

SELECT p.*

FROM Production.Product AS p

ORDER BY Name ASC;

GO

USE AdventureWorks2012;

GO

SELECT Name, ProductNumber, ListPrice AS Price

FROM Production.Product

ORDER BY Name ASC;

GO

USE AdventureWorks2012;

GO

SELECT Name, ProductNumber, ListPrice AS Price

FROM Production.Product

WHERE ProductLine = 'R'

AND DaysToManufacture < 4

ORDER BY Name ASC;

GO

USE AdventureWorks2012;

GO

SELECT p.Name AS ProductName,

NonDiscountSales = (OrderQty * UnitPrice),

Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount)

FROM Production.Product AS p

INNER JOIN Sales.SalesOrderDetail AS sod

ON p.ProductID = sod.ProductID

ORDER BY ProductName DESC;

GO

USE AdventureWorks2012;

GO

SELECT 'Total income is', ((OrderQty * UnitPrice) * (1.0 - UnitPriceDiscount)), ' for ',

p.Name AS ProductName

Transact-SQL

Transact-SQL

Transact-SQL

Transact-SQL

Transact-SQL

Page 98: Microsoft SELECT Transact SQL 2014

C.Usar DISTINCT con SELECT

En el siguiente ejemplo se utiliza DISTINCT para evitar la recuperación de títulos duplicados.

D.Crear tablas con SELECT INTO

En el primer ejemplo se crea una tabla temporal denominada #Bicycles en tempdb.

En el segundo ejemplo se crea la tabla permanente NewProducts.

E.Usar subconsultas correlacionadas

En el siguiente ejemplo se muestran consultas que son semánticamente equivalentes y se demuestra la diferencia entre la utilización de la palabra clave EXISTS y la

palabra clave IN. Ambos son ejemplos de subconsultas válidas que recuperan una instancia de cada nombre de producto cuyo modelo es un jersey de manga largacon logotipo y cuyos números de ProductModelID coinciden en las tablas Product y ProductModel.

FROM Production.Product AS p

INNER JOIN Sales.SalesOrderDetail AS sod

ON p.ProductID = sod.ProductID

ORDER BY ProductName ASC;

GO

USE AdventureWorks2012;

GO

SELECT DISTINCT JobTitle

FROM HumanResources.Employee

ORDER BY JobTitle;

GO

USE tempdb;

GO

IF OBJECT_ID (N'#Bicycles',N'U') IS NOT NULL

DROP TABLE #Bicycles;

GO

SELECT *

INTO #Bicycles

FROM AdventureWorks2012.Production.Product

WHERE ProductNumber LIKE 'BK%';

GO

USE AdventureWorks2012;

GO

IF OBJECT_ID('dbo.NewProducts', 'U') IS NOT NULL

DROP TABLE dbo.NewProducts;

GO

ALTER DATABASE AdventureWorks2012 SET RECOVERY BULK_LOGGED;

GO

SELECT * INTO dbo.NewProducts

FROM Production.Product

WHERE ListPrice > $25

AND ListPrice < $100;

GO

ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL;

GO

USE AdventureWorks2012;

GO

SELECT DISTINCT Name

FROM Production.Product AS p

WHERE EXISTS

(SELECT *

FROM Production.ProductModel AS pm

WHERE p.ProductModelID = pm.ProductModelID

AND pm.Name LIKE 'Long-Sleeve Logo Jersey%');

GO

-- OR

Transact-SQL

Transact-SQL

Transact-SQL

Transact-SQL

Page 99: Microsoft SELECT Transact SQL 2014

En el siguiente ejemplo se utiliza IN en una subconsulta correlativa o repetitiva. Se trata de una consulta que depende de la consulta externa de sus valores. Se ejecuta

varias veces, una vez por cada fila que pueda seleccionar la consulta externa. Esta consulta recupera una instancia del nombre y apellido de cada empleado cuya

bonificación en la tabla SalesPerson sea de 5000.00 y cuyos números de identificación coincidan en las tablas Employee y SalesPerson.

La subconsulta anterior de esta instrucción no se puede evaluar independientemente de la consulta externa. Necesita el valor Employee.EmployeeID, aunque este valor

cambia a medida que el Motor de base de datos de SQL Server examina diferentes filas de Employee.

Una subconsulta correlativa se puede usar también en la cláusula HAVING de una consulta externa. En este ejemplo se buscan los modelos cuyo precio máximo essuperior al doble de la media del modelo.

En este ejemplo se utilizan dos subconsultas correlativas para buscar los nombres de los empleados que han vendido un producto específico.

F.Usar GROUP BY

En este ejemplo se busca el total de cada pedido de venta de la base de datos.

USE AdventureWorks2012;

GO

SELECT DISTINCT Name

FROM Production.Product

WHERE ProductModelID IN

(SELECT ProductModelID

FROM Production.ProductModel

WHERE Name LIKE 'Long-Sleeve Logo Jersey%');

GO

USE AdventureWorks2012;

GO

SELECT DISTINCT p.LastName, p.FirstName

FROM Person.Person AS p

JOIN HumanResources.Employee AS e

ON e.BusinessEntityID = p.BusinessEntityID WHERE 5000.00 IN

(SELECT Bonus

FROM Sales.SalesPerson AS sp

WHERE e.BusinessEntityID = sp.BusinessEntityID);

GO

USE AdventureWorks2012;

GO

SELECT p1.ProductModelID

FROM Production.Product AS p1

GROUP BY p1.ProductModelID

HAVING MAX(p1.ListPrice) >= ALL

(SELECT AVG(p2.ListPrice)

FROM Production.Product AS p2

WHERE p1.ProductModelID = p2.ProductModelID);

GO

USE AdventureWorks2012;

GO

SELECT DISTINCT pp.LastName, pp.FirstName

FROM Person.Person pp JOIN HumanResources.Employee e

ON e.BusinessEntityID = pp.BusinessEntityID WHERE pp.BusinessEntityID IN

(SELECT SalesPersonID

FROM Sales.SalesOrderHeader

WHERE SalesOrderID IN

(SELECT SalesOrderID

FROM Sales.SalesOrderDetail

WHERE ProductID IN

(SELECT ProductID

FROM Production.Product p

WHERE ProductNumber = 'BK-M68B-42')));

GO

USE AdventureWorks2012;

GO

SELECT SalesOrderID, SUM(LineTotal) AS SubTotal

FROM Sales.SalesOrderDetail

GROUP BY SalesOrderID

ORDER BY SalesOrderID;

GO

Transact-SQL

Transact-SQL

Transact-SQL

Transact-SQL

Page 100: Microsoft SELECT Transact SQL 2014

Debido a la cláusula GROUP BY, solo se devuelve una fila que contiene la suma de todas las ventas por cada pedido de venta.

G.Usar GROUP BY con varios grupos

En este ejemplo se busca el precio medio y la suma de las ventas anuales hasta la fecha, agrupados por Id. de producto e Id. de oferta especial.

H.Usar GROUP BY y WHERE

En el siguiente ejemplo se colocan los resultados en grupos después de recuperar únicamente las filas con precios superiores a $1000.

I.Usar GROUP BY con una expresión

En este ejemplo se agrupa por una expresión. Puede agrupar por una expresión si ésta no incluye funciones de agregado.

J.Usar GROUP BY con ORDER BY

En este ejemplo se busca el precio medio de cada tipo de producto y se ordenan los resultados por precio medio.

K.Usar la cláusula HAVING

En el primer ejemplo se muestra una cláusula HAVING con una función de agregado. Agrupa las filas de la tabla SalesOrderDetail por Id. de producto y elimina

aquellos productos cuyas cantidades de pedido medias son cinco o menos. En el segundo ejemplo se muestra una cláusula HAVING sin funciones de agregado.

USE AdventureWorks2012;

GO

SELECT ProductID, SpecialOfferID, AVG(UnitPrice) AS [Average Price],

SUM(LineTotal) AS SubTotal

FROM Sales.SalesOrderDetail

GROUP BY ProductID, SpecialOfferID

ORDER BY ProductID;

GO

USE AdventureWorks2012;

GO

SELECT ProductModelID, AVG(ListPrice) AS [Average List Price]

FROM Production.Product

WHERE ListPrice > $1000

GROUP BY ProductModelID

ORDER BY ProductModelID;

GO

USE AdventureWorks2012;

GO

SELECT AVG(OrderQty) AS [Average Quantity],

NonDiscountSales = (OrderQty * UnitPrice)

FROM Sales.SalesOrderDetail

GROUP BY (OrderQty * UnitPrice)

ORDER BY (OrderQty * UnitPrice) DESC;

GO

USE AdventureWorks2012;

GO

SELECT ProductID, AVG(UnitPrice) AS [Average Price]

FROM Sales.SalesOrderDetail

WHERE OrderQty > 10

GROUP BY ProductID

ORDER BY AVG(UnitPrice);

GO

USE AdventureWorks2012;

GO

Transact-SQL

Transact-SQL

Transact-SQL

Transact-SQL

Transact-SQL

Page 101: Microsoft SELECT Transact SQL 2014

En esta consulta se utiliza la cláusula LIKE en la cláusula HAVING.

L.Usar HAVING y GROUP BY

En el siguiente ejemplo se muestra el uso de las cláusulas GROUP BY, HAVING, WHERE y ORDER BY en una instrucción SELECT. Genera grupos y valores de resumen pero lo

hace tras eliminar los productos cuyos precios superan los 25 $ y cuyas cantidades de pedido medias son inferiores a 5. También organiza los resultados porProductID.

M.Usar HAVING con SUM y AVG

En el siguiente ejemplo se agrupa la tabla SalesOrderDetail por Id. de producto y solo se incluyen aquellos grupos de productos cuyos pedidos suman más de$1000000.00 y cuyas cantidades de pedido medias son inferiores a 3.

Para ver los productos cuyas ventas totales son superiores a $2000000.00, utilice esta consulta:

Si desea asegurarse de que hay al menos mil quinientos elementos para los cálculos de cada producto, use HAVING COUNT(*) > 1500 para eliminar los productos que

devuelven totales inferiores a 1500 elementos vendidos. La consulta sería la siguiente:

SELECT ProductID

FROM Sales.SalesOrderDetail

GROUP BY ProductID

HAVING AVG(OrderQty) > 5

ORDER BY ProductID;

GO

USE AdventureWorks2012 ;

GO

SELECT SalesOrderID, CarrierTrackingNumber

FROM Sales.SalesOrderDetail

GROUP BY SalesOrderID, CarrierTrackingNumber

HAVING CarrierTrackingNumber LIKE '4BD%'

ORDER BY SalesOrderID ;

GO

USE AdventureWorks2012;

GO

SELECT ProductID

FROM Sales.SalesOrderDetail

WHERE UnitPrice < 25.00

GROUP BY ProductID

HAVING AVG(OrderQty) > 5

ORDER BY ProductID;

GO

USE AdventureWorks2012;

GO

SELECT ProductID, AVG(OrderQty) AS AverageQuantity, SUM(LineTotal) AS Total

FROM Sales.SalesOrderDetail

GROUP BY ProductID

HAVING SUM(LineTotal) > $1000000.00

AND AVG(OrderQty) < 3;

GO

USE AdventureWorks2012;

GO

SELECT ProductID, Total = SUM(LineTotal)

FROM Sales.SalesOrderDetail

GROUP BY ProductID

HAVING SUM(LineTotal) > $2000000.00;

GO

USE AdventureWorks2012;

GO

SELECT ProductID, SUM(LineTotal) AS Total

FROM Sales.SalesOrderDetail

GROUP BY ProductID

HAVING COUNT(*) > 1500;

GO

Transact-SQL

Transact-SQL

Transact-SQL

Transact-SQL

Page 102: Microsoft SELECT Transact SQL 2014

N.Usar la sugerencia del optimizador INDEX

En el ejemplo siguiente se muestran dos formas de usar la sugerencia del optimizador INDEX. En el primer ejemplo se muestra cómo obligar al optimizador a que useun índice no clúster para recuperar filas de una tabla, mientras que en el segundo ejemplo se obliga a realizar un recorrido de tabla mediante un índice igual a 0.

M.Usar OPTION y las sugerencias GROUP

En el ejemplo siguiente se muestra cómo se usa la cláusula OPTION (GROUP) con una cláusula GROUP BY.

O.Usar la sugerencia de consulta UNION

En el ejemplo siguiente se usa la sugerencia de consulta MERGE UNION.

P.Usar una instrucción UNION simple

En el ejemplo siguiente, el conjunto de resultados incluye el contenido de las columnas ProductModelID y Name de las tablas ProductModel y Gloves.

USE AdventureWorks2012;

GO

SELECT pp.FirstName, pp.LastName, e.NationalIDNumber

FROM HumanResources.Employee AS e WITH (INDEX(AK_Employee_NationalIDNumber))

JOIN Person.Person AS pp on e.BusinessEntityID = pp.BusinessEntityID

WHERE LastName = 'Johnson';

GO

-- Force a table scan by using INDEX = 0.

USE AdventureWorks2012;

GO

SELECT pp.LastName, pp.FirstName, e.JobTitle

FROM HumanResources.Employee AS e WITH (INDEX = 0) JOIN Person.Person AS pp

ON e.BusinessEntityID = pp.BusinessEntityID

WHERE LastName = 'Johnson';

GO

USE AdventureWorks2012;

GO

SELECT ProductID, OrderQty, SUM(LineTotal) AS Total

FROM Sales.SalesOrderDetail

WHERE UnitPrice < $5.00

GROUP BY ProductID, OrderQty

ORDER BY ProductID, OrderQty

OPTION (HASH GROUP, FAST 10);

GO

USE AdventureWorks2012;

GO

SELECT BusinessEntityID, JobTitle, HireDate, VacationHours, SickLeaveHours

FROM HumanResources.Employee AS e1

UNION

SELECT BusinessEntityID, JobTitle, HireDate, VacationHours, SickLeaveHours

FROM HumanResources.Employee AS e2

OPTION (MERGE UNION);

GO

USE AdventureWorks2012;

GO

IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL

DROP TABLE dbo.Gloves;

GO

-- Create Gloves table.

SELECT ProductModelID, Name

INTO dbo.Gloves

FROM Production.ProductModel

WHERE ProductModelID IN (3, 4);

GO

Transact-SQL

Transact-SQL

Transact-SQL

Transact-SQL

Page 103: Microsoft SELECT Transact SQL 2014

Q.Usar SELECT INTO con UNION

En el ejemplo siguiente, la cláusula INTO de la segunda instrucción SELECT especifica que la tabla denominada ProductResults contiene el conjunto final de resultados

de la unión de las columnas designadas de las tablas ProductModel y Gloves. Tenga en cuenta que la tabla Gloves se crea en la primera instrucción SELECT.

R.Usar UNION con dos instrucciones SELECT y ORDER BY

El orden de algunos parámetros empleados con la cláusula UNION es importante. En el ejemplo siguiente se muestra el uso correcto e incorrecto de UNION en dos

instrucciones SELECT en las que se va a cambiar el nombre de una columna en el resultado.

-- Here is the simple union.

USE AdventureWorks2012;

GO

SELECT ProductModelID, Name

FROM Production.ProductModel

WHERE ProductModelID NOT IN (3, 4)

UNION

SELECT ProductModelID, Name

FROM dbo.Gloves

ORDER BY Name;

GO

USE AdventureWorks2012;

GO

IF OBJECT_ID ('dbo.ProductResults', 'U') IS NOT NULL

DROP TABLE dbo.ProductResults;

GO

IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL

DROP TABLE dbo.Gloves;

GO

-- Create Gloves table.

SELECT ProductModelID, Name

INTO dbo.Gloves

FROM Production.ProductModel

WHERE ProductModelID IN (3, 4);

GO

USE AdventureWorks2012;

GO

SELECT ProductModelID, Name

INTO dbo.ProductResults

FROM Production.ProductModel

WHERE ProductModelID NOT IN (3, 4)

UNION

SELECT ProductModelID, Name

FROM dbo.Gloves;

GO

SELECT ProductModelID, Name

FROM dbo.ProductResults;

USE AdventureWorks2012;

GO

IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL

DROP TABLE dbo.Gloves;

GO

-- Create Gloves table.

SELECT ProductModelID, Name

INTO dbo.Gloves

FROM Production.ProductModel

WHERE ProductModelID IN (3, 4);

GO

/* INCORRECT */

USE AdventureWorks2012;

GO

SELECT ProductModelID, Name

FROM Production.ProductModel

WHERE ProductModelID NOT IN (3, 4)

ORDER BY Name

UNION

SELECT ProductModelID, Name

FROM dbo.Gloves;

GO

/* CORRECT */

Transact-SQL

Transact-SQL

Page 104: Microsoft SELECT Transact SQL 2014

S.Usar UNION de tres instrucciones SELECT para mostrar los efectos de ALL y los paréntesis

En los siguientes ejemplos se utiliza UNION para combinar los resultados de tres tablas que tienen las mismas 5 filas de datos. En el primer ejemplo se utiliza UNION ALL

para mostrar los registros duplicados y se devuelven las 15 filas. En el segundo ejemplo se utiliza UNION sin ALL para eliminar las filas duplicadas de los resultados

combinados de las tres instrucciones SELECT y se devuelven 5 filas.

En el tercer ejemplo se utiliza ALL con el primer UNION y los paréntesis incluyen al segundo UNION que no utiliza ALL. El segundo UNION se procesa en primer lugar

porque se encuentra entre paréntesis. Devuelve 5 filas porque no se utiliza la opción ALL y se quitan los duplicados. Estas 5 filas se combinan con los resultados del

primer SELECT mediante las palabras clave UNION ALL. Esto no quita los duplicados entre los dos conjuntos de 5 filas. El resultado final es de 10 filas.

USE AdventureWorks2012;

GO

SELECT ProductModelID, Name

FROM Production.ProductModel

WHERE ProductModelID NOT IN (3, 4)

UNION

SELECT ProductModelID, Name

FROM dbo.Gloves

ORDER BY Name;

GO

USE AdventureWorks2012;

GO

IF OBJECT_ID ('dbo.EmployeeOne', 'U') IS NOT NULL

DROP TABLE dbo.EmployeeOne;

GO

IF OBJECT_ID ('dbo.EmployeeTwo', 'U') IS NOT NULL

DROP TABLE dbo.EmployeeTwo;

GO

IF OBJECT_ID ('dbo.EmployeeThree', 'U') IS NOT NULL

DROP TABLE dbo.EmployeeThree;

GO

SELECT pp.LastName, pp.FirstName, e.JobTitle

INTO dbo.EmployeeOne

FROM Person.Person AS pp JOIN HumanResources.Employee AS e

ON e.BusinessEntityID = pp.BusinessEntityID

WHERE LastName = 'Johnson';

GO

SELECT pp.LastName, pp.FirstName, e.JobTitle

INTO dbo.EmployeeTwo

FROM Person.Person AS pp JOIN HumanResources.Employee AS e

ON e.BusinessEntityID = pp.BusinessEntityID

WHERE LastName = 'Johnson';

GO

SELECT pp.LastName, pp.FirstName, e.JobTitle

INTO dbo.EmployeeThree

FROM Person.Person AS pp JOIN HumanResources.Employee AS e

ON e.BusinessEntityID = pp.BusinessEntityID

WHERE LastName = 'Johnson';

GO

-- Union ALL

SELECT LastName, FirstName, JobTitle

FROM dbo.EmployeeOne

UNION ALL

SELECT LastName, FirstName ,JobTitle

FROM dbo.EmployeeTwo

UNION ALL

SELECT LastName, FirstName,JobTitle

FROM dbo.EmployeeThree;

GO

SELECT LastName, FirstName,JobTitle

FROM dbo.EmployeeOne

UNION

SELECT LastName, FirstName, JobTitle

FROM dbo.EmployeeTwo

UNION

SELECT LastName, FirstName, JobTitle

FROM dbo.EmployeeThree;

GO

SELECT LastName, FirstName,JobTitle

FROM dbo.EmployeeOne

UNION ALL

(

SELECT LastName, FirstName, JobTitle

FROM dbo.EmployeeTwo

UNION

SELECT LastName, FirstName, JobTitle

FROM dbo.EmployeeThree

);

Transact-SQL

Page 106: Microsoft SELECT Transact SQL 2014