sqlserver常用知识点备忘录（持续更新）

背景 一个项目的开发，离不开数据库的相关操作，表/视图设计，存储过程，触发器等等数据库对象的操作是非常频繁的。有时候，我们会查找系统中类似的代码，然后复制/粘贴进行再进行相应的修改。本文的目的在于归纳、总结sqlserver数据库的常用操作，并不断更

背景

　　一个项目的开发，离不开数据库的相关操作，表/视图设计，存储过程，触发器等等数据库对象的操作是非常频繁的。有时候，我们会查找系统中类似的代码，然后复制/粘贴进行再进行相应的修改。本文的目的在于归纳、总结sqlserver数据库的常用操作，并不断更新。期以备忘！

P1 sql的执行顺序

　　sql语句是操作数据库的工具，了解sql的执行顺序会极大地帮助我们提高我们编写的sql的执行效率。见以下代码：

(<span>8</span>)<span>SELECT</span> (<span>9</span>)<span>DISTINCT</span>  (<span>11</span>)<span><span>Top</span> Num<span>></span> <span><span>select</span> list<span>></span><span>
(</span><span>1</span>)<span>FROM</span> <span>[</span><span>left_table</span><span>]</span><span>
(</span><span>3</span>)<span>join_type<span>></span> <span>JOIN</span> <span>right_table<span>></span><span>
(</span><span>2</span>)<span>ON</span> <span>join_condition<span>></span><span>
(</span><span>4</span>)<span>WHERE</span> <span>where_condition<span>></span><span>
(</span><span>5</span>)<span>GROUP</span> <span>BY</span> <span>group_by_list<span>></span><span>
(</span><span>6</span>)<span>WITH</span> <span>CUBE <span>|</span> RollUP<span>></span><span>
(</span><span>7</span>)<span>HAVING</span> <span>having_condition<span>></span><span>
(</span><span>10</span>)<span>ORDER</span> <span>BY</span> <span>order_by_list<span>></span></span></span></span></span></span></span></span></span></span></span>

1. FROM：对FROM子句中的前两个表执行笛卡尔积（Cartesian product)(交叉联接），生成虚拟表VT1
2. ON：对VT1应用ON筛选器。只有那些使为真的行才被插入VT2。
3. OUTER(JOIN)：如 果指定了OUTER JOIN（相对于CROSS JOIN 或(INNER JOIN),保留表（preserved table：左外部联接把左表标记为保留表，右外部联接把右表标记为保留表，完全外部联接把两个表都标记为保留表）中未找到匹配的行将作为外部行添加到 VT2,生成VT3.如果FROM子句包含两个以上的表，则对上一个联接生成的结果表和下一个表重复执行步骤1到步骤3，直到处理完所有的表为止。
4. WHERE：对VT3应用WHERE筛选器。只有使为true的行才被插入VT4.
5. GROUP BY：按GROUP BY子句中的列列表对VT4中的行分组，生成VT5.
6. CUBE|ROLLUP：把超组(Suppergroups)插入VT5,生成VT6.
7. HAVING：对VT6应用HAVING筛选器。只有使为true的组才会被插入VT7.
8. SELECT：处理SELECT列表，产生VT8.
9. DISTINCT：将重复的行从VT8中移除，产生VT9.
10. ORDER BY：将VT9中的行按ORDER BY 子句中的列列表排序，生成游标（VC10).
11. TOP：从VC10的开始处选择指定数量或比例的行，生成表VT11,并返回调用者。

　总的来说，select的列是最后一步被执行的，而From的Table是首先被执行的。

P2 创建带Try。。。Catch的存储过程模板

　　Copy下面的代码，然后新建查询，就可以写sql语句，执行完后，一个你自己的存储过程就建立好了！

