SQL复杂的查询

Biegral 2016/9/19 8:10:22 3388

嵌套查询

定义：内查询先执行，内查询给外查询提供参数，外查询再查询。
语法：select * from 表1  where  字段=（select *from 表2）;

关联子查询

定义：外查询先执行，给内查询提供参数，内查询查询后给外查询提供参数，外查询再执行。
语法：select    字段=（select *from 表2） from 表1 ;

处理NULL数据——ISNULL函数

定义：ISNULL()接受一个变量或者表达式来验证是否是一个NULL值。如果确实是NULL值，那么函数返回其他预先指定的值。如果原值不是NULL,则返回原值。
语法：ISNULL(表达式，代替值)

派生表

定义：派生表由一个查询结果集的列和行构成
条件：
1.将生成结果集的查询用小括号括起来
2.给查询结果取别名，这样他可以作为表被引用
语法：SELECT  查询列表内容  FROM (表1) AS 查询语句别名  JOIN (表2)
注意：派生表没有索引

使用通用表达式(CTE)

定义：CTE最简单的用法类似于预先（而非内联）什么的派生表
WITH AS短语：也叫做子查询部分（subquery factoring），可以让你做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。
案例1  使用变量
declare @t table(name nvarchar(50));
insert into @t(name)(select CategoryName from Category)
SELECT * FROM Category where CategoryName in (select * from @t);
案例2 with  变量 as 
with cr as(
select CategoryName from Category
)
select * from Category where CategoryName in (select * from cr)

使用CTE 注意事项

1. CTE后面必须直接跟使用CTE的SQL语句（如select、insert、update等），否则，CTE将失效。如下面的SQL语句将无法正常使用CTE：
with
cr as
(
    select CountryRegionCode from person.CountryRegion where Name like 'C%'
)
select * from person.CountryRegion  -- 应将这条SQL语句去掉
-- 使用CTE的SQL语句应紧跟在相关的CTE后面 --
select * from person.StateProvince where CountryRegionCode in (select * from cr)

2. CTE后面也可以跟其他的CTE，但只能使用一个with，多个CTE中间用逗号（,）分隔，如下面的SQL语句所示：
with
cte1 as
(
    select * from table1 where name like 'abc%'
),
cte2 as
(
    select * from table2 where id > 20
),
cte3 as
(
    select * from table3 where price < 100
)
select a.* from cte1 a, cte2 b, cte3 c where a.id = b.id and a.id = c.id

3. 如果CTE的表达式名称与某个数据表或视图重名，则紧跟在该CTE后面的SQL语句使用的仍然是CTE，当然，后面的SQL语句使用的就是数据表或视图了，如下面的SQL语句所示：

--  table1是一个实际存在的表

with
table1 as
(
    select * from persons where age < 30
)
select * from table1  --  使用了名为table1的公共表表达式
select * from table1  --  使用了名为table1的数据表

4. CTE 可以引用自身，也可以引用在同一 WITH 子句中预先定义的 CTE。不允许前向引用。

5. 不能在 CTE_query_definition 中使用以下子句：

（1）COMPUTE 或 COMPUTE BY

（2）ORDER BY（除非指定了 TOP 子句）

（3）INTO

（4）带有查询提示的 OPTION 子句

（5）FOR XML

（6）FOR BROWSE

6. 如果将 CTE 用在属于批处理的一部分的语句中，那么在它之前的语句必须以分号结尾，如下面的SQL所示：

declare @s nvarchar(3)
set @s = 'C%'
;  -- 必须加分号
with
t_tree as
(
    select CountryRegionCode from person.CountryRegion where Name like @s
)
select * from person.StateProvince where CountryRegionCode in (select * from t_tree)

CTE除了可以简化嵌套SQL语句外，还可以进行递归调用。

 使用EXISTS运算符


定义：使用exists并不真正的返回数据，而是根据是否存在数据满足查询中exists语句建立的条件标准，返回true或者false结果，只要满足一个记录就返回tre。

语法 ：select 查询列表 from 表 wher exists (条件);/select 查询列表 from 表 wher not exists (条件);

