SQLWeb测试

SQL Server_查询整理

2017-04-02  本文已影响134人  1998962ab991

基于 SQL Server的查询整理！

SQL 逻辑流

Select 语法

  1. Select语句的From部分将所有数据源组装进一个结果集，然后由Select语句的剩余部分对结果集执行操作。
  2. Where子句作用于From组装的记录集，根据条件筛选某些行。
  3. 聚合函数对数据集执行求和操作。
  4. Group by 子句根据在该子句中指定的列将大量数据集分组成较小的数据集。
  5. Having 对较小的数据组执行聚合函数。
  6. Order by 子句确定结果子的排列顺序。默认为升序；
Select [Distinct] [Top(n)] *, columns, or expressions
  [From data source(s)]
    [Join data source
      ON condition] (may include multiple joins)
  [Where conditions]
  [Group by columns]
  [Having conditions]
  [Order by columns];

查询语句的逻辑流

数据源(From) —— 条件(Where) —— 列/表达式 (col/exp) —— Order by — 谓词

  1. From，查询首先组装初始数据集。

  2. Where，筛选；筛选过程实际上是选择符合标准的行的where子句。

  3. Group by，组合数据的子集

  4. 聚合，Aggregations，选择性地对数据进行聚合；如求平均值，按列中的值对数据分组以及筛选组；

  5. Having，筛选数据的子集

  6. 列表达式：处理Select列，并计算任何表达式 [ 这个时候才涉及到列 ]

  7. Order by，排序

  8. Over，窗口函数和排名函数通过与其他聚合函数一起提供结果的单独排序的视图

  9. Distinct，从结果集中删除任何重复的行

  10. Top，选定行后，执行计算，并按所需的顺序排序

  11. Insert,Update,Delete，最后一个逻辑步骤是将数据修改操作应用到查询结果。

  12. Output，选择插入和删除的虚拟表，并返回给客户端

  13. Union，堆叠或合并多个查询的结果

SQL编写语句标准

  1. 若出现多个主条件，先将主条件的区分变量统一到一个表中
  2. 其他表与这个表进行联接
    • 先把各个字段单独用Select写出来
    • 再合并为一个Select语句

运算符介绍

优先级

Not > 算术运算符(+-) > 条件运算符(where) > And > Or

电脑中字符优先级： 数字>字母

1a > a1 > a11 > aa1

通配符

运算符 含义 示例
% 任意长度的字符串 Email Like ‘%@%.com’
‘_’ 任意一个字符 AuthorName Like ‘张_’
[ ] 在指定范围内的任意一个字符 A Like ‘A6C8[1-5]’
[^] 不在指定范围内的任意一个字符 A Like ‘A6C8[^1-6]’

查找含通配符的表达式：

  1. 必须使用Like字符
  2. 把通配符放入方括号[ ] 内
  3. 在其之前放一个转义符

注意事项与通用规则

方括号 [ ]

# 一个表名叫user，user在sqlserver中属于关键字，那么查询的时候必须要这样
select * from [user] ;

# 若表名user中没有user的列，则无需加方括号
select * from user ;

合并：字段+表格

# 字段合并 +
SELECT 机构+客户名称  FROM allzjb  where 结算日期>'2015-11-15'

SELECT RTRIM(LastName) + ',' + SPACE(2) +  LTRIM(FirstName) FROM Person.Person
--剪裁姓氏，并将逗号、两个空格和 Person 中的 AdventureWorks2012 表列出的人员名字串联起来

使用“ + ”连接多个字段，合并成一列

  • 前后类型应兼容；

如果+连接数值类型，结果是数值之和

如果+连接字符串类型，结果是字符串的连接

# 表格合并 - insert into A1 select ... 
INSERT INTO table2 SELECT * FROM table1;  -- 把table1的数值插入到table2中

# 只插入某一列的值
INSERT INTO table2 (column_name) 
  SELECT column_name FROM table1 ;

# 合并为一个字符串 - concat()
Select Concat(Null, 'Patrick ', 1, ' LeBlacn')
--隐式地将所有值转换为字符串，将空值转为空字符串

Where条件

where col >= 10 ;  优于 where col !< 9

惊叹号! ，不是ANSI标准的SQL；

Where name Like 'Chain%' or ProductID Between 320 And 324 
  And name Like '%s%'  
--1. 先执行And，即找出name中 带有 s 的名字；
--2. 再在其中寻找 name中有Chain 或者 ProduceID在[320,324]

------------------
Where (name Like 'Chain%' or ProductID Between 320 And 324 )
  And name Like '%s%'
--1. 先执行括号，即先找出name中有Chain 或者 ProduceID在[320,324]
--2. 再在其中找出name中 带有 s 的名字；

Between and

# 使用带有日期时间值的 BETWEEN 
WHERE RateChangeDate BETWEEN '2001-12-12' AND '2002-01-05';   

