社区
学习
工具库
AI工具
休闲
搜索
简体中文
首页 数据库 mysql教程 sqlserver常用知识点备忘录（持续更新）

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

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB
Jun 07, 2016 pm 03:39 PM
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数据库中有一种称为【用户自定义表类型】的数据结构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>
登录后复制

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

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

<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

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

<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

 

本站声明
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn

热AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智能驱动的应用程序，用于创建逼真的裸体照片

AI Clothes Remover

AI Clothes Remover

用于从照片中去除衣服的在线人工智能工具。

Undress AI Tool

Undress AI Tool

免费脱衣服图片

Clothoff.io

Clothoff.io

AI脱衣机

AI Hentai Generator

AI Hentai Generator

免费生成ai无尽的。

显示更多

热门文章

R.E.P.O.能量晶体解释及其做什么（黄色晶体）
2 周前 By 尊渡假赌尊渡假赌尊渡假赌
仓库：如何复兴队友
4 周前 By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island冒险：如何获得巨型种子
3 周前 By 尊渡假赌尊渡假赌尊渡假赌
击败分裂小说需要多长时间？
3 周前 By DDD
R.E.P.O.保存文件位置：在哪里以及如何保护它？
3 周前 By DDD
显示更多

热工具

记事本++7.3.1

记事本++7.3.1

好用且免费的代码编辑器

SublimeText3汉化版

SublimeText3汉化版

中文版，非常好用

禅工作室 13.0.1

禅工作室 13.0.1

功能强大的PHP集成开发环境

Dreamweaver CS6

Dreamweaver CS6

视觉化网页开发工具

SublimeText3 Mac版

SublimeText3 Mac版

神级代码编辑软件(SublimeText3)

显示更多

热门话题

gmail邮箱登陆入口在哪里
7319
9
Java教程
1625
14
CakePHP 教程
1349
46
Laravel 教程
1261
25
PHP教程
1209
29
显示更多
Related knowledge
sqlserver数据库中已存在名为的对象怎么解决 sqlserver数据库中已存在名为的对象怎么解决 Apr 05, 2024 pm 09:42 PM

对于 SQL Server 数据库中已存在同名对象，需要采取以下步骤：确认对象类型（表、视图、存储过程）。如果对象为空，可使用 IF NOT EXISTS 跳过创建。如果对象有数据，使用不同名称或修改结构。使用 DROP 删除现有对象（谨慎操作，建议备份）。检查架构更改，确保没有引用删除或重命名的对象。

​sqlserver怎么导入mdf文件 ​sqlserver怎么导入mdf文件 Apr 08, 2024 am 11:41 AM

导入步骤如下：将 MDF 文件复制到 SQL Server 的数据目录（通常为 C:\Program Files\Microsoft SQL Server\MSSQL\DATA）。在 SQL Server Management Studio（SSMS）中，打开数据库并选择“附加”。单击“添加”按钮，选择 MDF 文件。确认数据库名称，点击确定按钮即可。

sqlserver服务无法启动怎么办 sqlserver服务无法启动怎么办 Apr 05, 2024 pm 10:00 PM

当 SQL Server 服务无法启动时，可采取以下步骤解决：检查错误日志以确定根本原因。确保服务帐户具有启动服务的权限。检查依赖项服务是否正在运行。禁用防病毒软件。修复 SQL Server 安装。如果修复不起作用，重新安装 SQL Server。

怎么查看sqlserver端口号 怎么查看sqlserver端口号 Apr 05, 2024 pm 09:57 PM

要查看 SQL Server 端口号：打开 SSMS，连接到服务器。在对象资源管理器中找到服务器名称，右键单击它，然后选择“属性”。在“连接”选项卡中，查看“TCP 端口”字段。

sqlserver误删数据库怎么恢复 sqlserver误删数据库怎么恢复 Apr 05, 2024 pm 10:39 PM

若误删 SQL Server 数据库，可采取以下步骤恢复：停止数据库活动；备份日志文件；检查数据库日志；恢复选项：从备份恢复；从事务日志恢复；使用 DBCC CHECKDB；使用第三方工具。请定期备份数据库并启用事务日志以防止数据丢失。

sqlserver数据库在哪里 sqlserver数据库在哪里 Apr 05, 2024 pm 08:21 PM

SQL Server 数据库文件通常存储在以下默认位置：Windows: C:\Program Files\Microsoft SQL Server\MSSQL\DATALinux: /var/opt/mssql/data可通过修改数据库文件路径设置来自定义数据库文件位置。

sqlserver安装失败怎么样删除干净 sqlserver安装失败怎么样删除干净 Apr 05, 2024 pm 11:27 PM

如果 SQL Server 安装失败，可通过以下步骤清理：卸载 SQL Server删除注册表项删除文件和文件夹重启计算机

sqlserver英文安装怎么更改中文 sqlserver英文安装怎么更改中文 Apr 05, 2024 pm 10:21 PM

SQL Server 英文安装可通过以下步骤更改为中文：下载相应语言包；停止 SQL Server 服务；安装语言包；更改实例语言；更改用户界面语言；重启应用程序。

See all articles