【SQL Server学习笔记】SELECT语句

本文主要记录在学习select语句中，需要特别注意的知识点。版本是SQL Server 2008 R2。

1、让%不被认为是通配符，可以用escape关键字。

--查找产品名称中包含%字符的记录，用/表示后面的%不是通配符SELECT *FROM PRODUCTWHERE PRODUCT_NAME LIKE '%/%%' ESCAPE '/'

需要特别注意的是，在代码中任何要转意的地方，必须都要写上 escape，比如：

select *,       case when v_name like '%/_%'  escape '/' then 1 else 0 endfrom v where v_name like '%/_%' escape '/'

更进一步，发现charindex函数并不会把 _ 当成特殊字符，而就是当成下划线本身，所以：

select *,       case when v_name like '%/_%'  escape '/' then left(v_name,charindex('_',v_name)-1) else v_name end as translate_namefrom (select 'abc_001' v_nameunion allselect 'abc001' )vwhere v_name like '%/_%' escape '/'/*v_nametranslate_nameabc_001abc*/

Great！给自己点赞

2、声明变量、赋值。

--SQL Server 2008之前的版本DECLARE @prod_name NVARCHAR(10)SET @prod_name = 'abc'SELECT *FROM productWHERE prod_name LIKE '%' + @prod_name + '%'--SQL Server 2008中初始化变量，一步到位DECLARE @prod_name NVARCHAR(10) = 'abc';

3、GROUP BY ALL

--对OrderDate列的所有值进行分组--不满足where条件的OrderDate也会出现，汇总值为NULLSELECT OrderDate,       sum(TotalDue) TotalFROM Sales.SalesOrderHeaderWHERE OrderDate BETWEEN '2001-07-31' AND '2001-07-31'GROUP BY ALL OrderDate

4、count(*)会统计NULL，count(字段)不会统计NULL。使用avg(字段)、count(字段)时，NULL不会有负面影响。

5、ORDER BY

--排序的字段可以不出现在select子句中SELECT NameFROM Production.ProductORDER BY ListPrice ASC--select中用distinct时，order by中的字段必须出现在select列表中SELECT DISTINCT        Name,       ListPriceFROM Production.ProductORDER BY ListPrice ASC--在排序结果集中使用top关键字SELECT TOP 10        NameFROM Production.ProductORDER by ListPrice ASC/*==========================================在早期版本中，由于top子句不能使用局部变量，所以为了限制返回的结果集，用set rowcount X，在最新的版本中直接用top关键字，而且top子句可以用变量============================================*/--限制10行SELECT TOP (10)        NameFROM Production.Product--限制返回10%的行SELECT TOP (10) PERCENT         NameFROM Production.Product--在top中使用变量DECLARE @percent TINYINT =80SELECT TOP (@percent) PERCENT         NameFROM Production.Product

6、union中的order by

select SalesPersonID,SalesQuotafrom sales.SalesPersonwhere SalesQuota > 200000union allselect SalesPersonID,SalesQuotafrom sales.SalesPersonwhere SalesQuota <=200000order by SalesQuota   --此处的order by是对合并之后的结果集进行的排序

7、CROSS APPLY与INNER JOIN 差不多，OUTER APPLY与LEFT JOIN差不多。

CREATE TABLE p (prod_id int,prod_name VARCHAR(100))INSERT INTO PSELECT 1000,'大白菜' UNION ALL SELECT 1001,'西红柿'CREATE TABLE PROD(prod_id INT,prod_name VARCHAR(100))INSERT INTO ProdSELECT 1000,'大白菜' UNION ALL SELECT 11,'胡萝卜'--创建内联表值函数             create FUNCTION dbo.fn_table(@prod_id int) RETURNS TABLEASRETURN       SELECT prod_id,             prod_name      FROM p      WHERE PROD_ID =@prod_idgo      /*===================================================================1.cross apply表值函数，返回：prod、表值函数结果集，两者的交集2.说明cross apply可以不是表值函数，只需要是一个结果集就可以3.说明crosss apply与inner join效果一样4.outer apply以第一个表为主表，会返回所有主表的记录，  如果辅表中没有关联的记录，那么辅直中的字段值为NULL,相当于left join=====================================================================*/SELECT *FROM PROD mcross apply dbo.fn_table(m.PROD_ID) rSELECT *FROM prod mCROSS apply (SELECT * FROM p WHERE p.prod_id = m.prod_id)rSELECT *FROM prod mINNER JOIN p        ON p.prod_id = m.prod_idSELECT *FROM prod m  --主表OUTER apply dbo.fn_table(m.prod_id) r  --辅表SELECT *FROM prod mouter apply (SELECT prod_id,prod_name FROM p WHERE p.prod_id = m.prod_id)rSELECT *FROM prod mleft JOIN p        ON p.prod_id = m.prod_id

