ejemplos de select (transact-sql)

Ejemplos de SELECT (Transact-SQL)

http://msdn.microsoft.com/es-es/library/ms187731.aspx[01/12/2012 0:00:48]

Pgina de inicio Aprender DescargasBiblioteca Iniciar sesin | United States - English | |

Ms...

Contenido de la comunidad

Agregue ejemplos de cdigo ysugerencias para mejorar este tema.

MSDN LibrarySQL ServerSQL Server 2012Documentacin del productoLibros en pantalla de SQL Server 2012Motor de base de datosReferencia de Transact-SQL (motor de base de Data Manipulation Language (DML) Statements

SELECT (Transact-SQL)SELECT (clusula de Transact-SQL)Ejemplos de SELECT (Transact-SQL)FOR (clusula de Transact-SQL)GROUP BY (Transact-SQL)HAVING (Transact-SQL)INTO (clusula de Transact-SQL)ORDER BY (clusula de Transact-SQL)OVER (clusula de Transact-SQL)

Traduccin Original

SQL Server 2012 Otras versionesPersonas que lo han encontrado til: 1 de 2 - Valorar este tema

Este artculo se tradujo de forma manual. Muevael puntero sobre las frases del artculo para ver eltexto traducido.

SELECT Examples (Transact-SQL)

This topic provides examples of using the SELECT statement.

The following example shows three code examples. This first code examplereturns all rows (no WHERE clause is specified) and all columns (using the *)from the Product table in the AdventureWorks2012 database.

This example returns all rows (no WHERE clause is specified), and only a subsetof the columns (Name, ProductNumber, ListPrice) from the Product table inthe AdventureWorks2012 database. Additionally, a column heading is added.

This example returns only the rows for Product that have a product line of Rand that have days to manufacture that is less than 4.

A.Using SELECT to retrieve rows and columns

USE AdventureWorks2012;GOSELECT *FROM Production.ProductORDER BY Name ASC;-- Alternate way.USE AdventureWorks2012;GOSELECT p.*FROM Production.Product AS pORDER BY Name ASC;GO

USE AdventureWorks2012;GOSELECT Name, ProductNumber, ListPrice AS PriceFROM Production.Product ORDER BY Name ASC;GO

USE AdventureWorks2012;GOSELECT Name, ProductNumber, ListPrice AS PriceFROM Production.Product WHERE ProductLine = 'R'

Transact-SQL

Transact-SQL

Transact-SQL



The following examples return all rows from the Product table. The firstexample returns total sales and the discounts for each product. In the secondexample, the total revenue is calculated for each product.

This is the query that calculates the revenue for each product in each salesorder.

The following example uses DISTINCT to prevent the retrieval of duplicate titles.

B.Using SELECT with column headings and calculations

C.Using DISTINCT with SELECT

AND DaysToManufacture < 4ORDER BY Name ASC;GO

USE AdventureWorks2012;GOSELECT p.Name AS ProductName, NonDiscountSales = (OrderQty * UnitPrice),Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount)FROM Production.Product AS p INNER JOIN Sales.SalesOrderDetail AS sodON p.ProductID = sod.ProductID ORDER BY ProductName DESC;GO

USE AdventureWorks2012;GOSELECT 'Total income is', ((OrderQty * UnitPrice) * (1.0 - UnitPriceDiscount)), ' for ',p.Name AS ProductName FROM Production.Product AS p INNER JOIN Sales.SalesOrderDetail AS sodON p.ProductID = sod.ProductID ORDER BY ProductName ASC;GO

USE AdventureWorks2012;GOSELECT DISTINCT JobTitleFROM HumanResources.EmployeeORDER BY JobTitle;GO

Transact-SQL

Transact-SQL

Transact-SQL



The following first example creates a temporary table named #Bicycles intempdb.

This second example creates the permanent table NewProducts.

The following example shows queries that are semantically equivalent andillustrates the difference between using the EXISTS keyword and the INkeyword. Both are examples of a valid subquery that retrieves one instance ofeach product name for which the product model is a long sleeve logo jersey,and the ProductModelID numbers match between the Product andProductModel tables.