----下面的示例检索所在的行【datetime值】可以介于'20011212'和'20020105'(含） 之间;因为在查询中的日期值和datetime值存储在RateChangeDate而无需在日期的时间部分中指定了列。
-- 下面是结果集：
BusinessEntityID RateChangeDate
----------- -----------------------
3 2001-12-12 00:00:00.000
4 2002-01-05 00:00:00.000
----未指定时间部分时，将默认使用 12:00 A.M。 
--请注意，若某行的时间部分晚于 2002-01-05 12:00 A.M.， 则由于它处于范围之外，因此此查询不返回该行。

ALL、SOME、ANY

  1. ALL，相当于And；如果子查询可能返回一个空值，那么使用ALL会判断为fasle，使用时要小心；

循环

--首先计算所有数的平均价格,如果低于30的话进入循环让所有的price翻倍,
--里面又有个if来判断如果最大的单价还大于50的话,退出循环,否则继续循环,知道最大单价大于50就break出循环,呵呵,
--我分析的应该对吧.
use pubs
go
while (select avg(price) from titles) <$30 
begin
 update titles
  set price=price*2
  select max(price) from titles
  if(select max(price) from titles) >$50
  break
  else
  continue
end
print 'too much for the marker to bear'

插入、更新、删除、合并数据

插入数据

# 插入简单的值行
Insert [Into] schema.table [ (colums, ...) ]
  Values (value,...),
    (value,...);

# 在标识列中插入数据，要使用Set Identity_insert on / off关键字
Set Identity_insert dbo.address On
  Insert Into dbo.address (AddreddID, Addressl, city, state, county)
  Valuse(999, )
Set Identity_insert dbo.address Off


------------------------------------------------------------------------------------
# 从Select语句中插入结果集
Insert [Into] schema.table [ (colums,...)]
  Select columns From data [Where conditions];
--1. schema.table 必须存在
--2. into schema.table中的列必须与之后Select中的列相同
--3. 若schema.table中有5列，只选取其中3列插入，则其他2列的观测值对NULL


------------------------------------------------------------------------------------
# 从存储过程插入结果集
Insert [Into] schema.table [ (colums,...)]
  Execute storedprocedure;

--

# 在插入数据时创建表
Select colums 
  Into newtable From data [where conditions];

SQL中合并列，只要选择对应的列即可

Select a.*, b.* From a Full Join b

更新数据

# 更新单个表
Update schema.table
  Set column = expression,
    column = value 
  [From data]
  [Where conditions];


----------------------------------------------------------------------------------
# 执行全局搜索与替代
# replace(data, 'aaa', 'bbb')
Update address
  Set county = Replace(county, 'sun', 'dark')  -- 将county列中所有的sun替换为dark
  Where county Like '%shine' ;

删除数据

# delete
Delete [From] schema.table
  /*[From data] SQL Server 特有的T-SQL扩展，用来进行联接；正常情况下应在where子句中创建子查询*/
  [Where conditions];

# 删除所有行
Delete From schema.table
--不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的：

---------------------------------------------------------------------------------
# 删除表中所有行 - truncate
Truncate Table dbo.address

---------------------------------------------------------------------------------
# 删除整个表
Drop table schema.table

truncate 、delete与drop区别

  1. truncate和不带where子句的delete、以及drop都会删除表内的数据drop、truncate都是DDL语句(数据定义语言)，执行后会自动提交。Delete是DML语句(数据库操作语言)

  1. truncate 和 delete 只删除数据不删除表的结构(定义)；drop 语句将删除表的结构被依赖的约束(constrain)、触发器(trigger)、索引(index)；依赖于该表的存储过程/函数将保留,但是变为 invalid 状态。

  2. delete 语句是数据库操作语言(dml)，这个操作会放到 rollback segement 中，事务提交之后才生效；如果有相应的 trigger，执行的时候将被触发。truncate、drop 是数据库定义语言(ddl)，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。

  3. delete 语句不影响表所占用的 extent，高水线(high watermark)保持原位置不动。drop 语句将表所占用的空间全部释放。truncate 语句缺省情况下见空间释放到 minextents个 extent，除非使用reuse storage；truncate 会将高水线复位(回到最开始)。

  4. 速度：drop> truncate > delete

    • TRUNCATE TABLE在功能上与不带WHERE子句的DELETE语句相同：二者均删除表中的全部行。但TRUNCATE TABLE 比DELETE速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

  5. 安全性：

    • 想保留表而将所有数据删除，如果和事务无关，用truncate即可。如果和事务有关,或者想触发trigger,还是用delete。

  1. 对于由FOREIGN KEY 约束引用的表，不能使用TRUNCATE TABLE，而应使用不带WHER 子句的DELETE语句。由于 TRUNCATE TABLE不记录在日志中，所以它不能激活触发器。

  2. TRUNCATE TABLE不能用于参与了索引视图的表。

合并数据

Meger flightpassengers As f         -- MERGE 子句用于指定作为插入、更新或删除操作目标的表或视图
  Using checkin as c                --USING 子句用于指定要与目标联接的数据源
    On c.lastname = f.lastname      --ON 子句用于指定决定目标与源的匹配位置的联接条件
    And c.firstname = f.firstname
    And c.flightcode = f.flightcode
    And c.flightdate = f.flightdate
  When Matched 
    Then Update Set f.seat = c.seat
  When Not Matched By Target        --当Soucre表与Target表（基准表）不匹配时，对Target表进行操作
    Then Insert (Firsrname, Lastname, Flightcode, flightdate, seat)
  When Not Matched By Source        --当Target表与Soucre表（基准表）不匹配时，对Target表进行操作【相同】
    Then Delete;

--WHEN 子句（WHEN MATCHED、WHEN NOT MATCHED BY TARGET 和 WHEN NOT MATCHED BY SOURCE）基于 ON 子句的结果和在 WHEN 子句中指定的任何其他搜索条件指定所要采取的操作。
--OUTPUT 子句针对插入、更新或删除的目标中的每一行返回一行。

--必须由分号进行终止
--必须是一对一匹配；一对多匹配是不允许的
--联接条件必须是确定性的，也就是可重复的

返回修改后的数据

Output子句有一个较为高级的应用，可以把输出数据传输到外查询。

# 从插入返回数据
Insert Into personlist
  Output Inserted.*    -- Inserted.
  Valuse(7777, 'Jane', 'Doe');


# 从更新返回数据 -- 可同时返回更新前、更新后的数据
Update personlist
  Set firstname = 'Jane', Lastname = "Doe"
  Output Deleted.firstname oldfirstname, Deleted.lastname oldflastname,  --Deleted.column oldcolumn
         Inserted.firstname newfirstname, Inserted.lastname newlastname
  Where businessentityID = 7777


# 从删除返回数据
Delete From personlist
  Output Deleted.*
  Where ...


# 从合并返回数据
...
  Output deleted.column, deleted.column,
         $action,                             -- 显示数据库操作的行为（为Insert、Delete、Update)
        inserted.column, inserted.column ;