例子：
IF EXISTS (
SELECT 'True' FROM sys.databases where name = 'abc'
)
BEGIN
DROP DATABASE abc;
END
ELSE
BEGIN
CREATE DATABASE abc;
END
GO

混合数据类型CAST和CONVERT

定义：都可以执行数据类型转换。不同的是CONVERT还提供一些日期格式转换，而CAST没有这个功能。
语法： CAST列名 AS 数据类型);
CONVERT(数据类型，列名);
案例1：select '价格为：'+cast(Price as nvarchar)+'元' from CategoryDetail;
案例2：

select CONVERT(varchar, getdate(), 120 )

2004-09-12 11:06:08

select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','')

20040912110608

select CONVERT(varchar(12) , getdate(), 111 )

2004/09/12

select CONVERT(varchar(12) , getdate(), 112 )

20040912

select CONVERT(varchar(12) , getdate(), 102 )

2004.09.12

select CONVERT(varchar(12) , getdate(), 101 )

09/12/2004

select CONVERT(varchar(12) , getdate(), 103 )

12/09/2004

select CONVERT(varchar(12) , getdate(), 104 )

12.09.2004

select CONVERT(varchar(12) , getdate(), 105 )

12-09-2004

select CONVERT(varchar(12) , getdate(), 106 )

12 09 2004

select CONVERT(varchar(12) , getdate(), 107 )

09 12, 2004

select CONVERT(varchar(12) , getdate(), 108 )

11:06:08

select CONVERT(varchar(12) , getdate(), 109 )

09 12 2004 1

select CONVERT(varchar(12) , getdate(), 110 )

09-12-2004

select CONVERT(varchar(12) , getdate(), 113 )

12 09 2004 1

select CONVERT(varchar(12) , getdate(), 114 )

11:06:08.177

select getdate()

结果：2003-12-28 16:52:00.107

select convert(char(8),getdate(),112)

结果：20031228

select convert(char(8),getdate(),108)

结果：16:52:00

select convert(char(8),getdate(),112)

                                   |

                             指日期格式

规则如下：

1 101 美国 mm/dd/yyyy

2 102 ANSI yy.mm.dd

3 103 英国/法国 dd/mm/yy

4 104 德国 dd.mm.yy

5 105 意大利 dd-mm-yy

6 106 - dd mon yy

7 107 - mon dd, yy

8 108 - hh:mm:ss

- 9 或 109 (*)   默认值 + 毫秒 mon dd yyyy hh:mi:ss:mmmAM（或 PM）

10 110 美国 mm-dd-yy

11 111 日本 yy/mm/dd

12 112 ISO yymmdd

- 13 或 113 (*)   欧洲默认值 + 毫秒 dd mon yyyy hh:mm:ss:mmm(24h)

14 114 - hh:mi:ss:mmm(24h)

- 20 或 120 (*)   ODBC 规范 yyyy-mm-dd hh:mm:ss[.fff]

- 21 或 121 (*)   ODBC 规范（带毫秒） yyyy-mm-dd hh:mm:ss[.fff]

- 126(***) ISO8601 yyyy-mm-dd Thh:mm:ss:mmm（不含空格）

- 130* 科威特 dd mon yyyy hh:mi:ss:mmmAM

- 131* 科威特 dd/mm/yy hh:mi:ss:mmmAM

          关于Emaker中字段的格式转换和字段间的运算代码可以加到属性里的“格式转换（读出）”和“格式转换（写入）”，table字段设定里的“字段”位置也可以灵活加函数。比如：'AF'+ID 或者ID+'/'+PWD ,convert(varchar(50),F1) ,

convert(int,%)-19110000 (读出)

convert(char(8),convert(int,%)+19110000) （写入）

实现行的合计运算等等。加入：%系统变量%，则是调用在Emaker 系统中设定的系统变量。


使用MERGE命令同步数据

定义：通过MERGE命令，可以将多个DML操作语句（INSERT,UPDATE和DELETE）组合成一个整体操作，从而提高性能和简化事物