D.Creating tables with SELECT INTO

E.Using correlated subqueries

USE tempdb;GOIF OBJECT_ID (N'#Bicycles',N'U') IS NOT NULLDROP TABLE #Bicycles;GOSELECT * INTO #BicyclesFROM AdventureWorks2012.Production.ProductWHERE ProductNumber LIKE 'BK%';GO

USE AdventureWorks2012;GOIF OBJECT_ID('dbo.NewProducts', 'U') IS NOT NULL DROP TABLE dbo.NewProducts;GOALTER DATABASE AdventureWorks2012 SET RECOVERY BULK_LOGGED;GO

SELECT * INTO dbo.NewProductsFROM Production.ProductWHERE ListPrice > $25 AND ListPrice < $100;GOALTER DATABASE AdventureWorks2012 SET RECOVERY FULL;GO

USE AdventureWorks2012;GOSELECT DISTINCT NameFROM 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%');

Transact-SQL

Transact-SQL

Transact-SQL



The following example uses IN in a correlated, or repeating, subquery. This is aquery that depends on the outer query for its values. The query is executedrepeatedly, one time for each row that may be selected by the outer query.This query retrieves one instance of the first and last name of each employeefor which the bonus in the SalesPerson table is 5000.00 and for which theemployee identification numbers match in the Employee and SalesPersontables.

The previous subquery in this statement cannot be evaluated independently ofthe outer query. It requires a value for Employee.EmployeeID, but this valuechanges as the SQL Server Database Engine examines different rows inEmployee.A correlated subquery can also be used in the HAVING clause of an outer query.This example finds the product models for which the maximum list price ismore than twice the average for the model.

This example uses two correlated subqueries to find the names of employees

GO

-- OR

USE AdventureWorks2012;GOSELECT DISTINCT NameFROM Production.ProductWHERE ProductModelID IN (SELECT ProductModelID FROM Production.ProductModel WHERE Name LIKE 'Long-Sleeve Logo Jersey%');GO

USE AdventureWorks2012;GOSELECT 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;GOSELECT p1.ProductModelIDFROM Production.Product AS p1GROUP BY p1.ProductModelIDHAVING MAX(p1.ListPrice) >= ALL (SELECT AVG(p2.ListPrice) FROM Production.Product AS p2 WHERE p1.ProductModelID = p2.ProductModelID);GO

Transact-SQL

Transact-SQL



who have sold a particular product.

The following example finds the total of each sales order in the database.

Because of the GROUP BY clause, only one row containing the sum of all sales isreturned for each sales order.

The following example finds the average price and the sum of year-to-datesales, grouped by product ID and special offer ID.

F.Using GROUP BY

G.Using GROUP BY with multiple groups

H.Using GROUP BY and WHERE

USE AdventureWorks2012;GOSELECT DISTINCT pp.LastName, pp.FirstName FROM Person.Person pp JOIN HumanResources.Employee eON e.BusinessEntityID = pp.BusinessEntityID WHERE pp.BusinessEntityID IN (SELECT SalesPersonID FROM Sales.SalesOrderHeaderWHERE SalesOrderID IN (SELECT SalesOrderID FROM Sales.SalesOrderDetailWHERE ProductID IN (SELECT ProductID FROM Production.Product p WHERE ProductNumber = 'BK-M68B-42')));GO

USE AdventureWorks2012;GOSELECT SalesOrderID, SUM(LineTotal) AS SubTotalFROM Sales.SalesOrderDetailGROUP BY SalesOrderIDORDER BY SalesOrderID;GO

USE AdventureWorks2012;GOSELECT ProductID, SpecialOfferID, AVG(UnitPrice) AS [Average Price], SUM(LineTotal) AS SubTotalFROM Sales.SalesOrderDetailGROUP BY ProductID, SpecialOfferIDORDER BY ProductID;GO

Transact-SQL

Transact-SQL

Transact-SQL



The following example puts the results into groups after retrieving only therows with list prices greater than $1000 .

The following example groups by an expression. You can group by anexpression if the expression does not include aggregate functions.