# 把数据库返回到表中
Declare @Deletedperson Table(
  businessentityID Int Not Null Primary Key,
  lastname Varchar(50) Not Null,
  firstname Varchar(50) Not Null
)

Delete dbo.personlist
  Output Deleted.colunm, Deleted.column
  Into @Deletedperson
  Where bussinessentityID = 2;

1. 查询：字符串

函数名 功能描述 举例
LEN 返回指定字符串的字符个数(而不是字节)，其中不包含尾随空格 SELECT LEN('李丽然作者') 返回:5
DATALENGTH 返回指定字符串的字节数 SELECT DATALENGTH('中国人') 返回：6
UPPER 将小写字符转换成大写字符 SELECT UPPER('book图书表') 返回:BOOK图书表
LTRIM 返回去掉左侧空格的字符串 SELECT LTRIM(' Authors') 返回: Authors
CHARINDEX 查找一个指定的字符串在另一个字符串中的起始位置 SELECT CHARINDEX('L', 'HELLO', 1) 返回:3
LEFT 返回字符串中从左边开指定个数的字符 SELECT LEFT('zhangsan', 2) 返回:zh
Substring 返回字符串的一部分：从字符串串的起始位置连续取指定个数的子串 SELECT SUBSTRING('我爱我的家乡',3, 2) 返回：我的
Replace 替换一个字符串中的字符 SELECT REPLACE('我爱我的家乡家乡', '家乡', '学校') 返回: 我爱我的学校学校
Stuff 将一个字符中删除指定数量的字符，并插入另一个字符
Concat 将多个字符串组合为单个字符串

使用字符串字面量时，通过输入两个单引号转化为一个单引号

