SQL查询技巧详解-优快云博客

本文链接：https://blog.youkuaiyun.com/Wgf2006/article/details/1785141

虽然 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 --将列转换为行

         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),

)

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)

--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

)

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

INSERT INTO Departments VALUES(2, 'Marketing', 7)

INSERT INTO Departments VALUES(3, 'Finance', 8)

INSERT INTO Departments VALUES(4, 'R&D', 9)

INSERT INTO Departments VALUES(5, 'Training', 4)

INSERT INTO Departments VALUES(6, 'Gardening', NULL)

---若要返回每个部门经理的所有级别的全部下属，请使用下面的查询：

CREATE FUNCTION dbo.fn_getsubtree(@empid AS INT) RETURNS @TREE TABLE

(

empid INT NOT NULL,

empname VARCHAR(25) NOT NULL,

mgrid INT NULL,

lvl INT NOT NULL

)

BEGIN

WITH Employees_Subtree(empid, empname, mgrid, lvl)

(

-- Anchor Member (AM)

SELECT empid, empname, mgrid, 0

FROM employees

WHERE empid = @empid

UNION all

-- Recursive Member (RM)

SELECT e.empid, e.empname, e.mgrid, es.lvl+1

FROM employees AS e

JOIN employees_subtree AS es

ON e.mgrid = es.empid

)

INSERT INTO @TREE

SELECT * FROM Employees_Subtree

RETURN

END

SELECT *

FROM Departments AS D

CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST

三、使用WHERE 和 HAVING 筛选行
         WHERE 和 HAVING 子句可以控制用于生成结果集的源表中的行。WHERE 和 HAVING 是筛选器。这两个子句指定一系列搜索条件，只有那些满足搜索条件的行才用于生成结果集。
        HAVING 子句通常与 GROUP BY 子句一起使用以筛选聚合值结果。但是 HAVING 也可以在不使用 GROUP BY 的情况下单独指定。HAVING 子句指定在应用 WHERE 子句筛选器后要进一步应用的筛选器。这些筛选器可以应用于 SELECT 列表中所用的聚合函数。
         1、比较搜索条件。
         2、范围搜索条件。[NOT ] BETWEEN 范围搜索返回介于两个指定值之间的所有值
           SELECT * FROM A WHERE Acount Between 10 AND 20
         3、列表搜索条件。IN 关键字使您可以选择与列表中的任意值匹配的行。
           SELECT ProductID, [Name] FROM Product WHERE CategoryID IN (12, 14, 16)
          4、搜索条件中的模式匹配。LIKE 关键字搜索与指定模式匹配的字符串、日期或时间值。
                    % 包含零个或多个字符的任意字符串。
                    _ 任何单个字符。
                    [ ] 指定范围（例如 [a-f]）或集合（例如 [abcdef]）内的任何单个字符。
                    [^] 不在指定范围（例如 [^a - f]）或集合（例如 [^abcdef]）内的任何单个字符。
          5、NULL比较搜索条件。
               NULL比较行为取决于 SET ANSI_NULLS设置，当 SET ANSI_NULLS 为 ON 时，如果比较中有一个或多个表达式为 NULL，则既不输出 TRUE 也不输出 FALSE，而是输出 UNKNOWN。此时需要使用 IS [NOT] NULL 子句测试 NULL 值。
             当 ANSI_NULLS 为 OFF 时，如果 ColumnA 包含空值，则比较操作 ColumnA = NULL 返回 TRUE；如果 ColumnA 除包含 NULL 外还包含某些值，则比较操作返回 FALSE。此外，两个都取空值的表达式的比较也输出 TRUE
          6、所有记录（=ALL、>ALL、<= ALL、ANY）。
                    select * from A where Amount=Any(select amount from A where [Year]=2001) and [Year]<>2001
          7、逻辑运算符。逻辑运算符包括 AND、OR 和 NOT。逻辑运算符的优先顺序为NOT、AND 和 OR。

四、使用 GROUP BY 分组行
        GROUP BY 子句用来为结果集中的每一行产生聚合值。如果聚合函数没有使用 GROUP BY 子句，则只为 SELECT 语句报告一个聚合值。
        WHERE 搜索条件在进行分组操作之前应用；而 HAVING 搜索条件在进行分组操作之后应用。HAVING 语法与 WHERE 语法类似，但 HAVING 可以包含聚合函数。HAVING 子句可以引用选择列表中显示的任意项。
         select [Year],Sum(AMount) AMount from A Group By [Year] --按年份分组统计销量