The following example finds the average price of each type of product andorders the results by average price.

The first example that follows shows a HAVING clause with an aggregatefunction. It groups the rows in the SalesOrderDetail table by product ID andeliminates products whose average order quantities are five or less. The secondexample shows a HAVING clause without aggregate functions.

I.Using GROUP BY with an expression

J.Using GROUP BY with ORDER BY

K.Using the HAVING clause

USE AdventureWorks2012;GOSELECT ProductModelID, AVG(ListPrice) AS [Average List Price]FROM Production.ProductWHERE ListPrice > $1000GROUP BY ProductModelIDORDER BY ProductModelID;GO

USE AdventureWorks2012;GOSELECT AVG(OrderQty) AS [Average Quantity], NonDiscountSales = (OrderQty * UnitPrice)FROM Sales.SalesOrderDetailGROUP BY (OrderQty * UnitPrice)ORDER BY (OrderQty * UnitPrice) DESC;GO

USE AdventureWorks2012;GOSELECT ProductID, AVG(UnitPrice) AS [Average Price]FROM Sales.SalesOrderDetailWHERE OrderQty > 10GROUP BY ProductIDORDER BY AVG(UnitPrice);GO

Transact-SQL

Transact-SQL

Transact-SQL

Transact-SQL



This query uses the LIKE clause in the HAVING clause.

The following example shows using GROUP BY, HAVING, WHERE , and ORDER BYclauses in one SELECT statement. It produces groups and summary values butdoes so after eliminating the products with prices over $25 and average orderquantities under 5. It also organizes the results by ProductID.

The following example groups the SalesOrderDetail table by product ID andincludes only those groups of products that have orders totaling more than$1000000.00 and whose average order quantities are less than 3.

L.Using HAVING and GROUP BY

M.Using HAVING with SUM and AVG

USE AdventureWorks2012;GOSELECT ProductID FROM Sales.SalesOrderDetailGROUP BY ProductIDHAVING AVG(OrderQty) > 5ORDER BY ProductID;GO

USE AdventureWorks2012 ;GOSELECT SalesOrderID, CarrierTrackingNumber FROM Sales.SalesOrderDetailGROUP BY SalesOrderID, CarrierTrackingNumberHAVING CarrierTrackingNumber LIKE '4BD%'ORDER BY SalesOrderID ;GO

USE AdventureWorks2012;GOSELECT ProductID FROM Sales.SalesOrderDetailWHERE UnitPrice < 25.00GROUP BY ProductIDHAVING AVG(OrderQty) > 5ORDER BY ProductID;GO

USE AdventureWorks2012;GOSELECT ProductID, AVG(OrderQty) AS AverageQuantity, SUM(LineTotal) AS TotalFROM Sales.SalesOrderDetailGROUP BY ProductIDHAVING SUM(LineTotal) > $1000000.00AND AVG(OrderQty) < 3;GO

Transact-SQL

Transact-SQL



To see the products that have had total sales greater than $2000000.00, usethis query:

If you want to make sure there are at least one thousand five hundred itemsinvolved in the calculations for each product, use HAVING COUNT(*) > 1500 toeliminate the products that return totals for fewer than 1500 items sold. Thequery looks like this:

The following example shows two ways to use the INDEX optimizer hint. Thefirst example shows how to force the optimizer to use a nonclustered index toretrieve rows from a table, and the second example forces a table scan byusing an index of 0.

N.Using the INDEX optimizer hint

USE AdventureWorks2012;GOSELECT ProductID, Total = SUM(LineTotal)FROM Sales.SalesOrderDetailGROUP BY ProductIDHAVING SUM(LineTotal) > $2000000.00;GO

USE AdventureWorks2012;GOSELECT ProductID, SUM(LineTotal) AS TotalFROM Sales.SalesOrderDetailGROUP BY ProductIDHAVING COUNT(*) > 1500;GO

USE AdventureWorks2012;GOSELECT pp.FirstName, pp.LastName, e.NationalIDNumberFROM HumanResources.Employee AS e WITH (INDEX(AK_Employee_NationalIDNumber))JOIN Person.Person AS pp on e.BusinessEntityID = pp.BusinessEntityIDWHERE LastName = 'Johnson';GO

