TSQL拾漏补遗

    做了好多年程序，发现自己已经形成一些技术惯性，对熟练的知识反复应用的过程中，反而忘记了一些非常规的知识。学习本来就是一个循环往复的过程，如果不回头就容易忽视一些技术，让自己的路越走越窄。

    决定开几篇帖子，慢慢复习一些知识，寻找以前被忽视的非常规，这篇是关于TSQL的

 

---

 

   1.compute 和 compute by

    第一个发现被遗漏的是以前觉得很鸡肋的语句

    compute方法用于汇总数据，在SQL Server 2000及SQL SERVER 2005中均存在，不过2005保留此语句是为了向下兼容

    compute与SUM、AVG、MIN、MAX 、COUNT这些聚合函数结合使用，用于汇总数据，并在查询结果中显示为单独结果集。

    by 用于分组，by后面跟的列必须在排序语句中同时体现，否则产生错误

    例如：

select   *   from   order
order   by  goodsid
compute   sum (salecount)  by  goodsid

 

   如取消order by子句将会产生“在排序依据列表中没有找到 COMPUTE BY 子句中的某一项。计算依据列表中的所有表达式也必须同时出现在排序依据列表中。”的错误提示

   

    当 COMPUTE 带有可选的 BY 子句时，符合 SELECT 条件的每个组都有两个结果集：

• 每个组的第一个结果集是明细行集，其中包含该组的选择列表信息。
  
• 每个组的第二个结果集有一行，其中包含该组的 COMPUTE 子句中所指定的聚合函数的小计。
  

     当 COMPUTE 不带可选的 BY 子句时，SELECT 语句有两个结果集：

• 每个组的第一个结果集是包含选择列表信息的所有明细行。
  
• 第二个结果集有一行，其中包含 COMPUTE 子句中所指定的聚合函数的合计。

     注意使用compute后，聚合函数比如指定具体的列，比如count(*)就是错误的，只能为count(userid)

    

    

     COMPUTE 和 GROUP BY 之间的区别汇总如下：

• GROUP BY 生成单个结果集。每个组都有一个只包含分组依据列和显示该组子聚合的聚合函数的行。选择列表只能包含分组依据列和聚合函数。
  
• COMPUTE 生成多个结果集。一种结果集包含每个组的明细行，其中包含选择列表中的表达式。另一种结果集包含组的子聚合，或 SELECT 语句的总聚合。选择列表可包含除分组依据列或聚合函数之外的其他表达式。聚合函数在 COMPUTE 子句中指定，而不是在选择列表中指定。

     这样的区别还是很明显的，有时候用compute来查询汇总数据更方便一些，例如报表

 

     在.net中，datatable中有一个compute方法可以替代此功能

 

 

---

 

 

     2.rollup和cube

     SQL SERVER 2005引入rollup 和 cube用于混合统计，在oracle中早就存在

     微软将rollup与compute by进行比较，给出以下结论：

    

     ROLLUP 操作的结果集与 COMPUTE BY 返回的结果集具有类似的功能。然而，ROLLUP 具有下列优点：

• ROLLUP 返回单个结果集，而 COMPUTE BY 返回多个结果集，而多个结果集会增加应用程序代码的复杂性。
  
• ROLLUP 可以在服务器游标中使用，而 COMPUTE BY 则不可以。
  
• 有时，查询优化器为 ROLLUP 生成的执行计划比为 COMPUTE BY 生成的更为高效。

 

     看一些sql server 2005给出的例子

Item                 Color                Quantity                  
-------------------- -------------------- --------------------------
Table                Blue                 124                       
Table                Red                  223                       
Chair                Blue                 101                       
Chair                Red                  210    

 

 a.使用rollup

SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
            ELSE ISNULL(Item, 'UNKNOWN')
       END AS Item,
       CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
            ELSE ISNULL(Color, 'UNKNOWN')
       END AS Color,
       SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH ROLLUP