<span>USE</span> <span>[</span><span>DB</span><span>]</span><span>--</span><span>设定对应的数据库</span>
<span>GO</span>
<span>SET</span> ANSI_NULLS <span>ON</span>
<span>GO</span>
<span>SET</span> QUOTED_IDENTIFIER <span>ON</span>
<span>GO</span>
<span>--</span><span> =============================================</span><span>
--</span><span> AUTHOR:</span><span>
--</span><span> DESCRIBE:</span><span>
--</span><span> =============================================</span>
<span>CREATE</span> <span>PROCEDURE</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>UP_InsertJHBData</span><span>]   </span><span>--</span><span>存储过程名</span>
<span>    (
      </span><span>@CustomerName</span> <span>VARCHAR</span>(<span>50</span>)             <span>--</span><span>参数</span>
<span>    )
</span><span>AS</span> 
    <span>BEGIN</span>    
        <span>SET</span> NOCOUNT <span>ON</span>                     <span>--</span><span>提高性能的，必须要有</span>
        <span>DECLARE</span> <span>@Now</span> <span>DATETIME</span>
        <span>SET</span> <span>@Now</span> <span>=</span> <span>GETDATE</span>()               <span>--</span><span>所有操作保证统一时间</span>
        
        <span>BEGIN</span><span> TRY 
        </span><span>--</span><span>在这里写SQL</span>
        <span>END</span><span> TRY
        
        </span><span>BEGIN</span><span> CATCH    
            </span><span>DECLARE</span> <span>@ErrorMessage</span> <span>NVARCHAR</span>(<span>4000</span><span>) ;
            </span><span>DECLARE</span> <span>@ErrorSeverity</span> <span>INT</span><span> ;
            </span><span>DECLARE</span> <span>@ErrorState</span> <span>INT</span><span> ;
                   
            </span><span>SELECT</span>  <span>@ErrorMessage</span> <span>=</span><span> ERROR_MESSAGE() ,
                    </span><span>@ErrorSeverity</span> <span>=</span><span> ERROR_SEVERITY() ,
                    </span><span>@ErrorState</span> <span>=</span><span> ERROR_STATE() ;
            </span><span>PRINT</span> <span>@ErrorMessage</span> 
            <span>RAISERROR</span>(<span>@ErrorMessage</span>,  <span>--</span><span> Message text.</span>
                        <span>@ErrorSeverity</span>, <span>--</span><span> Severity.</span>
                        <span>@ErrorState</span>     <span>--</span><span> State.</span>
<span>                        ) ;
            </span><span>RETURN</span> <span>-</span><span>1</span><span> ;
        </span><span>END</span><span> CATCH
    </span><span>END</span>

P3 创建带事务的存储过程模板

　　只是将带Try。。。Catch的存储过程的模板中加入了事务的控制，使用类似

<span>USE</span> <span>[</span><span>DB</span><span>]</span>
<span>GO</span>

<span>SET</span> ANSI_NULLS <span>ON</span>
<span>GO</span>
<span>SET</span> QUOTED_IDENTIFIER <span>ON</span>
<span>GO</span>
<span>--</span><span> =============================================</span><span>
--</span><span> AUTHOR:</span><span>
--</span><span> DESCRIBE:</span><span>
--</span><span> =============================================</span>
<span>CREATE</span> <span>PROCEDURE</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>UP_InsertJHBData</span><span>]</span><span>--</span><span>存储过程名</span><span>
--</span><span>参数</span>
<span>    (
      </span><span>@CustomerName</span> <span>VARCHAR</span>(<span>50</span><span>)
    )
</span><span>--</span><span>参数</span>
<span>AS</span> 
    <span>BEGIN</span>   
        <span>SET</span> NOCOUNT <span>ON</span> ;<span>--</span><span>提高性能的，必须要有</span>
        <span>DECLARE</span> <span>@Now</span> <span>DATETIME</span><span> ;
        </span><span>SET</span> <span>@Now</span> <span>=</span> <span>GETDATE</span>() ;<span>--</span><span>所有操作保证统一时间</span>
       
        <span>BEGIN</span><span> TRY 
           </span><span>BEGIN</span> <span>TRANSACTION</span> myTrans ;<span>--</span><span>开始事务</span>
           <span>--</span><span>在这里写SQL</span>
           <span>COMMIT</span> <span>TRANSACTION</span> myTrans ;<span>--</span><span>事务提交语句</span>
        <span>END</span><span> TRY
        
        </span><span>BEGIN</span><span> CATCH
            </span><span>ROLLBACK</span> <span>TRANSACTION</span> myTrans<span>--</span><span> 始终回滚事务</span>
            <span>--</span><span>抛出异常</span>
            <span>DECLARE</span> <span>@ErrorMessage</span> <span>NVARCHAR</span>(<span>4000</span><span>) ;
            </span><span>DECLARE</span> <span>@ErrorSeverity</span> <span>INT</span><span> ;
            </span><span>DECLARE</span> <span>@ErrorState</span> <span>INT</span><span> ;
            </span><span>SELECT</span>  <span>@ErrorMessage</span> <span>=</span><span> ERROR_MESSAGE() ,
                    </span><span>@ErrorSeverity</span> <span>=</span><span> ERROR_SEVERITY() ,
                    </span><span>@ErrorState</span> <span>=</span><span> ERROR_STATE() ;
            </span><span>RAISERROR</span>(<span>@ErrorMessage</span>,  <span>--</span><span> Message text.</span>
                 <span>@ErrorSeverity</span>, <span>--</span><span> Severity.</span>
                 <span>@ErrorState</span>     <span>--</span><span> State.</span>
<span>                 ) ;
        </span><span>END</span><span> CATCH
    </span><span>END</span>