-- Force a table scan by using INDEX = 0.USE AdventureWorks2012;GOSELECT pp.LastName, pp.FirstName, e.JobTitleFROM HumanResources.Employee AS e WITH (INDEX = 0) JOIN Person.Person AS ppON e.BusinessEntityID = pp.BusinessEntityIDWHERE LastName = 'Johnson';GO

Transact-SQL

Transact-SQL

Transact-SQL



The following example shows how the OPTION (GROUP) clause is used with aGROUP BY clause.

The following example uses the MERGE UNION query hint.

In the following example, the result set includes the contents of theProductModelID and Name columns of both the ProductModel and Glovestables.

M.Using OPTION and the GROUP hints

O.Using the UNION query hint

P.Using a simple UNION

USE AdventureWorks2012;GOSELECT ProductID, OrderQty, SUM(LineTotal) AS TotalFROM Sales.SalesOrderDetailWHERE UnitPrice < $5.00GROUP BY ProductID, OrderQtyORDER BY ProductID, OrderQtyOPTION (HASH GROUP, FAST 10);GO

USE AdventureWorks2012;GOSELECT BusinessEntityID, JobTitle, HireDate, VacationHours, SickLeaveHoursFROM HumanResources.Employee AS e1UNIONSELECT BusinessEntityID, JobTitle, HireDate, VacationHours, SickLeaveHoursFROM HumanResources.Employee AS e2OPTION (MERGE UNION);GO

USE AdventureWorks2012;GOIF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULLDROP TABLE dbo.Gloves;GO-- Create Gloves table.SELECT ProductModelID, NameINTO dbo.GlovesFROM Production.ProductModelWHERE ProductModelID IN (3, 4);GO

Transact-SQL

Transact-SQL

Transact-SQL



In the following example, the INTO clause in the second SELECT statementspecifies that the table named ProductResults holds the final result set of theunion of the designated columns of the ProductModel and Gloves tables. Notethat the Gloves table is created in the first SELECT statement.

The order of certain parameters used with the UNION clause is important. Thefollowing example shows the incorrect and correct use of UNION in two SELECTstatements in which a column is to be renamed in the output.

Q.Using SELECT INTO with UNION

R.Using UNION of two SELECT statements with ORDER BY

-- Here is the simple union.USE AdventureWorks2012;GOSELECT ProductModelID, NameFROM Production.ProductModelWHERE ProductModelID NOT IN (3, 4)UNIONSELECT ProductModelID, NameFROM dbo.GlovesORDER BY Name;GO

USE AdventureWorks2012;GOIF OBJECT_ID ('dbo.ProductResults', 'U') IS NOT NULLDROP TABLE dbo.ProductResults;GOIF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULLDROP TABLE dbo.Gloves;GO-- Create Gloves table.SELECT ProductModelID, NameINTO dbo.GlovesFROM Production.ProductModelWHERE ProductModelID IN (3, 4);GO

USE AdventureWorks2012;GOSELECT ProductModelID, NameINTO dbo.ProductResultsFROM Production.ProductModelWHERE ProductModelID NOT IN (3, 4)UNIONSELECT ProductModelID, NameFROM dbo.Gloves;GO

SELECT ProductModelID, Name FROM dbo.ProductResults;

Transact-SQL



The following examples use UNION to combine the results of three tables thatall have the same 5 rows of data. The first example uses UNION ALL to showthe duplicated records, and returns all 15 rows. The second example usesUNION without ALL to eliminate the duplicate rows from the combined results ofthe three SELECT statements, and returns 5 rows.The third example uses ALL with the first UNION and parentheses enclose thesecond UNION that is not using ALL. The second UNION is processed firstbecause it is in parentheses, and returns 5 rows because the ALL option is notused and the duplicates are removed. These 5 rows are combined with theresults of the first SELECT by using the UNION ALL keywords. This does notremove the duplicates between the two sets of 5 rows. The final result has 10rows.