8、使用tablesample返回随机行

/*=======================================================实际上TABLESAMPLE不是限制返回的行数，而是限制返回数据页的页数下面的例子是返回整个表中80%的行，进一步转化成应该返回的页数的百分比但由于每次是随机抽样选取页，而有些页中记录多，有些则少，所以每次执行这个语句，都会返回不同数目的记录=========================================================*/SELECT *FROM PRODUCTtablesample system (80 percent)/*=======================================================下面的例子指定了返回的行数为10，这个行数会转化成行数的百分比，进一步转化应该返回的页数的百分比，比如整个表一共100行，那么就会返回10%的行，如果这个表一样有10页，那么就会返回10页，但由于每次是随机抽样选取页，而有些页中行数多，有些行数则少，甚至没有数据，所以每次执行这个语句，都会返回不同数目的行=========================================================*/SELECT *FROM producttablesample system (10 rows)--repeatable的种子数必须大于0，可任意选取，这样在每次执行时，都会返回同一组行SELECT *FROM PRODUCTtablesample system (80 percent)repeatable(1)

9、POVIT与UNPOVIT

/*========================================================POVIT:prod_name字段内的值为字符串，不必用引号括起来，而是必须用方括号,select子句中的列，不能出现prod表的字段，实际都是引用了pp中的字段(下面的代码特意加上了前缀，当然不加前缀也是可以的，第二段代码就没加前缀)==========================================================*/SELECT pp.prod_code,       pp.[大白菜],       pp.[胡萝卜],       pp.[西红柿]FROM PROD ppivot(count(prod_id)FOR prod_name IN ([大白菜],[胡萝卜],[西红柿]) --把in中的行值旋转成列) AS pp--通过分组，case when来实现类似于POVIT的行转列select p.prod_code,       COUNT(case when prod_name = '大白菜' then 1 else null end) as '大白菜',       COUNT(case when prod_name = '胡萝卜' then 1 else null end) as '胡萝卜',       COUNT(case when prod_name = '西红柿' then 1 else null end) as '西红柿'      from PROD pgroup by p.prod_code--UNPIVOTif object_id('tb') is not null   drop table tbgocreate table tb(a int,b int,c int)insert tbvalues(1,1,1)select e,nfrom tbunpivot(n for e in (a,b,c))t/*ena1b1c1*/--通过union all来实现select 'a' as e,a as n from tbunion allselect 'b' as n,b from tbunion allselect 'c' as n,c from tb/*n        aa        1b        1c        1*/   

10、汇总时，rollup关键字运用时要注意字段的顺序，对字段列表中的各个字段从左到右，进行分层级的汇总。

 cube关键字不关注字段顺序，按照多字段的各种值进行多维度的汇总。

/*==========================================================Grouping Sets(a,b,c)定义就是: group by a  union all   group by b  union all  group by c ，也就是把GROUP BY a产生的结果集、GROUP BY b产生的结果集、GROUP BY c产生的结果集合并在一起。 group by grouping sets((a,b,c)) 加括号后,里面是一个整体,特别要注意加括号和不加括号的区别,等价于:group by a,b,cgroup by grouping sets(a,(b,c)) 等价于group by a union allgroup by b,cgroup by grouping sets(a),         grouping sets(b),         grouping sets(c)等价于group by a,b,c   group by grouping sets(a),         grouping sets((b,c))等价于group by a,b,c  group by grouping sets(a),         grouping sets(b,c)等价于group by a,bunion allgroup by a,c   group by a,         b,         grouping sets((b,c))等价于group by a,b,cgroup by a,         b,         grouping sets(b,c)等价于group by a,bunion allgroup by a,b,c  group by a,         b,         c,         grouping sets(b,c)等价于group by a,b,cunion allgroup by a,b,c   ==============================================================================================*/--注意：由于union all不去重,所以结果集中会出现重复的记录select *from (values(1,2,3),      (2,3,4),      (3,4,5))x(a,b,c)group by a,         b,         grouping sets(b,c) 

 