P4 批量插入

　　或者生成测试数据，或者填充临时表，我们都会碰到批量插入表的需求，此时，针对被插入的表是否存在分以下两种情况：

1. 被插入的表存在，使用以下sql，达到将表#Table2Name中的ID和Name两列的数据插入表#Table1Name中

   1. <span>INSERT</span> <span>INTO</span> #Table1Name <span>SELECT</span> ID,NAME <span>FROM</span> #Table2Name

2. 被插入的表不存在，使用以下sql，达到创建表#Table1Name，并将表#Table2Name中的ID和Name两列的数据插入表#Table1Name中

   1. <span>SELECT</span> ID,NAME <span>INTO</span> #Table1Name <span>FROM</span> #Table2Name

P5 批量更新

　　链接两个表，通过第一张表的数据去批量地更新第二张表，使用以下的sql

<span>　　　　　　　　 UPDATE</span><span>  t2
               </span><span>SET</span>     t2.FirstSaleOrderDate <span>=</span><span> t1.FirstSaleOrderDate ,
                       t2.LastSaleOrderDate </span><span>=</span><span> t1.LastSaleOrderDate
               </span><span>FROM</span><span>    #T_ValidSODate t1
               </span><span>INNER</span> <span>JOIN</span> #T_PendingReport t2 <span>ON</span> t1.GiftCardNO <span>=</span> t2.GiftCardNO

P6 循环模板

　　在存储过程中，经常会生成一些临时表，然后循环临时表的数据进行处理，以下模板可以帮助伙伴们快速处理此类需求

<span>　　 --</span><span>生成带行号的临时表数据，并插入临时表#T_Table中</span>
    <span>SELECT</span> ROW_NUMBER() <span>OVER</span> (<span>ORDER</span> <span>BY</span> ID) <span>AS</span><span> RowNum,
           NAME 
    </span><span>INTO</span><span> #T_Table
    </span><span>FROM</span><span> TableName
    
    </span><span>--</span><span>获取记录总数</span>
    <span>DECLARE</span> <span>@RecordCount</span> <span>INT</span> <span>=</span> <span>0</span>    
    <span>SELECT</span> <span>@RecordCount</span> <span>=</span> <span>COUNT</span>(<span>1</span>) <span>FROM</span><span> #T_Table
                
    </span><span>DECLARE</span> <span>@CurrRowNum</span> <span>INT</span> <span>=</span> <span>1</span>        <span>--</span><span>当前行号</span>
    <span>DECLARE</span> <span>@CurrName</span> <span>VARCHAR</span>(<span>50</span>)    <span>--</span><span>当前字段</span>
    
    <span>--</span><span>循环记录            </span>
    <span>WHILE</span> <span>@CurrRowNum</span> <span> <span>@RecordCount</span> 
        <span>BEGIN</span>
            <span>--</span><span>获取当前记录</span>
            <span>SELECT</span> <span>@CurrName</span> <span>=</span> Name <span>FROM</span> #T_Table <span>WHERE</span> RowNum <span>=</span> <span>@CurrRowNum</span>
            
            <span>--</span><span>自定义sql</span>
            
            <span>SET</span> <span>@CurrRowNum</span> <span>=</span> <span>@CurrRowNum</span> <span>+</span> <span>1</span> <span>--</span><span>到下一条记录</span>
        <span>END</span> </span>

