SqlServer2008实例05SELECT子句技术

目录

1.使用DISTINCT消除重复值

2.在聚合函数中使用DISTINCT

3.使用列别名

4.使用SELECT创建脚本

5.字符串拼接

6.使用SELECT创建逗号分隔的列表

7.使用INTO子句

---

1.使用DISTINCT消除重复值

SELECT DISTINCT HireDate
FROM HumanResources.Employee

2.在聚合函数中使用DISTINCT

SELECT AVG(ListPrice)
FROM Production.Product


SELECT AVG(DISTINCT ListPrice)
FROM Production.Product

3.使用列别名

SELECT Color as 'Grouped Color',
       AVG(DISTINCT ListPrice) as 'Average Distinct List Price',
       AVG(ListPrice) 'Average List Price'
FROM Production.product
GROUP BY Color

4.使用SELECT创建脚本

SELECT column_name + 'IS NULL AND'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='Employee'
ORDER BY ORDINAL_POSITION

5.字符串拼接

SELECT 'The' +p.name+'is only' +CONVERT(varchar(25),p.ListPrice)+'!'
FROM Production.Product p
WHERE p.ListPrice BETWEEN 100 AND 120
ORDER BY p.ListPrice

6.使用SELECT创建逗号分隔的列表

DECLARE @Shifts varchar(20)=''

SELECT @Shifts=@Shifts+s.Name+','
FROM HumanResources.Shift s
ORDER BY s.EndTime

SELECT @Shifts

7.使用INTO子句

SELECT CustomerID,Name,SalesPersonID,DemoGraphics
INTO Store_Archive
FROM Sales.Store


SELECT CustomerID,Name,SalesPersonID,DemoGraphics
INTO Store_Archive_2
FROM Sales.Store
WHERE 1=0