/*==============================================================grouping函数:可以区分使用cube,rollup,grouping sets聚合生成的行返回0表示NULL是实际值返回1表示NULL不是实际值,不是数据本省,而是聚合过程产生的================================================================*/select i.Shelf,       i.LocationID,       case when GROUPING(i.Shelf) = 0 and     --数据本身                 GROUPING(i.LocationID) = 1    --聚合产生的                 then 'Shelf Total'                     when GROUPING(i.Shelf) = 1 and                 GROUPING(i.LocationID) = 0                  then 'Location Total'            when GROUPING(i.Shelf) = 1 and     --聚合产生的                 GROUPING(i.LocationID) = 1    --聚合产生的                 then 'Grand Total'        end                     from production.ProductInventory iwhere LocationID = 2group by cube (i.Shelf,i.LocationID)

 

/*==========================================================grouping_id函数可以区分并操作那些使用cube,                               rollup,                               grouping sets聚合生成的行返回0表示NULL是实际值返回1表示NULL不是实际值,不是数据本身,而是聚合过程产生的举例grouping_id函数返回cube(i.Shelf,i.LocationID,i.Bin)结果集，2的3次方共8个,第一列是返回值：0ShelfLocationIDBin1ShelfLocationID  2ShelfBin3Shelf4LocationIDBin5LocationID6Bin7============================================================*/select i.Shelf,       i.LocationID,       i.bin,       case grouping_id(i.Shelf,i.LocationID,i.Bin)            when 0 then 'Shelf-Location-Bin'            when 1 then 'Shelf-Location Total'            when 2 then 'Shelf-Bin Total'            when 3 then 'Shelf Total'            when 4 then 'Location-Bin Total'            when 5 then 'Location Total'            when 6 then 'Bin Total'            when 7 then 'Grand Total'                                end,       grouping_id(i.Shelf,i.LocationID,i.Bin) as grouping_id_NUM                     from production.ProductInventory iwhere i.LocationID in (3)  --限制了条件      and i.Bin in (1,2)group by cube (i.Shelf,i.LocationID,i.Bin)order by i.Shelf desc,         i.LocationID desc,         i.Bin desc 

11、CTE通用表表达式

declare @object_id numeric(10,0) = 0, --sql server 2008的新语法        @num int = 0 ;--=====在游标中使用CTE，可以写复杂语句========================declare tt cursor  for   with c1as(select object_id from sys.objects),c2as(select object_id from sys.tables)select c1.object_id,       count(c2.object_id) as numfrom c1 inner join c2 on c1.object_id = c2.object_idgroup by c1.object_id--=====================================================open tt                                      --打开游标fetch next from tt into object_id,@num       --取出游标中的第一条记录while @@FETCH_STATUS =0                      --是否到游标尾部begin   print 'out:' + right(space(10)+ cast(@object_id as varchar),8) + ':'  --格式化输出                + right(space(10)+ cast(@num as varchar),8);                      fetch next from tt into @org_id,@emp_id,@numendclose tt          --关闭游标deallocate tt     --释放游标资源

--通过递归的CTE实现：a列值相同，连接v列的字符串CREATE TABLE t(idd INT ,a INT ,v VARCHAR(100))INSERT INTO tVALUES (1,1,'a'),   --添加多条记录       (2,2,'b'),       (3,1,'c'),       (4,1,'d'),       (5,3,'e'),       (6,3,'f')       ;WITH cAS(SELECT idd,       a,        v,       row_number() OVER(PARTITION BY a   --通过分组再排序，把需要连接的字符串排在一起                             ORDER BY idd) AS rownumFROM t),ccas(SELECT idd,       a,       CAST(v AS varchar(1000)) AS v,       rownum,       1 AS levelFROM cUNION ALLSELECT cc.idd,       cc.a,       CAST(cc.v +','+ c.v AS VARCHAR(1000)),  --保存已经连接的字符串       cc.rownum+1,       LEVEL + 1FROM cc,cWHERE cc.a = c.a AND      cc.rownum + 1 = c.rownum   --通过rownum加1，和下一个字符串连接)SELECT * FROM ccWHERE level = (SELECT max(level) FROM cc ccc WHERE ccc.a = cc.a )