P7 字符串转表函数

　　下面的函数的功能是将【a;b;c;】这样的字符串按照【;】进行分割并返回一张表

<span>USE</span> <span>[</span><span>Util</span><span>]</span>
<span>GO</span>
<span>/*</span><span>***** Object:  UserDefinedFunction [dbo].[Func_StringListToTable]    Script Date: 04/08/2014 10:59:53 *****</span><span>*/</span>
<span>SET</span> ANSI_NULLS <span>ON</span>
<span>GO</span>
<span>SET</span> QUOTED_IDENTIFIER <span>ON</span>
<span>GO</span>
<span>--</span><span> =============================================</span><span>
--</span><span> Author:        </span><span>
--</span><span> Create date: </span><span>
--</span><span> Description:    将字符串转换为表 </span><span>
--</span><span>                调用示例如下</span>
                <span>--</span><span>DECLARE  @StringList NVARCHAR(max)</span>
                <span>--</span><span>SET @StringList='a;b;c;'</span>
                <span>--</span><span>DECLARE @Split VARCHAR(10)</span>
                <span>--</span><span>SET @Split=';'</span>
                <span>--</span><span>SELECT * FROM Util.dbo.[Func_StringListToTable](@StringList,@Split)</span><span>
--</span><span> =============================================</span>
<span>CREATE</span> <span>FUNCTION</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>Func_StringListToTable</span><span>]</span><span>
    (
      </span><span>--</span><span> Add the parameters for the function here</span>
      <span>@StringList</span> <span>NVARCHAR</span>(<span>MAX</span><span>) ,
      </span><span>@split</span> <span>VARCHAR</span>(<span>10</span><span>)
    )
</span><span>RETURNS</span> <span>@StringTable</span> <span>TABLE</span><span>
    (
      ID </span><span>INT</span><span> ,
      String </span><span>VARCHAR</span>(<span>MAX</span><span>)
    )
</span><span>AS</span> 
    <span>BEGIN</span>
    <span>--</span><span> Fill the table variable with the rows for your result set</span>
        <span>DECLARE</span> <span>@i</span> <span>INT</span>
        <span>SET</span> <span>@i</span> <span>=</span> <span>1</span>
        <span>WHILE</span> ( <span>CHARINDEX</span>(<span>@split</span>, <span>@StringList</span>) <span></span> <span>0</span><span> ) 
            </span><span>BEGIN</span>   
                <span>INSERT</span>  <span>@StringTable</span><span>
                        ( ID ,
                          String
                        )
                </span><span>VALUES</span>  ( <span>@i</span><span> ,
                          </span><span>SUBSTRING</span>(<span>@StringList</span>, <span>1</span><span>,
                                    </span><span>CHARINDEX</span>(<span>@split</span>, <span>@StringList</span>) <span>-</span> <span>1</span><span>)
                        )   
                </span><span>SET</span> <span>@StringList</span> <span>=</span> <span>STUFF</span>(<span>@StringList</span>, <span>1</span><span>,
                                        </span><span>CHARINDEX</span>(<span>@split</span>, <span>@StringList</span><span>)
                                        </span><span>+</span> <span>LEN</span>(<span>@split</span>) <span>-</span> <span>1</span>, <span>''</span><span>)    
                </span><span>SET</span> <span>@i</span> <span>=</span> <span>@i</span> <span>+</span> <span>1</span>
            <span>END</span>    
        <span>IF</span> <span>@StringList</span> <span></span> <span>''</span> 
            <span>BEGIN</span>
                <span>INSERT</span>  <span>@StringTable</span><span>
                        ( ID, String )
                </span><span>VALUES</span>  ( <span>@i</span>, <span>@StringList</span><span> ) 
            </span><span>END</span> 
        <span>RETURN</span>   
    <span>END</span>

P8 分组数据集并返回每个组的前n条记录

　　Row_NUMBER()函数用于生成行号；利用PARTITION BY可以将结果集按照指定需求进行分组；最终使用一个简单的子查询就能够获取每组的前3条数据