五、用 ORDER BY 对行进行排序
ORDER BY 子句按一列或多列（最多 8,060 个字节）对查询结果进行排序。排序可以是升序的 (ASC)，也可以是降序的 (DESC)。如果未指定是升序还是降序，就假定为 ASC。

六、子查询
        子查询是一个嵌套在 SELECT、INSERT、UPDATE 或 DELETE 语句或其他子查询中的查询。任何允许使用表达式的地方都可以使用子查询。子查询也称为内部查询或内部选择，而包含子查询的语句也称为外部查询或外部选择。
          1、使用别名的子查询
              当表进行自联接或需要引入外表表列与本表列名相同时需要使用表别名
          2、使用 [NOT] IN 的子查询

USE AdventureWorks;

SELECT Name

FROM Production.Product

WHERE ProductSubcategoryID IN

(SELECT ProductSubcategoryID

FROM Production.ProductSubcategory

WHERE Name = 'Wheels')

3、UPDATE、DELETE 和 INSERT 语句中的子查询
如：联表更新

UPDATE Production.Product

SET ListPrice = ListPrice * 2

FROM Production.Product AS p

INNER JOIN Purchasing.ProductVendor AS pv

ON p.ProductID = pv.ProductID AND pv.VendorID = 51;

4、使用比较运算符的子查询

SELECT CustomerID

FROM Sales.Customer

WHERE TerritoryID =

(SELECT TerritoryID

FROM Sales.SalesPerson

WHERE SalesPersonID = 276)

5、使用 [NOT] EXISTS 的子查询
6、用于替代表达式的子查询

SELECT Name, ListPrice,

(SELECT AVG(ListPrice) FROM Production.Product) AS Average,

ListPrice - (SELECT AVG(ListPrice) FROM Production.Product)

AS Difference

FROM Production.Product

WHERE ProductSubcategoryID = 1

七、表联接
通过联接，可以从两个或多个表中根据各个表之间的逻辑关系来检索数据。
联接条件可通过以下方式定义两个表在查询中的关联方式：
指定每个表中要用于联接的列。典型的联接条件在一个表中指定一个外键，而在另一个表中指定与其关联的键。
指定用于比较各列的值的逻辑运算符（例如 = 或 <>）。
         1、内联接[INNER] JOIN.
               仅当两个表中都至少有一个行符合联接条件时，内部联接才返回行,内部联接消除了与另一个表中的行不匹配的行.
         2、外连接 {LEFT |RIGHT|FULL} OUTER JOIN
               外部联接会返回 FROM 子句中提到的至少一个表或视图中的所有行，只要这些行符合任何 WHERE 或 HAVING 搜索条件。将检索通过左外部联接引用的左表中的所有行，以及通过右外部联接引用的右表中的所有行。在完全外部联接中，将返回两个表的所有行
                    其分为：
                    左外部联接，数据列表包括了满足查询条件的左边表的所有行。
                    右外部联接，数据列表包括了满足查询条件的右边表的所有行。
                    完全外部联接，数据包含了所有满足查询条件的列。
          3、交叉联接CROSS JOIN
                    返回满足查询条件记录的笛卡尔积运算的集合（N×M）。
          4、自联接
                    表可以通过自联接与自身联接。
          5、多表联接

SELECT p.Name, v.Name

FROM Production.Product p

JOIN Purchasing.ProductVendor pv

ON p.ProductID = pv.ProductID

JOIN Purchasing.Vendor v

ON pv.VendorID = v.VendorID

WHERE ProductSubcategoryID = 15

ORDER BY v.Name

6、NULL和联接
联接表的列中的空值（如果有）互相不匹配。如果其中一个联接表的列中出现空值，只能通过外部联接返回这些空值（除非 WHERE 子句不包括空值）。