Replace(name, '''', '') ; Life''s Great! 被解释为 Life's Great!

行数&字符数

# 返回观测值的行数 - count [非空值的个数]
SELECT COUNT(name) FROM my_contacts;

# 返回字符串的字符个数 - len
SELECT LEN('中国人') ;  # 返回:3

# 返回字符串的字节数 - datalength
SELECT DATALENGTH('中国人');  # 返回：6

去空格

# 去空格 - ltrim / rtrim
select ltrim(rtrim(' "左右都没有空格"  '));  # 左右去空格

# 生成空格 - space
select space(2);  # 生成2个空格

SELECT RTRIM(LastName) + ',' + SPACE(2) +  LTRIM(FirstName) FROM Person.Person
# 剪裁姓氏，并将逗号、两个空格和 Person 中的 AdventureWorks2012 表列出的人员名字串联起来

取子串

# 取子串：特定位置 - substring
select substring(name,1,2);  # 返回na;

# 取子串：左/右 - left /right
select left(ltrim( name), 3);  # 返回nam;

返回特定位置

# 返回位置：起始位置 - charindex
select charindex('L', 'HELLO', 1);  # 返回:3; 1表示第一次出现
--charindex(serach string, string ,starting position) ；第三个参数默认为1，可不写

# 返回位置：表达式中某模式第一次出现的起始位置 - patindex
patindex('%123%','abc123def');  # 返回4
--允许通配符的使用

替换

# 替换 - replace
select replace('abcdef','cde','xxx'); 返回 abxxxf
Select replace(colname, 'aaa', 'bbb');   --colname列中每个单元格的aaa值替换为bbb值，并返回colname列

# 删除&替换 - stuff()
Select stuff('abcdef', 3, 2, '123')  -- 返回ab123ef；从第三个位置开始删除2个字符，并插入123
--stuff(string, insertion position, delete count, string inserted);

合并

# 合并为一个字符串 - concat()
Select Concat(Null, 'Patrick ', 1, ' LeBlacn')
--隐式地将所有值转换为字符串，将空值转为空字符串

格式转换

# 转换：大小写 - upper/lower
select upper('abc');  # 返回 ABC

# 转换：反转 - reverse
select reverse('abc');  # 返回'cba'

# 转换：字符形式 - char
select char(213);

# 转换：字符串形式 - str
select str(123.45, 6,1);  # 把数值转换成字符串格式
--返回123.5； 将123.45转为6个位置的字符串，数字的小数部分舍入为1为小数；

# 转换：ascii码 - ascii
select ascii(123) as '123' 

# 格式的转换、显示 - convert : 将第2个参数转换为第1个参数所指定的类型 / 用不同的格式显示日期/时间数据。
-- CONVERT (data_type[(length)], expression [, style]) ;
-- [,style] 日期格式样式

SELECT CONVERT(DateTime, '2020-09-09');  # 返回: 2020-09-09 00:00:00.000

SELECT CONVERT(varchar(5), 92.89);  # 返回：92.89

SELECT CONVERT(varchar(11), GETDATE(), 121);  # 返回：2010-03-24
常用日期格式：
  - 23 ：日期格式 yy-mm-dd
  - 111：日期格式 yy/mm/dd
  - 120：日期格式 yyyy-mm-dd hh:mi:ss(24h)
  - 121：日期格式 yyyy-mm-dd hh:mi:ss.mmm(24h)

  - 105：日期格式 dd-mm-yy
  - 110：日期格式 mm-dd-yy

Data_type : INT / DECIMAL(10,2) / CHAR() / VARCHAR() /

2. 查询：数字相关

函数名 功能描述 举例
ABS 返回表达式绝对值 SELECT ABS(-90) 返回：90
ROUND 按指定的精度进行四舍五入 SELECT ROUND(56.629, 2) 返回：56.630
SQRT 返回指定表达式的平方根 SELECT SQRT(9) 返回：3
FLOOR 返回小于或等于指定数值表达式的最大整数 $\le$ SELECT FLOOR(23.9) 返回: 23
CEILING 返回大于或等于指定数值表达式的最小整数 $\ge$ SELECT CEILING(23.9) 返回：24
POWER 次方；返回x的y次方 SELECT POWER(2,3) ; 返回8
EXP 指数；e的x次方 SELECT EXP(2) 返回e$^2$
LN /LOG(x,y) 对数； SELECT LN(e) ；返回1
MOD 返回x除以y的余数 SELECT MOD(9,2); 返回1
SIGN 判断正负；若x为正返回1；若x为负 返回-1 ; 若x为0 返回0 SELECT SIGN(2); 返回1

3. 查询：聚合函数

函数名 功能描述
AVG 平均值
SUM 求和
MAX/MIN 求最大值/最小值
COUNT 计算非空单元格（ 返回 int 数据类型值）
count_big 计算非空单元格（返回 bigint 数据类型值）
VAR 方差 # 平方
varp 总体方差
STDEV 标准差
stdevp 总体标准差

除Count(*)外，所有聚合函数均忽略空值NULL；

avg( ) $\ge$ $sum( ) \over count(*)$

Count(*)，计数时也将NULL计入；
其他所有聚合函数，包括Count(col_name)的形式，计算时均已排除了NULL

表a，观测值10行，其中2个NULL；
Count(*) # 返回10；

Count(列名) # 返回8

除非，可用 isnull() 函数进行转换，来计算；

例如，对表a求平均值，若直接用avg() ，其分母为8；

若想使得其分母变成10，应添加 case when isnull(col_name,0) then col_name else end

4. 查询：日期相关

函数名 描述
Getdate 返回当前服务器的日期和时间
Current_timestamp 除了ANSI标准，等同于Getdate
Getutcdate 返回当前服务器的日期和时间，并转化为格林威治标准时间
Sysdatetime 返回当前服务器的日期和时间
Sysutcdatetime 返回当前服务器日期，并转化为格林威治标准时间
Sysdatetimeoffset 返回Datetimeoffset值
函数名 描述
Dateadd 在指定的日期上累加数值得到新的日期；dateadd(datepart,number,date) SELECT DATEADD(yyyy, 4, '01/09/2003') 返回：2007-01-09
datepar是参数的格式：datepart=yy(年)，mm(月)，qq(季度)；date 参数是合法的日期表达式。number 是您希望添加的间隔数；对于未来的时间，此数是正数，对于过去的时间，此数是负数。
DATEDIFF 返回两个日期的差值 ； datediff(datepart,startdate,endate) SELECT DATEDIFF(dd, '02/05/2003', '02/09/2005') 返回：735
DATEPART 返回指定日期部分的整数（整数形式） SELECT DATEPART(dd, '01/09/2003') 返回：9
DATENAME 返回指定日期部分的字符串（字符串形式）；工作日(dw)、周(wk)、日(dd)、月(mm) SELECT DATENAME(dw, '02/02/2009') 返回: 星期一
YEAR 返回指定日期“年”部分整数 SELECT YEAR(GETDATE()) 返回：当前年份整数
MONTH 返回指定日期“月”部分整数 SELECT MONTH(GETDATE()) 返回：当前月份整数
DAY 返回指定日期“日”部分整数 SELECT DAY(GETDATE()) 返回：当前日期整数

查询：日期的应用

# 获取当前时间 - getdate
select getdate();


# 返回指定的时间 - dateadd
select dateadd(dd, 3, '2017-01-31'); 
    # datepart：yy/qq/mm/ww/dd/hh/mi/ss/ms ; 
    # num为正或为负；
--dateadd()仅接受提取日期部分


# 计算两个时间差 - datediff
select datediff(dd,'2016-06-01', '2017-01-31'); # yy/qq/mm/ww/dd/hh/mi/ss/ms
--datediff(date_type,startdate , enddate)


# 取出时间的某一部分 - datename/datepart
select datename(dd, '2017-01-31');  # datename 字符串形式
select datepart(dd, '2017-01-31');  # datepart 整数形式


# 获取日期的年份/季度/月度/日期等 
select year(getdate());  quarter/month/day

参考：日期缩写参考

日期部分 缩写
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y # 查询date在当年是第多少天. 一年中的第几天；
day dd, d
week wk, ww # 查询date在当年中是第几周 / 以周为单位的间隔数
weekday **dw **# 一周中的第几天(星期几)
Hour hh
minute mi, n
second ss, s
millisecond ms
代码 Style 格式
100 或者 0 mon dd yyyy hh:miAM （或者 PM）
101 mm/dd/yy
102 yy.mm.dd
103 dd/mm/yy
104 dd.mm.yy
105 dd-mm-yy
106 dd mon yy
107 Mon dd, yy
108 hh:mm:ss
109 或者 9 mon dd yyyy hh:mi:ss:mmmAM（或者 PM）
110 mm-dd-yy
111 yy/mm/dd
112 yymmdd
113 或者 13 dd mon yyyy hh:mm:ss:mmm(24h)
114 hh:mi:ss:mmm(24h)
120 或者 20 yyyy-mm-dd hh:mi:ss(24h)
121 或者 21 yyyy-mm-dd hh:mi:ss.mmm(24h)
126 yyyy-mm-ddThh:mm:ss.mmm（没有空格）
130 dd mon yyyy hh:mi:ss:mmmAM

5. 查询：表格相关

限定行数 - TOP

# TOP n( 前n行 )
SELECT TOP 5 *  FROM  allzjb;  #　查询所有数据的中前5个

------------------------
TOP n PERCENT ( 按百分比取数据 )
SELECT Top 30 PERCENT * FROM allzjb ;

With Ties

  1. 允许最后的位置包含多行，但这多行是完全相同的
Select top (10) With Ties listprice From ...

随机行选择

  1. 使用Top(1) 返回单行，且用Newid()随机排序结果；每次将返回一个随机值
  2. 涉及到较大的表时，可用Tablesample( n Percent/Rows)选项
  3. 由于是随机选择，可通过Repeatable()来指定 [效果同R语言中的set.seed()]
Select top(1) Lastname From person.person Tablesample(10 Percent) -- 随机选择10%的
  Repeatable(1234)
  Order by Newid();  --随机排序结果集

分组&排序

分组

分组后筛选

HAVING

 Select 交易账号,建平仓,[交易间隔(s)] From #b11  where [交易间隔(s)] < '60'
   group by 交易账号,建平仓,[交易间隔(s)]
   having count([交易间隔(s)]) >= 2  
  --指的是对满足所有group by字段的观测值进行计数统计；因为每个观测值都已经按照分组划分为不完全相同的观测值（每行中必定有一个不相等），所以结果为0

--HAVING 子句从 SalesOrderID 表中检索超过 SalesOrderDetail 的每个 $100000.00 的总计
SELECT SalesOrderID, SUM(LineTotal) AS SubTotal  
  FROM Sales.SalesOrderDetail  
    GROUP BY SalesOrderID  
    HAVING SUM(LineTotal) > 100000.00  
    ORDER BY SalesOrderID ;

排序

# 按某个组分组并排序
group by name
order by name;

---------------------------------
GROUP BY name
ORDER BY first_name, last_name DESC, SUM(age);  # 默认为升序排序（ASC）

窗口函数 - Select Over()

聚合子句

Sum(cola) Over(partition by col1 order by col2)
--其他聚合函数：avg/max/min /count 

# 根据分组求和
Over(Partition by col) --根据col1的分组对cola进行求和； [若同一组别有多个维度，则求和的值是相同的]

# 根据排名求累计和
Over(Order by col2)--根据col2的顺序对cola列累计求和； [若同一组有多个维度，求和的值是累加的]

# 根据分组后的排名，求累积和
Over(Partition by col1 order by col2) --根据Col1的分组进行排名得出Col2的次序，并按col2的次序对cola进行分组求和

# 移动平均
CONVERT(varchar(20),AVG(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate)),1) AS MovingAvg
![](

在窗口内分区：Partition by

  • 执行逻辑：先对查询结果进行排序，之后通过Partition by的列进行分区；
  • 窗口函数只能在Select 或者 Group by 子句中

排名 - rank子句

# 排名：无重复排名 - Row_number（组内连续的唯一的)
row_number() over (order by col2);  # 对所有进行排名
row_number() over (partition by col1 order by col2);  # 根据COL1分组，在分组内部根据 COL2排序


-------------------------------------------------
#　排名：有重复排名 - Rank （若出现字段值相同，序号一样，下一个跳过1位[排名是非连续的]）
rank() over([partion by col1] order by col2); 对所有进行排名

ProductID   Name                   LocationID   Quantity Rank  
494         Paint - Silver         3            49       1  
495         Paint - Blue           3            49       1  
493         Paint - Red            3            41       3 


-------------------------------------------------
# 排名：有重复排名 - Dense_rank （若出现字段值相同，序号一样，后一个不跳过[排名是连续的]）
dense_rank() over([partion by col1] order by col2)

ProductID   Name                               LocationID Quantity Rank  
494         Paint - Silver                     3          49       1  
495         Paint - Blue                       3          49       1  
493         Paint - Red                        3          41       2 


-------------------------------------------------
# 排名：对序号进行分组处理 - Ntile
ntile (4) over ([partion by col1] order by col2);  # (4)表示分为4组

ntile函数的分组依据（约定）：

首先系统会去检查能不能对所有满足条件的记录进行平均分组，若能则直接平均分配就完成分组了；若不能，则会先分出一个组，这个组分多少条记录呢？就是 （总记录数/总组数）+1 条，之所以分配 （总记录数/总组数）+1 条是因为当不能进行平均分组时，总记录数%总组数肯定是有余的，又因为分组约定1，所以先分出去的组需要+1条。

分完之后系统会继续去比较余下的记录数和未分配的组数能不能进行平均分配，若能，则平均分配余下的记录；若不能，则再分出去一组，这个组的记录数也是（总记录数/总组数）+1条。

举个例子，将53条记录分配成5组，53%5 = 3不能平均分配，则将余数3平均分配到前3组 （余数 = 分配的组数），然后比较余下的 53-(113)=20 条记录能否平均分配给未分配的2组，能平均分配，则剩下的2组，每组各20/2=10 条记录，分配完成，分配结果为：11，11，11，10，10。*

分页

# Offset & Fetch 必须结合使用
Select ... From
  Order by 
  Offset n rows  # 表示跳过n行
  Fetch Next 20 rows only  # 表示返回之后的20行

条件查询

    ## 两者区别
    1. 输入表达式，只能用于等同性(=)检查
    2. 布尔表达式，不局限于等同行(=)检查

    --用输入表达式，将与每个where子句中的值比较
    Case <input expression>  --只能用于等同性检查，而不进行其他比较
      when <when exp> then <result exp>
      [n...]
      [else <result exp>]
    End

    --给每个when子句提供一个布尔表达式，求值为TRUE或FALSE [布尔值是“真” True 或“假” False 中的一个]
    Case
      when <Boolean exp> then <result exp>
      [n...]
      [else <result exp>]
    End 

## 1.简单Case语句

# 在SELECT语句中，CASE 简单表达式只能用于等同性检查，而不进行其他比较
SELECT ProductNumber, Category =  
      CASE ProductLine     --表示如果 ProductLine = R 则返回 Road； Case之后的变量，为比较的变量
         WHEN 'R' THEN 'Road'  
         WHEN 'M' THEN 'Mountain'  
         WHEN 'T' THEN 'Touring'  
         WHEN 'S' THEN 'Other sale items'  
         ELSE 'Not for sale'  
      END,  
   Name  
  FROM Production.Product ； 

--
Select top 10 SalesOrderID % 10 As 'OrderLastDigit',ProductID % 10 As 'ProductLastDigit',
"How Colse ?" =  CASE SalesOrderID % 10 
         WHEN  ProductID % 1  THEN 'Exact Match'   -- 可在When子句中引用第二个列来做判断
         WHEN  ProductID % 1-1 THEN 'Within 1'  
         WHEN  ProductID % 1+1 THEN 'Within 1'  
         ELSE 'More Than One Apart'  
      END  
FROM Sales.SalesOrderDetail ；
-- % 表示返回余数； 5%2 返回1
-- 变量名在Case之前使用双引号（“”）说明，该变量是新创建的变量;但不建议如此；

------------------------------------------------------
## 2. 搜索Case语句
-- 没有输入表达式（即Case关键字与第一个When之间的部分）
-- When表达式必须求值为一个 布尔值；（Case简单语句中，When的表达式可以为1、3、Price+1[含运算]）

Select top 10 SalesOrderID % 10 As 'OrderLastDigit',ProductID % 10 As 'ProductLastDigit',
"How Colse ?" =  CASE                         -- 没有输入表达式
   WHEN (SalesOrderID % 10) < 3 THEN 'Ends With Less Than Threes' 
   WHEN ProductID =6 THEN 'ProductID is 6'     -- 可在When子句中引用第二个列来做判断
   WHEN ABS(SalesOrderID % 10 - ProductID) <= 1 THEN 'Within 1'  
   ELSE 'More Than One Apart'  
      END  
FROM Sales.SalesOrderDetail ；

# Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略
# 可在条件表达式中混合搭配的字段
# 可执行为任何表达式，只要最后的结果为布尔值

# SELECT CASE WHEN 用法
select userID ,  
count(CASE WHEN letterType='干部介绍信' then '1' end)干部介绍信数,  
count(CASE WHEN letterType='转递档案通知单' then '1' end)转递档案通知单数  
from T_LettersRecord GROUP BY userID  

------------------------------------------------------
# WHERE CASE WHEN 用法
SELECT l.letterType, u.realName  
   FROM T_LettersRecord as l, T_User as u  
  WHERE (CASE 
           WHEN l.letterType = '干部介绍信' AND u.userID = '1'  THEN 1  
           WHENl.letterType = '干部介绍信' AND  u.userID <> '1'  THEN 1  
         ELSE 0   
         END) = 1  

------------------------------------------------------
# 在 ORDER BY 子句中使用 CASE 
--计算 SalariedFlag 表中 HumanResources.Employee 列的值。SalariedFlag 设置为 1 的员工将按 BusinessEntityID 以降序顺序返回。 SalariedFlag 设置为 0 的员工将按 BusinessEntityID 以升序顺序返回
SELECT BusinessEntityID, SalariedFlag  
  FROM HumanResources.Employee  
  ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC  
        ,CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END;

------------------------------------------------------
# GROUP BY CASE WHEN 用法
SELECT   
CASE WHEN salary <= 3000 THEN 'T1'   
WHEN salary > 3000 AND salary <=8000  THEN'T2'   
WHEN salary > 8000 AND salary <=12000  THEN'T3'   
WHEN salary > 12000 AND salary <= 20000 THEN 'T4'   
ELSE NULL END 级别名称, -- 别名命名  
COUNT(*)   
FROM    t_userSalary  
GROUP BY   
CASE WHEN salary <= 3000 THEN 'T1'   
WHEN salary > 3000 AND salary <=8000  THEN'T2'   
WHEN salary > 8000 AND salary <=12000  THEN'T3'   
WHEN salary > 12000 AND salary <= 20000 THEN 'T4'   
ELSE NULL END;

联接与联合

当涉及多个联接时

一定要用较小规模的数据对查询进行单元测试；

并坚持使用左外联接

内联接

Inner Join

外联接

 # 条件位于Join子句
  Select a.col, b.col 
    From table As a
    Left join table2 As b 
    On a.id = b.id 
    And a.Lastname = 'Adams'  --该条件限制的是第二个表中的行
    [And a.Lastname = b.name]  --也可以再进行限制

------------------------------------------
# 条件位于Where子句中
Select a.col, b.col 
  From table As a
    Left join table2 As b 
       On a.id = b.id 
  Where a.Lastname = 'Adams'   --该where条件是对联接后的表进行限制

Left [Outer] Join

必须要表基准表放在左侧，非常重要！否则会产生大量NULL值。

Right [Outer] Join

建议左/右外联接不要混合使用

Full [Outer] Join

交叉联接

Cross Join

差集查询

# 左差集查询
--不在右表中的左表数据集；【可用Inner Join 中不等号为条件进行联接 <>】

Select c.customerid, so.ordernumber 
  From customer As c
    Left join salesorder As so On c.customerid = so.customerid
  Where so.ordernumer Is Null;  --设置第二个表的主键为NULL


# 全差集查询
Select c.customerid, so.ordernumber 
  From customer As c
    Left join salesorder As so On c.customerid = so.customerid
  Where so.customerid Is Null 
    or c.customerid Is Null  
--设置两个表的的主键为NULL

联合 - Union

  1. 返回每个结果集中的所有行
    • Union ，表示删除重复行
    • Union ALL ，表示不考虑是否存在重复行
  2. 每个Select必须具有相同的数量、类型；
  3. 列名或别名由第一个Select确定
  4. Order by子句放在最后，并且对所有结果进行排序，且列名必须是第一个Select语句中存在的
  5. 可用Select Into，但Into必须放在第一个Select语句中

列的xml数据类型必须为等效。

所有的列必须类型化为 XML 架构或是非类型化的。 如果要类型化，这些列必须类型化为相同的 XML 架构集合。

子查询

非相关子查询

公用表表达式

# With子句的表达式格式
With CTEname [Col Aliases]
  As (Select ...From ...
  )

------------------------------------------------------
With 
  CTEname1 (col names) As (Select) , 
  Ctename2 (col names) As (Select)
Select ...
  From CTEname1
    Inner join CTEname2 On

相关子查询

子查询可出现在Select子句中，也可以出现在Where 子句中；

判断是否为相关子查询的标准为是否引用了外查询中的列；

没有引用外查询中的列，为非相关子查询；先于外查询运行

引用了外查询中的列，为相关子查询，后于外查询运行

批处理

GO(Transact-SQL)

  • what ：向 SQL Server 实用工具发出一批 Transact-SQL 语句结束的信号
  • how ：当前批语句由上一 GO 命令后输入的所有语句组成，如果是第一条 GO 命令，则由即席会话或脚本开始后输入的所有语句组成。
    • GO 命令和 Transact-SQL 语句不能在同一行中。但在 GO 命令行中可包含注释
# GO [count] ;   # count 一个正整数，GO 之前的批处理将执行指定的次数

声明：DECLARE

Declare @test Int, @testtwo Varchar(20) ;
Set @test = 1 ;
Set @testtwo = 'a value' ;

使用Set 和 Select 命令

命令 描述
Set 限制在从表达式中检索数据；
一次只能设置一个变量
Select 可从数据源中检所数据，还可包括其他Select子句(From、Where等）；可使用函数
一次课设置多个变量

Set命令可以使用访问数据源的标量子查询（一个变量）

如果你希望在确保没有行的情况下将变量设置为NULL，以及在不止一行的情况下获得一个错误，这是最佳做法；

Select 命令在检索多行时，只会将最后一行的值存储在变量中

Use adventureworks2012
GO

Declare @productID Int, @producename varchar(25);

Set @productID = 782;  --声明一个变量

Select 
    @productID = productID,  -- 声明多个变量；
    @productname = @productID + name --声明变量可叠加使用
  From production.product
  Order by productID;

Select 声明，即将 @var = value 作为一个整体，当做Select查询中的列去看待即可

声明变量可叠加使用

GO
Declare @productID Int, @producename varchar(25);

Set @productID = 999;

Select 
    @productID = productID,  -- 声明多个变量；
    @productname = @productID + name --声明变量可叠加使用
  From production.product
  Where productID = 1000;  -- 由于ProductID 没有1000，故ProductID仍未NULL

# DECLARE @local_var data_type [= value] 


# 直接声明
declare @mycounter int;

DECLARE @find varchar(30) = 'Man%';   # 使用名为 @find 的局部变量检索所有姓氏以 Man 开头的联系人信息 
--Select ... WHERE LastName LIKE @find; 

# 使用select声明
DECLARE @var1 varchar(30)   
SELECT @var1 = (SELECT Name FROM Sales.Store WHERE CustomerID = 1000) 

# 用声明并赋值过的变量构建一个Select语句并查询
Select lastname,firstname,title
  From employees
  where firstname= @firstnamevariable or region=@regionvariable

# 声明日期，并应用
DECLARE @起始日期 date set @起始日期 = dateadd(dd,-3,getdate())
Select top 100 * From [exchange].[YTX].[v_allcj] where 结算日期 = @起始日期

------------------------------------------------
# 声明多个变量
declare @last_name varchar(30),@fname varchar(20);

DECLARE @起始日期 datatime, @截止日期 datatime
set @起始日期 = '2017-03-07';
set @截止日期 = '2017-03-07';

# 给多个变量赋值
declare @firstnamevariable varchar(20), @regionvariable varchar(30)
set @firstnamevariable='anne'
set @regionvariable ='wa'

------------------------------------------------------------
全局变量
select @@version  --返回数据库版本
select @@error  --返回最后的一次脚本错误
select @@identity  --返回最后的一个自动增长列的id

过程流

If condition 
Statement;


If 1=0
Print 'Line one';
Print 'Line two';

--结果返回Line two；
--if语句之后没有分号； if语句实际上是后面语句的提前；


----------------------------------------------------------------------------------
# 使用Begin / End 有条件地执行多条语句
If condition 
  Begin;
    Multipie Line;
  End;  --每个都有分号

If Exists
  (Select * From production.product Where quantity = 0)
  Begin;
    Print 'Relpenish Inventory';
  End;

If condition
     Single line or Begin/End block of code;
  Else
    Single line or Begin/End block of code;

--

使用While循环

数据清洗

去重复值

--Distinct # 去除完全重复的值
SELECT DISTINCT 机构 FROM allzjb ;


--Row_number() over(partation by col1 order by col2) 某个字段有重复值
--【先根据某相同字段分组，根据其他字段排序，创建临时表；在提取排序=1的信息】

# 对于相同的交易账号，取激活时间最前面的那一个账户的相关信息；
Select *, Row_number() Over(partition by 交易账号 order by 激活时间) as 排序 Into #11 
  From  exchange.ytx.ext_激活客户归属表;  
Select * Into #2 From #11  where 排序 = '1';

NULL

聚合函数中SUM( ) 与AVG( ) 会自动排除NULL进行计算；

Count( * ) 会计算空值；但Count( col )会排除空值

# 测试空值 - IS NULL
SELECT * FROM allzjb   where 机构 is null ;

-------------------------------------------------------
# ISNULL 将NULL替换为某个值
SELECT AVG(ISNULL(Weight, 50)) FROM Production.Product;  # 将Weight中的NULL替换为50

--

## 处理空值 

# Isnull() --将NULL替换为某个值
Select Isnull(col, 0)  -- 对col列进行搜索，并将空值NULL转换为0；也可以是其他任意值/字符串
--isnull(soucre_expression, prlacement_value)  # isnull是T-SQL特有的函数

-----------------------------------------------------------------------------
# Coalesce()
Select Coalesce(Null, Null+1, 1+2, "abc")  返回3
--Coalesce(expression, expression,... ) # 接受一系列表达式或列，返回第一个非空值

-----------------------------------------------------------------------------
# Nullif()

NULL ：未定义的值 / 不存在
NA ：缺失数据
NaN ：无意义的数，比如sqrt(-2)， 0/0。
Inf ：正无穷大
-Inf ：负无穷大

数据运算

两行观测值相减

# 根据某个字段先排序进行编号，创建为#a1；复制为另一个表#a2；通过联合，使得联合的条件为 #a1.rank+1 = #a2.rank
Select *, order by 成交时间 as rank into #a1 From [exchange].[YTX].[v_allcj] as cj;
Select * into #a2 From #a1;

Select a1.*, datediff(ss,#a1.成交时间,#a2.成交时间) s [交易间隔(s)] From #a1 
  Left join #a2 On #a1.rank = #a2.rank +1

# 根据某个字段【分组】后排序编号；再通过唯一列联接，再设置条件 a.rank+1=b.rank 进行相减【理论上应该是 a.rank=b.rank+1】

Select *, Row_number() over(partition by 交易账号 order by 成交时间) as [rank] into #a1
  From [exchange].[YTX].[v_allcj] as cj;
Select * into #a2 From #a1;

Select #a1.*, datediff(ss,#a1.成交时间,#a2.成交时间) as [交易间隔(s)] From #a1 
  Left join #a2 On #a1.交易账号 = #a2.交易账号
  where #a1.rank+1 = #a2.rank
  and datediff(ss,#a1.成交时间,#a2.成交时间) < '60'

注释

参照文档

select * from #100
pivot(

sum(人数) for 资金等级 in ([100万以下])) as B  转置
上一篇 下一篇

猜你喜欢

热点阅读