<span>SELECT</span>  <span>*</span>
<span>FROM</span>    ( <span>SELECT</span>    ROW_NUMBER() <span>OVER</span> ( PARTITION <span>BY</span> ProductNO <span>ORDER</span> <span>BY</span> ProductNO ) <span>AS</span><span> RowNum ,
                    </span><span>*</span>
          <span>FROM</span><span>      IM.dbo.ItemInfo
        ) t
</span><span>WHERE</span>   t.RowNum <span>IN</span> ( <span>1</span>, <span>2</span>, <span>3</span> )

P9 【用户自定义表类型】的使用

　　您是否碰到过这样的需求：调用存储过程的时候传一张表进去？？？

在sqlserver数据库中有一种称为【用户自定义表类型】的数据结构，类似表，存储过程的参数可以定义为【用户自定义表类型】，代码调用时可以直接传入一个List，而存储过程调用时可以直接传入一个表变量。

以下代码实现了在IM数据库中新建一个名称为GCRP_PendingGiftCard_TYPE的用户自定义表结构：

<span>USE</span> <span>[</span><span>IM</span><span>]</span>
<span>GO</span>

<span>/*</span><span>***** Object:  UserDefinedTableType [dbo].[GCRP_PendingGiftCard_TYPE]    Script Date: 04/08/2014 14:56:16 *****</span><span>*/</span>
<span>CREATE</span> TYPE <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>GCRP_PendingGiftCard_TYPE</span><span>]</span> <span>AS</span> <span>TABLE</span><span>(
    </span><span>[</span><span>RowNum</span><span>]</span> <span>[</span><span>int</span><span>]</span> <span>NULL</span><span>,
    </span><span>[</span><span>GiftCardNO</span><span>]</span> <span>[</span><span>varchar</span><span>]</span>(<span>500</span>) <span>NULL</span><span>,
    </span><span>[</span><span>UsedDate</span><span>]</span> <span>[</span><span>datetime</span><span>]</span> <span>NULL</span><span>,
    </span><span>[</span><span>CustomerName</span><span>]</span> <span>[</span><span>varchar</span><span>]</span>(<span>50</span>) <span>NULL</span><span>,
    </span><span>[</span><span>ReduceAmount</span><span>]</span> <span>[</span><span>decimal</span><span>]</span>(<span>18</span>, <span>2</span>) <span>NULL</span><span>,
    </span><span>[</span><span>Amount</span><span>]</span> <span>[</span><span>decimal</span><span>]</span>(<span>18</span>, <span>2</span>) <span>NULL</span><span>
)
</span><span>GO</span>

下面的代码演示了【用户自定义表类型】的使用方式和场景（使用起来和临时表、表变量类似）

   <span>--</span><span>1 声明一个自定义表类型@T_PendingGiftCard</span>
   <span>DECLARE</span> <span>@T_PendingGiftCard</span><span> GCRP_PendingGiftCard_TYPE
   </span><span>--</span><span>2 执行一个存储过程,并把返回的结果集插入到上面声明的自定义表类型@T_PendingGiftCard中</span>
   <span>INSERT</span> <span>INTO</span> <span>@T_PendingGiftCard</span> 
            <span>EXEC</span><span> IM.dbo.UP_GCRP_GetEntireGfitCardWithReduceAmount    
   </span><span>--</span><span>3 声明一个自定义表类型@T_PendingSO</span>
   <span>DECLARE</span> <span>@T_PendingSO</span><span> GCRP_PendingSO_TYPE
   </span><span>--</span><span>4 执行一个存储过程,传入表类型@T_PendingGiftCard,并把返回的结果集插入自定义表类型@T_PendingSO中</span>
   <span>INSERT</span> <span>INTO</span> <span>@T_PendingSO</span>
            <span>EXEC</span><span> IM.dbo.UP_GCRP_GetEntireSOWithGiftCardUsed 
                </span><span>@T_PendingGiftCard</span>

下面贴出这两个存储过程的源码，供大家参考

