[MSSQL] - SELECT语句使用大全

SELECT语句使用大全

虽然 SELECT 语句的完整语法比较复杂，但是大多数 SELECT 语句都描述结果集的四个主要属性

1、结果集中的列的数量和属性。

2、从中检索结果集数据的表，以及这些表之间的所有逻辑关系。

3、为了符合 SELECT 语句的要求，源表中的行所必须达到的条件。不符合条件的行会被忽略。

4、结果集的行的排列顺序。

它的主要子句可归纳如下：

SELECT select_list --描述结果集的列

INTO new_table_name --指定使用结果集来创建新表

FROM table_list --包含从中检索到结果集数据的表的列表[返回结果集的对象]。

[ WHERE search_conditions ] --WHERE 子句是一个筛选，它定义了源表中的行要满足 SELECT 语句的要求所必须达到的条件

[ GROUP BY group_by_list ] --根据 group_by_list 列中的值将结果集分成组[ HAVING search_conditions ] --结果集的附加筛选

[ ORDER BY order_list [ ASC | DESC ] ] --结果集的附加筛选

一、使用选择列表

1、使用 *号来选择所有列；使用“[表名|别名]。[字段]”选取特定的列。

2、AS 子句可用来更改结果集列的名称或为派生列分配名称，也可以使用空格代替

如: SELECT Name AS Name1,Name Name2 FROM Product ORDER BY Name ASC

3、使用 DISTINCT 消除重复项

如：select distinct [Year] from A

4、使用 TOP 和 PERCENT 限制结果集数量

TOP ( expression ) [ PERCENT ] [ WITH TIES ] --expression 数量、PERCENT按百分比返回数据、WITH TIES返回排序与最后一行并列的行。

如：获取成绩前三名的同学

select top 3 * from Score order by Num desc --不考虑成绩并列

select top 3 WITH TIES * from Score order by Num desc --可解决成绩并列的问题

5、选择列表中的计算值

选择的列不但可以包括数据表列，还可以包括计算值，这些结果集列被称为派生列。

计算并且包括以下运算：

对数值列或常量使用算术运算符或函数进行的计算和运算。如SUM()，

COUNT(),AVG()等。

数据类型转换.如CAST(ProductID AS VARCHAR(10)) 。

CASE 函数。如

select ID,[name],Case Sex when 'm' then '男' else '女' end from Student

--根据SEX的值输出性别信息

6、子查询。

select ID,[name],(Select(sum) from Score S where S.SID=A.ID ) AllScore from Student A

--获取学生的基本信息和总成绩。

7、使用INTO。使用INTO 将会把选择的数据插入到指定的表中而不返回数据集。

如： select ID,[name],(Select(sum) from Score S where S.SID=A.ID ) INTO #T Student

--将查询的结果装入临时表T中。

二、使用 FROM 子句

1、使用表别名

SELECT 语句的可读性可通过为表指定别名来提高，别名也称为相关名称或范围变量。分配表别名时，可以使用 AS 关键字，也可以不使用： table_name AS table alias 或 table_name table_alias

2、使用 PIVOT 和 UNPIVOT [SQL2005有效]

可以使用 PIVOT 和 UNPIVOT 关系运算符对表值表达式进行操作以获得另一个表。PIVOT 通过将表达式某一列中的唯一值转换为输出中的多个列来转换表值表达式，并在必要时对最终输出中所需的任何其余的列值执行聚合。UNPIVOT 与 PIVOT 执行相反的操作，将表值表达式的列转换为列值。

PIVOT示例：

SELECT * FROM [StuSources] pivot(sum(chengji) for kecheng in([语文],[数学],[历史])) as prv --将行转换为列SELECT VendorID, Employee, Orders FROM pvtUNPIVOT (Orders FOR Employee IN (Emp1, Emp2, Emp3, Emp4, Emp5))AS unpvt --将列转换为行er

3、使用 APPLY

使用 APPLY 运算符可以为实现查询操作的外部表表达式返回的每个行调用表值函数。表值函数作为右输入，外部表表达式作为左输入。通过对右输入求值来获得左输入每一行的计算结果，生成的行被组合起来作为最终输出。

APPLY 有两种形式： CROSS APPLY 和 OUTER APPLY。CROSS APPLY 仅返回外部表中通过表值函数生成结果集的行。OUTER APPLY 既返回生成结果集的

行，也返回不生成结果集的行，其中表值函数生成的列中的值为 NULL。

如：

--Create Employees table and insert values

CREATE TABLE Employees

(

empid int NOT NULL, --员工编号

mgrid int NULL,--经理编号

empname varchar(25) NOT NULL,--姓名

salary money NOT NULL,--薪水

CONSTRAINT PK_Employees PRIMARY KEY(empid),

)

GO

INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)

INSERT INTO Employees VALUES(2 , 1, 'Andrew' , $5000.00)

INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)

INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)

INSERT INTO Employees VALUES(5 , 2, 'Steven' , $2500.00)

INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)

INSERT INTO Employees VALUES(7 , 3, 'Robert' , $2500.00)

INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)

INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)

INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)

INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)

INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)

INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)

INSERT INTO Employees VALUES(14, 11 , 'James', $1500.00)

GO

--Create Departments table and insert values

CREATE TABLE Departments

(

deptid INT NOT NULL PRIMARY KEY,

deptname VARCHAR(25) NOT NULL,

deptmgrid INT NULL REFERENCES Employees

)

GO

INSERT INTO Departments VALUES(1, 'HR', 2)