八、结果集的操作
          1、[ALL] UNION 运算符组合结果集。
                UNION 运算符使您得以将两个或多个 SELECT 语句的结果组合成一个结果集。使用 UNION 运算符组合的结果集都必须具有相同的结构。而且它们的列数必须相同，并且相应的结果集列的数据类型必须兼容。
                默认情况下，UNION 运算符将从结果集中删除重复的行。如果使用 ALL 关键字，那么结果中将包含所有行而不删除重复的行。
           2、EXCEPT 和 INTERSECT 执行半联接操作
            EXCEPT 和 INTERSECT 运算符使您可以比较两个或多个 SELECT 语句的结果并返回非重复值。EXCEPT 运算符返回由 EXCEPT 运算符左侧的查询返回、而又不包含在右侧查询所返回的值中的所有非重复值。
            INTERSECT 返回由 INTERSECT 运算符左侧和右侧的查询都返回的所有非重复值。使用 EXCEPT 或 INTERSECT 比较的结果集必须具有相同的结构。
           3、公用表表达式 (CTE) 递归查询
                 公用表表达式 (CTE) 具有一个重要的优点，那就是能够引用其自身，从而创建递归 CTE。递归 CTE 是一个重复执行初始 CTE 以返回数据子集直到获取完整结果集的公用表表达式。
            递归 CTE 由下列三个元素组成：
                      1、例程的调用：递归 CTE 的第一个调用包括一个或多个由 UNION ALL、UNION、EXCEPT 或 INTERSECT 运算符联接的 CTE_query_definitions。由于这些查询定义形成了 CTE 结构的基准结果集，所以它们被称为“定位点成员”。
                      2、例程的递归调用。递归调用包括一个或多个由引用 CTE 本身的 UNION ALL 运算符联接的 CTE_query_definitions。这些查询定义被称为“递归成员”。
                      3、终止检查。终止检查是隐式的；当上一个调用中未返回行时，递归将停止。
  示例：

--创建表

CREATE TABLE T (id INT identity(1,1), [NAME] VARCHAR(100), FathID int)

--插入测试数据

Insert t([Name],FathID) VALUES('A',0) --1

Insert t([Name],FathID) VALUES('B',0)

Insert t([Name],FathID) VALUES( 'C',0)

Insert t([Name],FathID) VALUES('Aa',1) --4

Insert t([Name],FathID) VALUES('Ab',1) --5

Insert t([Name],FathID) VALUES('Ac',1)

Insert t([Name],FathID) VALUES('Ba',2)

Insert t([Name],FathID) VALUES('Bb',2)

Insert t([Name],FathID) VALUES('Aaa',4)

Insert t([Name],FathID) VALUES('Aab',4)

Insert t([Name],FathID) VALUES('ABa',5)

Insert t([Name],FathID) VALUES('ABb',5)

WITH CN (ID,[Name],FathID,Lev)

(

--例程的调用,定位点成员

select A.ID,A.[Name],FathID,1 LEV

FROM T A

WHERE A.FATHid=0 and [Name]='A'

UNION ALL

--递归调用,递归成员

select a.ID,a.[Name],a.FathID, (LEV+1) LEV

FROM T A JOIN CN c ON A.FATHID=C.ID

WHERE A.FATHID=C.ID

)

--测试调用

SELECT * FROM CN order by Lev

--删除表

DROP TABLE t

4、汇总数据
          1、CUBE 汇总数据。 CUBE 运算符生成的结果集是多维数据集。多维数据集是事实数据的扩展。扩展是基于用户要分析的列建立的。这些列称为维度
             如：根据区域（AreaID）汇总各商品（ProductID）的销售情况
             SELECT AreaID,ProductID,Sum(Total) FROM CW_ORDERdETAIL where ostate=1   Group BY AreaID ,ProductID with cube
          2、ROLLUP汇总数据。ROLLUP 运算符生成的结果集类似于 CUBE 运算符生成的结果集。CUBE 生成的结果集显示了所选列中值的所有组合的聚合；ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。
          3、COMPUTE 和 COMPUTE BY 汇总数据
                     COMPUTE BY 子句使您得以用同一 SELECT 语句既查看明细行，又查看汇总行。可以计算子组的汇总值，也可以计算整个结果集的汇总值。
          3、FOR子句
                      FOR 子句用来指定 BROWSE 或 XML 选项
                      SELECT id,name FROM content FOR XML AUTO,XMLDATA --

SELECT语句使用大全