<span>USE</span> <span>[</span><span>IM</span><span>]</span>
<span>GO</span>
<span>/*</span><span>***** Object:  StoredProcedure [dbo].[UP_GCRP_GetEntireGfitCardWithReduceAmount]    Script Date: 04/08/2014 15:01:40 *****</span><span>*/</span>
<span>SET</span> ANSI_NULLS <span>ON</span>
<span>GO</span>
<span>SET</span> QUOTED_IDENTIFIER <span>ON</span>
<span>GO</span>
        
<span>--</span><span> =============================================</span><span>
--</span><span> Author:        DeanZhou    </span><span>
--</span><span> Create date: 2014-04-04</span><span>
--</span><span> Description:    获取礼品卡报表数据-获取当前所有有余额的礼品卡信息</span><span>
--</span><span> =============================================</span>
<span>CREATE</span> <span>PROCEDURE</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>UP_GCRP_GetEntireGfitCardWithReduceAmount</span><span>]</span>
<span>AS</span> 
    <span>BEGIN</span>
        <span>SET</span> NOCOUNT <span>ON</span> ;<span>--</span><span>提高性能的，必须要有</span>

        <span>--</span><span>1.1 从表accounting.dbo.EWalletData中查询出有充值记录的礼品卡</span>
        <span>SELECT</span>  <span>RIGHT</span>(<span>RTRIM</span>(<span>ISNULL</span>(SONO, <span>''</span>)), <span>10</span>) <span>AS</span><span> GiftCardNO ,
                </span><span>ISNULL</span>(Amount, <span>0</span>) <span>AS</span><span> Amount ,
                CreateDate </span><span>AS</span><span> UsedDate ,
                CustomerName
        </span><span>INTO</span><span>    #T_TotalGiftCard
        </span><span>FROM</span><span>    accounting.dbo.EWalletData
        </span><span>WHERE</span>   Source <span>=</span> <span>11</span> <span>AND</span> InOrOut <span>=</span> <span>1</span>
    
        <span>--</span><span>1.2 从表IM.dbo.Temp_UsedGiftCardReportDetail中查询出所有有使用记录的礼品卡(消费金额设置为负数)</span>
        <span>SELECT</span><span>  A.GiftCardNO ,
                </span><span>-</span>A.UsedAmount <span>AS</span><span> Amount ,
                A.GiftCardChargedDate </span><span>AS</span><span> UsedDate ,
                A.CustomerName
        </span><span>INTO</span><span>    #T_UsedGiftCard
        </span><span>FROM</span><span>    IM.dbo.Temp_UsedGiftCardReportDetail A
                </span><span>INNER</span> <span>JOIN</span> #T_TotalGiftCard B <span>ON</span> A.GiftCardNO <span>=</span><span> B.GiftCardNO
        </span><span>DECLARE</span> <span>@E</span> <span>DECIMAL</span>(<span>18</span>,<span>2</span><span>)
        
        </span><span>--</span><span>1.3 合并1.1和1.2的数据,获取所有礼品卡的余额 </span>
        <span>SELECT</span>  ROW_NUMBER() <span>OVER</span> (<span>ORDER</span> <span>BY</span> S.UsedDate <span>ASC</span>) <span>AS</span> RowNum, <span>--</span><span>编号</span>
                S.GiftCardNO ,                                <span>--</span><span>礼品卡编号</span>
                S.UsedDate ,                                <span>--</span><span>礼品卡充值日期</span>
                S.CustomerName ,                            <span>--</span><span>客户名称</span>
                <span>ISNULL</span>(S.ReduceAmount, <span>0</span>) <span>AS</span> ReduceAmount , <span>--</span><span>礼品卡剩余金额</span>
                <span>ISNULL</span>(M.Amount, <span>0</span>) <span>AS</span> Amount                <span>--</span><span>礼品卡面额</span>
        <span>FROM</span>    ( <span>SELECT</span><span>    T.GiftCardNO ,
                            T.UsedDate ,
                            T.CustomerName ,
                            </span><span>SUM</span>(T.Amount) <span>AS</span><span> ReduceAmount
                  </span><span>FROM</span>      ( <span>SELECT</span><span>    GiftCardNO ,
                                        Amount ,
                                        UsedDate ,
                                        CustomerName
                              </span><span>FROM</span><span>      #T_TotalGiftCard
                              </span><span>UNION</span> <span>ALL</span>
                              <span>SELECT</span><span>    GiftCardNO ,
                                        Amount ,
                                        UsedDate ,
                                        CustomerName
                              </span><span>FROM</span><span>      #T_UsedGiftCard
                            ) T
                  </span><span>GROUP</span> <span>BY</span><span>  T.GiftCardNO ,
                            T.UsedDate ,
                            T.CustomerName
                ) S
                </span><span>INNER</span> <span>JOIN</span> #T_TotalGiftCard M <span>ON</span> S.GiftCardNO <span>=</span><span> M.GiftCardNO
        </span><span>WHERE</span>   S.ReduceAmount <span>></span> <span>0</span>
        
    <span>END</span>