案例：http://www.cnblogs.com/CareySon/archive/2012/03/07/2383690.html

使用BY TARGET 和 BY SOURCE

定义：通常都可以将一个特定的合并操作映射到一个特定的匹配场景
NOT MATCHED [BY TARGET]: 这通常对应根据表中的数据将行插入到表中的场景。
MATCHED [BY TARGET]:这暗示了行已经存在与目标表上，因此很可能对目标表执行更新操作。
NOT MATCHED BY SOURCE:这通常用于处理源表中缺少（可能是已删除）的行，在这种场景下，通常将删除目标表中的行

使用OUTPUT 子句收集受影响的行

定义：在执行DML操作时，有时会要操作受影响的行。INSERT、DELETE和MERGE命令提供了一个实际执行操作的细节和数据输出到SELECT语句的选项。OUTPUT关键字实际上可替代SELECT,但是同时它还有一些特殊运算符来匹配合并的数据。
1.$action:只用于MERGE.返回INSERTED、UPDATED、DELETED，表明对特定行执行的操作。
2.inserted:用于MERGE,INSERT,UPDATE。对内部工作表的引用，该工作表包含了特定行插入的数据引用。注意，这包括了已更新数据的当前值。
3.deleted:用于MERGE,DELETE或UPDATE。对内部工作表的引用，该工作表包含了从给定行中删除的数据的引用。注意，这包括了已更新数据之前的值。

//创建XXX_Data.mdf文件日志
CREATE DATABASE XXX
ON (FILENAME = 'E:\XXX_Data.mdf')
FOR ATTACH_REBUILD_LOG//意思是附加重建日志

研究窗口化参数

定义：使用ROW_NUMBER、RANK,DENSE_RANK,NTILE,对结果进行记数，从而确定每一行在结果集中的具体位置。
ROW_NUMBER 只是为每个返回的行输出唯一的、递增的值。
案例：
SELECT name, (ROW_NUMBER() OVER (ORDER BY score desc)) as '排名' FROM StudentScore;

PARTITION BY 子句
定义：规定查询结果按什么进行分区
案例1：SELECT name,Class,(ROW_NUMBER() OVER (PARTITION BY Class ORDER BY score desc)) AS '排名' FROM StudentScore;
案例2：WITH Randked AS(
SELECT name,Class,(ROW_NUMBER() OVER (PARTITION BY Class ORDER BY score desc)) AS '排名' FROM StudentScore
)
SELECT * FROM Randked;
RANK,DENSE_RANK和NTILE
定义：解决排序中的重复问题，例如成绩出现重复那么排名保留相同，ROW_NUMBER() 则与其相反。
案例：
WITH Randked AS(
SELECT name,Class,(RANK() OVER (ORDER BY score desc)) AS '排名' FROM StudentScore
)
SELECT * FROM Randked;


WITH Randked AS(
SELECT name,Class,(DENSE_RANK() OVER (ORDER BY score desc)) AS '排名' FROM StudentScore
)
SELECT * FROM Randked;


WITH Randked AS(
SELECT name,Class,(NTILE(1) OVER (ORDER BY score desc)) AS '排名' FROM StudentScore
)
SELECT * FROM Randked;

一次一个数据块：特殊的查询分页

--前20行
--SELECT TOP 20 ProductID,ProductNumber,Name FROM Production.Product;
--第二页
--SELECT ProductID,ProductNumber,Name FROM Production.Product ORDER BY ProductNumber OFFSET 20 ROWS FETCH NEXT 20 ROW ONLY;
--第三页
--SELECT ProductID,ProductNumber,Name FROM Production.Product ORDER BY ProductNumber OFFSET 40 ROWS FETCH NEXT 20 ROW ONLY;

注意：
1.使用OFFSET....FETCH子句时必须同时使用ORDER BY
2.可以单独使用OFFSET,但是不可以单独使用FETCH
3.不可以同时使用SELECT TOP 和 OFFSET.....FETCH
4.可以使用算术式或变量来确定偏移量多少行或获取多少行，但是不可以使用标量子查询。
本文转载:CSDN博客

阅读排行

分类

归档