SQL常用语句简单

数据库脚本

USE [Test]
GO
/****** Object:  Table [dbo].[Class]    Script Date: 2017/6/29 13:17:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Class](
    [ClassID] [int] NULL,
    [ClassName] [nvarchar](50) NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Student]    Script Date: 2017/6/29 13:17:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Student](
    [StuID] [int] NOT NULL,
    [StuName] [nvarchar](50) NULL,
    [StuAge] [int] NULL,
    [ClassID] [int] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[Class] ([ClassID], [ClassName]) VALUES (1, N'班级1')
GO
INSERT [dbo].[Class] ([ClassID], [ClassName]) VALUES (2, N'班级2')
GO
INSERT [dbo].[Class] ([ClassID], [ClassName]) VALUES (3, N'班级3')
GO
INSERT [dbo].[Class] ([ClassID], [ClassName]) VALUES (4, N'班级4')
GO
INSERT [dbo].[Class] ([ClassID], [ClassName]) VALUES (5, N'班级5')
GO
INSERT [dbo].[Class] ([ClassID], [ClassName]) VALUES (2, N'班级2')
GO
INSERT [dbo].[Student] ([StuID], [StuName], [StuAge], [ClassID]) VALUES (1, N'张三', 19, 1)
GO
INSERT [dbo].[Student] ([StuID], [StuName], [StuAge], [ClassID]) VALUES (2, N'李四', 18, 1)
GO
INSERT [dbo].[Student] ([StuID], [StuName], [StuAge], [ClassID]) VALUES (3, N'王五', 16, 2)
GO
INSERT [dbo].[Student] ([StuID], [StuName], [StuAge], [ClassID]) VALUES (4, N'啦啦', 20, 7)
GO
INSERT [dbo].[Student] ([StuID], [StuName], [StuAge], [ClassID]) VALUES (5, N'王五2', 16, 2)
GO
INSERT [dbo].[Student] ([StuID], [StuName], [StuAge], [ClassID]) VALUES (5, N'王五3', 16, 2)
GO
INSERT [dbo].[Student] ([StuID], [StuName], [StuAge], [ClassID]) VALUES (5, N'王五3', 16, 2)
GO

View Code

一、排序

　　排序的可以产生序号，所以都可以分页查询

1、row_number() over(order by 字段)

SELECT *,ROW_NUMBER() OVER ( ORDER BY s.StuAge) FROM Student AS s

3    王五     16    2    1
5    王五2    16    2    2
5    王五3    16    2    3
5    王五3    16    2    4
2    李四     18    1    5
1    张三     19    1    6
4    啦啦     20    7    7

2、row_number() over(PARTITION BY 字段order by 字段) over中可以分区在编号

SELECT *,ROW_NUMBER() OVER ( PARTITION BY s.ClassID ORDER BY s.StuAge) FROM Student AS s

2    李四     18    1    1
1    张三     19    1    2
3    王五     16    2    1
5    王五2    16    2    2
5    王五3    16    2    3
5    王五3    16    2    4
4    啦啦     20    7    1

3、DENSE_RANK()出现  并列排序 （序号连续）

--DENSE_RANK()
SELECT *,DENSE_RANK() OVER ( ORDER BY s.StuAge DESC) FROM Student AS s

4    啦啦     20    7    1
1    张三     19    1    2
2    李四     18    1    3
3    王五     16    2    4
5    王五2    16    2    4
5    王五3    16    2    4
5    王五3    16    2    4

4、RANK()   出现并列排序（序号不连续）

--rank()
SELECT *,RANK() OVER (ORDER BY s.StuAge ) FROM Student AS s

3    王五     16    2    1
5    王五2    16    2    1
5    王五3    16    2    1
5    王五3    16    2    1
2    李四     18    1    5
1    张三     19    1    6
4    啦啦     20    7    7

5、NTILE(INT)     要把查询得到的结果平均分为几组

--NTILE 要把查询得到的结果平均分为几组，总数/组数=每组数量
--如果每组数量部位整数  则在第一组多加行   少的话在最后一组减
SELECT *,NTILE(3) OVER (ORDER BY s.StuAge DESC) FROM Student AS s

4    啦啦     20    7    1
1    张三     19    1    1
2    李四     18    1    1
3    王五     16    2    2
5    王五2    16    2    2
5    王五3    16    2    3
5    王五3    16    2    3

二、结果集合并

1、UNION

--UNION  去除两个表中重复的行
SELECT * FROM Student AS s UNION  SELECT * FROM Student

1    张三     19    1
2    李四     18    1
3    王五     16    2
4    啦啦     20    7
5    王五2    16    2
5    王五3    16    2

View Code

2、UNION ALL

--UNION ALL  完全合并，不去除两个表中重复的行
SELECT * FROM Student AS s UNION ALL  SELECT * FROM Student

1    张三     19    1
2    李四     18    1
3    王五     16    2
4    啦啦     20    7
5    王五2    16    2
5    王五3    16    2
5    王五3    16    2
1    张三     19    1
2    李四     18    1
3    王五     16    2
4    啦啦     20    7
5    王五2    16    2
5    王五3    16    2
5    王五3    16    2

View Code

三、GROUP BY产生高级汇总结果

https://msdn.microsoft.com/zh-cn/library/ms175939(v=sql.90).aspx

小计合计

1、WITH CUBE（英文多维数据集）生成的结果集显示了所选列中值的所有组合的聚合。

正常分组

select 
s.ClassID,s.StuAge,COUNT(1)
FROM Class AS c
inner JOIN Student AS s
ON s.ClassID = c.ClassID GROUP BY s.ClassID,s.StuAge

ClassID    StuAge    (无列名)
2          16         8
1          18         1
1          19         1

with cube 汇总

--只是在基于GROUP BY 子句创建和汇总分组的可能的组合上有一定差别，CUBE将返回的更多
--的可能组合。如果在GROUP BY子句中有N个列或者是有N个表达式的话，SQLSERVER在结果集
--上会返回2的N－1次幂个可能组合。
select 
s.ClassID,s.StuAge,COUNT(1)
FROM Class AS c
inner JOIN Student AS s
ON s.ClassID = c.ClassID GROUP BY s.ClassID,s.StuAge WITH CUBE

ClassID    StuAge    (无列名)
2          16            8
NULL       16            8
1          18            1
NULL    　 18    　　　　 1
1        　19     　　　　1
NULL    　 19    　　　　 1
NULL    　 NULL    　　   10
1       　 NULL    　　   2
2       　 NULL  　　     8

    select 
s.ClassID,s.StuAge,COUNT(1)
FROM Class AS c
inner JOIN Student AS s
ON s.ClassID = c.ClassID GROUP BY CUBE( s.ClassID,s.StuAge)

ClassID    StuAge    (无列名)
2    16    8
NULL    16    8
1    18    1
NULL    18    1
1    19    1
NULL    19    1
NULL    NULL    10
1    NULL    2
2    NULL    8

View Code

    select 
s.ClassID,s.StuAge,COUNT(1)
FROM Class AS c
inner JOIN Student AS s
ON s.ClassID = c.ClassID GROUP BY CUBE( s.ClassID),s.StuAge

ClassID    StuAge    (无列名)
2    16    8
NULL    16    8
1    18    1
NULL    18    1
1    19    1
NULL    19    1

View Code

2、WITH ROLLUP（英文汇总）　　生成的结果集显示了所选列中值的某一层次结构的聚合

https://msdn.microsoft.com/zh-cn/library/ms189305(v=sql.90).aspx

ROLLUP就是将GROUP BY后面的第一列名称求总和，而其他列并不要求而CUBE则会将每一个列名称都求总和

select 
s.ClassID,s.StuAge,COUNT(1)
FROM Class AS c
inner JOIN Student AS s
ON s.ClassID = c.ClassID GROUP BY s.StuAge,s.ClassID WITH ROLLUP

ClassID    StuAge    (无列名)
2        16    8
NULL    16    8
1        18    1
NULL    18    1
1        19    1
NULL    19    1
NULL    NULL    10

View Code

select 
s.ClassID,s.StuAge,COUNT(1)
FROM Class AS c
inner JOIN Student AS s
ON s.ClassID = c.ClassID GROUP BY s.ClassID,s.StuAge WITH ROLLUP

ClassID    StuAge    (无列名)
1        18        1
1        19        1
1      NULL      2
2        16        8
2    NULL        8
NULL    NULL        10

View Code

四、GROUPING SETS和GROUPING函数

1、GROUPING SETS可以简化大量的UNION

　　插叙:

SQL统计函数

https://docs.microsoft.com/zh-cn/sql/t-sql/functions/aggregate-functions-transact-sql

View Code

　　使用union合并

SELECT c.ClassName,NULL,COUNT(1)
  FROM Class AS c INNER JOIN Student AS s ON s.ClassID = c.ClassID GROUP BY c.ClassName
  UNION
SELECT  NULL,s.StuAge,COUNT(1)
  FROM Class AS c INNER JOIN Student AS s ON s.ClassID = c.ClassID GROUP BY s.StuAge

ClassName    (无列名)    (无列名)
NULL    16    8
NULL    18    1
NULL    19    1
班级1    NULL    2
班级2    NULL    8

　　对应执行计划

　　使用grouping sets(列明,表达式,(列名1,列名2...)) 可以是列名表达式，多个组合。

SELECT c.ClassName,s.StuAge,COUNT(1)
  FROM Class AS c INNER JOIN Student AS s ON s.ClassID = c.ClassID GROUP BY GROUPING SETS(s.StuAge,c.ClassName)

ClassName    StuAge    (无列名)
班级1    NULL    2
班级2    NULL    8
NULL    16    8
NULL    18    1
NULL    19    1

GROUPING SETS不仅仅只是语法糖.而是从执行原理上做出了改变.（可以查看这两者的执行计划，第二次表扫描增多，第一次少，但是前面就行union操作。）

2、grouping sets和grouping 联合使用

grouping(列名或者表达式) grouping(列名1,列名2...) 返回 0 1，必须和group by一起使用。参数必须在GROUPBY里面的

MSDN:Is a column or an expression that contains a column in a GROUP BY clause.

SELECT CASE    WHEN GROUPING(c.ClassName)=0 THEN c.ClassName ELSE '空' end as ClassName,s.StuAge,COUNT(1)
  FROM Class AS c INNER JOIN Student AS s ON s.ClassID = c.ClassID GROUP BY GROUPING SETS(s.StuAge,c.ClassName)

ClassName    StuAge    (无列名)
班级1    NULL    2
班级2    NULL    8
空    　　16    8
空    　　18    1
空    　　19    1

3、GROUPING_ID

　　　　GROUPING_ID (<column_expression> [ ,...n ]) 也可以是组合（根据groupingsets 里面的组合），作用：计算分组等级

  SELECT s.StuAge,s.ClassID,COUNT(1),grouping_id(s.StuAge,s.ClassID) AS c FROM Student AS s GROUP BY cube(s.StuAge,s.ClassID) ORDER BY  c

StuAge    ClassID    (无列名)    c
18    1    1    0
19    1    1    0
16    2    4    0
20    7    1    0
16    NULL    4    1
18    NULL    1    1
19    NULL    1    1
20    NULL    1    1
NULL    2    4    2
NULL    1    2    2
NULL    7    1    2
NULL    NULL    7    3

View Code

  SELECT s.StuAge,s.ClassID,COUNT(1),grouping_id(s.StuAge) AS c FROM Student AS s GROUP BY cube(s.StuAge,s.ClassID) ORDER BY  c

StuAge    ClassID    (无列名)    c
18    1    1    0
19    1    1    0
16    2    4    0
16    NULL    4    0
18    NULL    1    0
19    NULL    1    0
20    NULL    1    0
20    7    1    0
NULL    7    1    1
NULL    NULL    7    1
NULL    2    4    1
NULL    1    2    1

View Code

SELECT CASE    WHEN GROUPING(c.ClassName)=0 THEN c.ClassName ELSE '空' end as ClassName,s.StuAge,COUNT(1),grouping_id(s.StuAge,c.ClassName) 
  FROM Class AS c INNER JOIN Student AS s ON s.ClassID = c.ClassID GROUP BY GROUPING SETS(s.StuAge,c.ClassName)

ClassName    StuAge    (无列名)    (无列名)
班级1    NULL    2    2
班级2    NULL    8    2
空    16    8    1
空    18    1    1
空    19    1    1

View Code

SELECT CASE    WHEN GROUPING(c.ClassName)=0 THEN c.ClassName ELSE '空' end as ClassName,s.StuAge,COUNT(1),grouping_id(s.StuAge) 
  FROM Class AS c INNER JOIN Student AS s ON s.ClassID = c.ClassID GROUP BY GROUPING SETS(s.StuAge,c.ClassName)

ClassName    StuAge    (无列名)    (无列名)
班级1    NULL    2    1
班级2    NULL    8    1
空    16    8    0
空    18    1    0
空    19    1    0

View Code

只有一个分组等级

SELECT s.StuAge,s.ClassID,COUNT(1),grouping_id(s.StuAge) AS c FROM Student AS s GROUP BY s.StuAge,s.ClassID ORDER BY  c




    SELECT s.StuAge,s.ClassID,COUNT(1),grouping_id(s.StuAge,s.ClassID) AS c FROM Student AS s GROUP BY s.StuAge,s.ClassID ORDER BY  c

两个sql结果一样

StuAge    ClassID    (无列名)    c
18    1    1    0
19    1    1    0
16    2    4    0
20    7    1    0

View Code

思考:group 里面分组，

单一条件分组，group by 列1，列2

多项分组，例如 group by grouping sets(列1，列2，（列3，列4）)，GROUP BY cube(s.StuAge,s.ClassID)

五、WITH AS 公用表表达式（CTE）（common table expression）也叫做子查询部分

MSDN详解:https://docs.microsoft.com/zh-cn/sql/t-sql/queries/with-common-table-expression-transact-sql (还是这帮老外厉害)

http://wudataoge.blog.163.com/blog/static/80073886200961652022389/

1、语法格式

[ WITH <common_table_expression> [ ,n ] ]
<common_table_expression>::=
        expression_name [ ( column_name [ ,n ] ) ]
    AS
        ( CTE_query_definition )

如果WITH AS短语所定义的表名被调用两次以上，则优化器会自动将
WITH AS短语所获取的数据放入一个TEMP表里，如果只是被调用一次，则不会。而提示materialize则是强制将WITH AS
短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。

2、用法

DECLARE @cname NVARCHAR(50),@cclass NVARCHAR(50)
SET @cname='%名%';
SET @cclass='%班级%';
WITH stu AS (SELECT * FROM Student AS s WHERE s.StuName LIKE @cname),
     claa AS    (SELECT * FROM Class AS c WHERE c.ClassName LIKE @cclass)
SELECT * FROM stu st INNER JOIN claa AS s ON st.ClassID =s.ClassID
--下面一句不能执行
SELECT * FROM claa

StuID    StuName    StuAge    ClassID    ClassID    ClassName
8         名字8        8           1         1        班级1

3、递归

数据库脚本

USE [Test]
GO
/****** Object:  Table [dbo].[Deparment]    Script Date: 2017/6/30 11:23:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Deparment](
    [ID] [int] NULL,
    [PID] [int] NULL,
    [Name] [nvarchar](50) NULL
) ON [PRIMARY]

GO
INSERT [dbo].[Deparment] ([ID], [PID], [Name]) VALUES (1, NULL, N'永赢基金')
GO
INSERT [dbo].[Deparment] ([ID], [PID], [Name]) VALUES (2, 1, N'人力资源')
GO
INSERT [dbo].[Deparment] ([ID], [PID], [Name]) VALUES (3, 1, N'财务部')
GO
INSERT [dbo].[Deparment] ([ID], [PID], [Name]) VALUES (4, 2, N'人力主管')
GO
INSERT [dbo].[Deparment] ([ID], [PID], [Name]) VALUES (5, 2, N'人力员工')
GO
INSERT [dbo].[Deparment] ([ID], [PID], [Name]) VALUES (6, 5, N'人力大员工')
GO
INSERT [dbo].[Deparment] ([ID], [PID], [Name]) VALUES (7, 5, N'人力小员工')
GO
INSERT [dbo].[Deparment] ([ID], [PID], [Name]) VALUES (8, 1, N'监察稽核部')
GO

View Code

递归语句

WITH dep AS (SELECT *,0 AS DepLeavel FROM Deparment WHERE ID=1
UNION ALL

SELECT d.*,dtemp.DepLeavel+1 FROM dep AS dtemp INNER JOIN Deparment AS d ON d.PID=dtemp.ID
)
SELECT * FROM dep AS d OPTION (MAXRECURSION 3)

ID    PID    Name    DepLeavel
1    NULL    永赢基金    0
2    1    人力资源    1
3    1    财务部    1
8    1    监察稽核部    1
4    2    人力主管    2
5    2    人力员工    2
6    5    人力大员工    3
7    5    人力小员工    3

View Code