View Code

<span>USE</span> <span>[</span><span>IM</span><span>]</span>
<span>GO</span>
<span>/*</span><span>***** Object:  StoredProcedure [dbo].[UP_GCRP_GetEntireSOWithGiftCardUsed]    Script Date: 04/08/2014 15:02:50 *****</span><span>*/</span>
<span>SET</span> ANSI_NULLS <span>ON</span>
<span>GO</span>
<span>SET</span> QUOTED_IDENTIFIER <span>ON</span>
<span>GO</span>
        
<span>--</span><span> =============================================</span><span>
--</span><span> Author:        DeanZhou    </span><span>
--</span><span> Create date: 2014-04-04</span><span>
--</span><span> Description:    获取礼品卡报表数据-获取使用礼品卡的订单信息</span><span>
--</span><span> =============================================</span>
<span>ALTER</span> <span>PROCEDURE</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>UP_GCRP_GetEntireSOWithGiftCardUsed</span><span>]</span><span>
    (
      </span><span>@T_PendingGiftCard</span><span> GCRP_PendingGiftCard_TYPE READONLY
    )
</span><span>AS</span> 
    <span>BEGIN</span>
        <span>SET</span> NOCOUNT <span>ON</span> ;<span>--</span><span>提高性能的，必须要有</span>
        
       <span>--</span><span>订单状态：1 待审核 2 待支付 3 已支付，待确认 4 已支付 5 出库中 6 已发货 7 已收货 8 换货中 9 退货中</span>
       <span>--</span><span>          10 已换货 11 已退货 12 退款成功 13 换货被拒绝 14 退货被拒绝 15 交易成功 16 订单已关闭 17 客户作废</span>
       <span>--</span><span>          18 商城作废 19 系统自动作废 20 退款中 21 等待团购成功 22 组团失败        </span>
           
        <span>SELECT</span>  S.SONO ,                    <span>--</span><span> 订单编号 VARCHAR(30)                        </span>
                S.EwalletDiscountAmount ,    <span>--</span><span> 电子钱包支付金额 DECIMAL(18,2)</span>
                S.RefundAmount ,            <span>--</span><span> 发生退款的总金额 DECIMAL(18,2)</span>
                ( S.EwalletDiscountAmount <span>+</span> S.RefundAmount ) <span>AS</span> RealUsedAmount , <span>--</span><span> 实际使用电子钱包的金额 DECIMAL(18,2)</span>
                <span>0</span> <span>AS</span><span> RelatedRefundID ,
                S.CustomerName ,            </span><span>--</span><span> 客户名称 VARCHAR(50)</span>
                S.SaleOrderStatus ,            <span>--</span><span> 订单状态    INT</span>
                S.CreateDate                <span>--</span><span> 下单日期 DATETIME</span>
        <span>INTO</span><span>    #T_PendingSO
        </span><span>FROM</span>    ( <span>SELECT</span><span>    A.SONO ,
                            A.EwalletDiscountAmount ,
                            ( </span><span>SELECT</span>    <span>-</span><span>ISNULL</span>(<span>SUM</span>(B.Amount), <span>0</span><span>)
                              </span><span>FROM</span><span>      RMA.dbo.RefundRecord B
                              </span><span>WHERE</span>     B.SONO <span>=</span><span> A.SONO
                                        </span><span>AND</span> B.RefundType <span>=</span> <span>3</span>
                                        <span>AND</span> B.Status <span>IN</span> ( <span>2</span>, <span>3</span>, <span>4</span><span> )
                            ) </span><span>AS</span><span> RefundAmount ,
                            </span><span>CONVERT</span>(<span>DECIMAL</span>(<span>18</span>, <span>2</span>), <span>0</span>) <span>AS</span><span> RealUsedAmount ,
                            A.CustomerName ,
                            A.SaleOrderStatus ,
                            A.CreateDate
                  </span><span>FROM</span><span>      SO.dbo.SOMaster A
                  </span><span>WHERE</span>     EwalletDiscountAmount <span>></span> <span>0</span>
                            <span>AND</span> SaleOrderStatus <span>NOT</span> <span>IN</span> ( <span>1</span>, <span>2</span>, <span>17</span>, <span>19</span><span> )
                            </span><span>AND</span> <span>EXISTS</span> ( <span>SELECT</span> <span>1</span>
                                         <span>FROM</span>   <span>@T_PendingGiftCard</span><span> C
                                         </span><span>WHERE</span>  A.CustomerName <span>=</span><span> C.CustomerName
                                                </span><span>AND</span> A.CreateDate <span>></span><span> C.UsedDate )
                            </span><span>AND</span> <span>NOT</span> <span>EXISTS</span> ( <span>SELECT</span> <span>1</span>
                                             <span>FROM</span><span>   IM.dbo.Temp_UsedGiftCardReportDetail B
                                             </span><span>WHERE</span>  A.SONO <span>=</span><span> B.SONO
                                                    </span><span>AND</span> B.UsedAmount <span>></span> <span>0</span><span> )
                ) S

        </span><span>DELETE</span> IM.dbo.Temp_UsedGiftCardReportDetail <span>WHERE</span> UsedAmount <span> <span>0</span> <span>AND</span> <span>EXISTS</span> (<span>SELECT</span> <span>1</span> <span>FROM</span> #T_PendingSO B <span>WHERE</span> Temp_UsedGiftCardReportDetail.SONO <span>=</span><span> B.SONO )
       
        </span><span>SELECT</span>  ROW_NUMBER() <span>OVER</span> ( <span>ORDER</span> <span>BY</span> S.CustomerName, S.SONO, S.CreateDate <span>ASC</span> ) <span>AS</span><span> RowNum ,
                S.</span><span>*</span>
        <span>FROM</span>    ( <span>SELECT</span>    <span>*</span>
                  <span>FROM</span><span>      #T_PendingSO
                  </span><span>UNION</span> <span>ALL</span>
                  <span>SELECT</span><span>    A.SONO ,
                            B.EwalletDiscountAmount ,
                            </span><span>-</span>A.Amount <span>AS</span><span> RefundAmount ,
                            B.RealUsedAmount ,
                            A.RelatedRefundID ,
                            B.CustomerName ,
                            A.Status ,
                            A.CreateDate
                  </span><span>FROM</span><span>      RMA.dbo.RefundRecord A
                            </span><span>INNER</span> <span>JOIN</span> #T_PendingSO B <span>ON</span> A.SONO <span>=</span><span> B.SONO
                  </span><span>WHERE</span>     A.RefundType <span>=</span> <span>3</span>
                            <span>AND</span> A.Status <span>IN</span> ( <span>2</span>, <span>3</span>, <span>4</span><span> )
                  </span><span>UNION</span> <span>ALL</span>
                  <span>SELECT</span><span>    A.SONO ,
                            </span><span>0</span> <span>AS</span><span> EwalletDiscountAmount ,
                            </span><span>-</span>A.Amount <span>AS</span><span> RefundAmount ,
                            B.UsedAmount ,
                            A.RelatedRefundID ,
                            B.CustomerName ,
                            A.Status ,
                            A.CreateDate
                  </span><span>FROM</span><span>      RMA.dbo.RefundRecord A
                            </span><span>INNER</span> <span>JOIN</span> IM.dbo.Temp_UsedGiftCardReportDetail B <span>ON</span> A.SONO <span>=</span> B.SONO <span>AND</span> B.UsedAmount <span>></span> <span>0</span>
                  <span>WHERE</span>     A.RefundType <span>=</span> <span>3</span>
                            <span>AND</span> A.Status <span>IN</span> ( <span>2</span>, <span>3</span>, <span>4</span><span> )
                ) S
          
    </span><span>END</span></span>

View Code