Item                 Color                QtySum                    
-------------------- -------------------- --------------------------
Chair                Blue                 101.00                    
Chair                Red                  210.00                    
Chair                ALL                  311.00                    
Table                Blue                 124.00                    
Table                Red                  223.00                    
Table                ALL                  347.00                    
ALL                  ALL                  658.00                    

(7 row(s) affected)

                   

b.使用cube

 

SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'
            ELSE ISNULL(Item, 'UNKNOWN')
       END AS Item,
       CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
            ELSE ISNULL(Color, 'UNKNOWN')
       END AS Color,
       SUM(Quantity) AS QtySum
FROM Inventory
GROUP BY Item, Color WITH CUBE

Item                 Color                QtySum                    
-------------------- -------------------- --------------------------
Chair                Blue                 101.00                    
Chair                Red                  210.00                    
Chair                ALL                  311.00                    
Table                Blue                 124.00                    
Table                Red                  223.00                    
Table                ALL                  347.00                    
ALL                  ALL                  658.00  

ALL                  Blue                 225.00                    
ALL                  Red                  433.00                                       

(7 row(s) affected)

 

 使用cube要比rollip多出两行

cube报告的是Item 和Color所有组合的汇总

rollup没有进行组合，他只对左列进行组合，并没有把左列和右列进行组合汇总

这是cube和rollup的区别

    

---

 

    3.有关一些简写

    left outer join 和 left join等同的(有关内联 外联 左联 右联等等一些问题就不辨别了） 

    自然right同

    *=符号可以替代left join  

    =*可以替代right join

    这两个缩写符号可是懒人的福音啊,对于我这种老是把join拼写成jion 把outer写成out的人来说是再好不过了。不过如果团队协作，还是写清晰点好

    select a.*,b.* from a,b where a.aid*=b.bid

   

---

 

    4.自定义数据类型

    sp_addtype 存储过程用于自定义数据类型

    sp_droptype 存储过程用于删除自定义数据类型

   

    sp_addtyped定义如下：

    sp_addtype [ @typename = ] type,

    [ @phystype = ] system_data_type

    [ , "identity" |[ @nulltype = ] 'null_type' ]

 

    其中：

    @typeName：用户自定义数据类型的名称

    @phystype：创建自定义类型时依据的SQL Server 数据类型

    @nulltype /identity：该自定义类型处理空值的方式/或是否为标识字段

    @owner：类型的所有者，可以为空

    示例：EXEC SP_ADDTYPE password_type,'varchar(30)','not null'

  

    自定义数据类型在多数据库 多表中统一数据类型有重要的意义，并且可以统一相同数据类型的默认值和为其设定规则

   

    a.为自定义数据类型指定默认值

    分为两步走

    首先使用CREATE DEFAULT语句创建一个默认对象

    CREATE DEFAULT Default_Password AS   '123456'

    然后使用sp_bindefault存储过程将该默认对象绑定到自定义数据类型之上

    EXEC SP_BINDEFAULT 'Default_Password','password_type'

   

    反向过程

    首先取消所有默认对象与自定义数据类型的绑定关系

    EXEC SP_UNBINDEFAULT 'password_type'

    然后才可以删除默认对象

   

    IF EXISTS (SELECT name FROM sys.objects

         WHERE name = 'Default_Password'

            AND type = 'D')

    DROP DEFAULT Default_Password

 

    2.为自定义数据类型指定规则

    很多情况下，可能需要某一字段值必须满足某些条件来约束字段的设置，这就是为自定义数据类型指定规则的含义

    首先创建规则

    CREATE RULE PasswordLength_rule

    AS

    LEN(@Password) >= 6 AND LEN(@Password) <=10

    然后将规则与指定自定义类型绑定

    SP_BINDRULE 'PasswordLength_rule ','password_type'

 

 

    以上内容在model数据库下定义，就可以多个数据库共用

    

 

 

 

    

转载于:https://www.cnblogs.com/ErosSignum/archive/2010/07/08/1773795.html