S.Using UNION of three SELECT statements to show the effects of ALL and parentheses

USE AdventureWorks2012;GOIF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULLDROP TABLE dbo.Gloves;GO-- Create Gloves table.SELECT ProductModelID, NameINTO dbo.GlovesFROM Production.ProductModelWHERE ProductModelID IN (3, 4);GO

/* INCORRECT */USE AdventureWorks2012;GOSELECT ProductModelID, NameFROM Production.ProductModelWHERE ProductModelID NOT IN (3, 4)ORDER BY NameUNIONSELECT ProductModelID, NameFROM dbo.Gloves;GO

/* CORRECT */USE AdventureWorks2012;GOSELECT ProductModelID, NameFROM Production.ProductModelWHERE ProductModelID NOT IN (3, 4)UNIONSELECT ProductModelID, NameFROM dbo.GlovesORDER BY Name;GO

USE AdventureWorks2012;GOIF OBJECT_ID ('dbo.EmployeeOne', 'U') IS NOT NULLDROP TABLE dbo.EmployeeOne;GO

Transact-SQL

Transact-SQL



Reference

CREATE TRIGGER (Transact-SQL)CREATE VIEW (Transact-SQL)DELETE (Transact-SQL)

See Also

IF OBJECT_ID ('dbo.EmployeeTwo', 'U') IS NOT NULLDROP TABLE dbo.EmployeeTwo;GOIF OBJECT_ID ('dbo.EmployeeThree', 'U') IS NOT NULLDROP TABLE dbo.EmployeeThree;GO

SELECT pp.LastName, pp.FirstName, e.JobTitle INTO dbo.EmployeeOneFROM Person.Person AS pp JOIN HumanResources.Employee AS eON e.BusinessEntityID = pp.BusinessEntityIDWHERE LastName = 'Johnson';GOSELECT pp.LastName, pp.FirstName, e.JobTitle INTO dbo.EmployeeTwoFROM Person.Person AS pp JOIN HumanResources.Employee AS eON e.BusinessEntityID = pp.BusinessEntityIDWHERE LastName = 'Johnson';GOSELECT pp.LastName, pp.FirstName, e.JobTitle INTO dbo.EmployeeThreeFROM Person.Person AS pp JOIN HumanResources.Employee AS eON e.BusinessEntityID = pp.BusinessEntityIDWHERE LastName = 'Johnson';GO-- Union ALLSELECT LastName, FirstName, JobTitleFROM dbo.EmployeeOneUNION ALLSELECT LastName, FirstName ,JobTitleFROM dbo.EmployeeTwoUNION ALLSELECT LastName, FirstName,JobTitle FROM dbo.EmployeeThree;GO

SELECT LastName, FirstName,JobTitleFROM dbo.EmployeeOneUNION SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeTwoUNION SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeThree;GO

SELECT LastName, FirstName,JobTitle FROM dbo.EmployeeOneUNION ALL(SELECT LastName, FirstName, JobTitle FROM dbo.EmployeeTwoUNIONSELECT LastName, FirstName, JobTitle FROM dbo.EmployeeThree);GO



2012 Microsoft. Reservados todos los derechos. Trminos de uso | Marcas Registradas | Privacidad |Administre su perfil | MSDN Flash en Espaol | Contacto | Aviso legal | Comentario del sitio

Contenido de la comunidad Agregar Preguntas ms frecuentes

EXECUTE (Transact-SQL)Expressions (Transact-SQL)INSERT (Transact-SQL)LIKE (Transact-SQL)UNION (Transact-SQL)EXCEPT and INTERSECT (Transact-SQL)UPDATE (Transact-SQL)WHERE (Transact-SQL)PathName (Transact-SQL)INTO Clause (Transact-SQL)

Le ha resultado til? S No

microsoft.comEjemplos de SELECT (Transact-SQL)

9saWJyYXJ5L21zMTg3NzMxLmFzcHgA: form1: query: rdIsUseful: 0

9saWJyYXJ5L21zMTg3NzMxLmFzcHgA: TrnaslatorView: 1

ejemplos de select (transact-sql)

Documents