讲师中心 微信公众号
首页
文章
后端开发 web前端 数据库 开发工具 php框架 常见问题 人工智能 Java 系统教程 电脑教程 硬件教程 手机教程 软件教程 游戏教程 自媒体 新闻
专题
后端开发 web前端 数据库 开发工具 php框架 人工智能 Java 系统教程 电脑教程 硬件教程 手机教程 软件教程 游戏教程 新闻
AI工具
AI聊天问答 Agent智能体 AI文本写作 AI绘画作图 AI设计工具 AI视频创作 AI音频制作 AI办公学习 AI编程开发 AI提示词
学习
大前端 后端开发 数据库 移动端 运维开发 计算机基础
编程手册
大前端 后端开发 数据库 移动端 运维开发 计算机基础
下载
js特效 网站源码 工具下载 类库下载 网站素材 学习资源 插件扩展 手机游戏
最近更新
当前位置：首页 > 数据库 > mysql教程 >

正文

0

0

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

php中文网

php中文网

发布时间：2016-06-07 15:39:32

|

1320人浏览过

|

来源于php中文网

原创

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

背景

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

P1 sql的执行顺序

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

(<span>8</span>)<span>SELECT</span> (<span>9</span>)<span>DISTINCT</span>  (<span>11</span>)<span><</span><span>Top</span> Num<span>></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><</span>join_type<span>></span> <span>JOIN</span> <span><</span>right_table<span>></span><span>
(</span><span>2</span>)<span>ON</span> <span><</span>join_condition<span>></span><span>
(</span><span>4</span>)<span>WHERE</span> <span><</span>where_condition<span>></span><span>
(</span><span>5</span>)<span>GROUP</span> <span>BY</span> <span><</span>group_by_list<span>></span><span>
(</span><span>6</span>)<span>WITH</span> <span><</span>CUBE <span>|</span> RollUP<span>></span><span>
(</span><span>7</span>)<span>HAVING</span> <span><</span>having_condition<span>></span><span>
(</span><span>10</span>)<span>ORDER</span> <span>BY</span> <span><</span>order_by_list<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 循环模板

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

Decktopus AI
Decktopus AI

AI在线生成高质量演示文稿

下载 

<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> <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>

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> <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>
View Code

 

相关文章

SQLServer如何查询MySQL_SQLServer连接查询MySQL数据教程

WHERE条件中LIKE模糊匹配怎么做？%和_通配符的实战案例解析

MongoDB 与关系数据库：全面比较

如何在 SQL 中使用变量引发错误

使用策略设计模式分步指南在 C# 中实现数据库助手

相关标签:

sqlserver

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

上一篇：关于SqlServer四大排名函数NTILE()函数的问题 下一篇：sqlserver将存储过程返回的数据集存入临时表的问题

热门AI工具

更多
DeepSeek
DeepSeek

幻方量化公司旗下的开源大模型平台

AI编程开发AI聊天问答
豆包大模型
豆包大模型

字节跳动自主研发的一系列大型语言模型

AI编程开发AI大模型
通义千问
通义千问

阿里巴巴推出的全能AI助手

AI编程开发Agent智能体
腾讯元宝
腾讯元宝

腾讯混元平台推出的AI助手

文档处理Excel 表格
文心一言
文心一言

文心一言是百度开发的AI聊天机器人，通过对话可以生成各种形式的内容。

AI编程开发AI文本写作
讯飞写作
讯飞写作

基于讯飞星火大模型的AI写作工具，可以快速生成新闻稿件、品宣文案、工作总结、心得体会等各种文文稿

AI文本写作中文写作
即梦AI
即梦AI

一站式AI创作平台，免费AI图片和视频生成。

图片拼接图画生成
ChatGPT
ChatGPT

最最强大的AI聊天机器人程序，ChatGPT不单是聊天机器人，还能进行撰写邮件、视频脚本、文案、翻译、代码等任务。

AI编程开发AI文本写作
智谱清言 - 免费全能的AI助手
智谱清言 - 免费全能的AI助手

智谱清言 - 免费全能的AI助手

AI编程开发Agent智能体

相关专题

更多
Swift iOS架构设计与MVVM模式实战
Swift iOS架构设计与MVVM模式实战

本专题聚焦 Swift 在 iOS 应用架构设计中的实践，系统讲解 MVVM 模式的核心思想、数据绑定机制、模块拆分策略以及组件化开发方法。内容涵盖网络层封装、状态管理、依赖注入与性能优化技巧。通过完整项目案例，帮助开发者构建结构清晰、可维护性强的 iOS 应用架构体系。

3

2026.03.03

C++高性能网络编程与Reactor模型实践
C++高性能网络编程与Reactor模型实践

本专题围绕 C++ 在高性能网络服务开发中的应用展开，深入讲解 Socket 编程、多路复用机制、Reactor 模型设计原理以及线程池协作策略。内容涵盖 epoll 实现机制、内存管理优化、连接管理策略与高并发场景下的性能调优方法。通过构建高并发网络服务器实战案例，帮助开发者掌握 C++ 在底层系统与网络通信领域的核心技术。

12

2026.03.03

Golang 测试体系与代码质量保障：工程级可靠性建设
Golang 测试体系与代码质量保障：工程级可靠性建设

Go语言测试体系与代码质量保障聚焦于构建工程级可靠性系统。本专题深入解析Go的测试工具链（如go test）、单元测试、集成测试及端到端测试实践，结合代码覆盖率分析、静态代码扫描（如go vet）和动态分析工具，建立全链路质量监控机制。通过自动化测试框架、持续集成（CI）流水线配置及代码审查规范，实现测试用例管理、缺陷追踪与质量门禁控制，确保代码健壮性与可维护性，为高可靠性工程系统提供质量保障。

69

2026.02.28

Golang 工程化架构设计：可维护与可演进系统构建
Golang 工程化架构设计：可维护与可演进系统构建

Go语言工程化架构设计专注于构建高可维护性、可演进的企业级系统。本专题深入探讨Go项目的目录结构设计、模块划分、依赖管理等核心架构原则，涵盖微服务架构、领域驱动设计(DDD)在Go中的实践应用。通过实战案例解析接口抽象、错误处理、配置管理、日志监控等关键工程化技术，帮助开发者掌握构建稳定、可扩展Go应用的最佳实践方法。

59

2026.02.28

Golang 性能分析与运行时机制：构建高性能程序
Golang 性能分析与运行时机制：构建高性能程序

Go语言以其高效的并发模型和优异的性能表现广泛应用于高并发、高性能场景。其运行时机制包括 Goroutine 调度、内存管理、垃圾回收等方面，深入理解这些机制有助于编写更高效稳定的程序。本专题将系统讲解 Golang 的性能分析工具使用、常见性能瓶颈定位及优化策略，并结合实际案例剖析 Go 程序的运行时行为，帮助开发者掌握构建高性能应用的关键技能。

46

2026.02.28

Golang 并发编程模型与工程实践：从语言特性到系统性能
Golang 并发编程模型与工程实践：从语言特性到系统性能

本专题系统讲解 Golang 并发编程模型，从语言级特性出发，深入理解 goroutine、channel 与调度机制。结合工程实践，分析并发设计模式、性能瓶颈与资源控制策略，帮助将并发能力有效转化为稳定、可扩展的系统性能优势。

24

2026.02.27

Golang 高级特性与最佳实践：提升代码艺术
Golang 高级特性与最佳实践：提升代码艺术

本专题深入剖析 Golang 的高级特性与工程级最佳实践，涵盖并发模型、内存管理、接口设计与错误处理策略。通过真实场景与代码对比，引导从“可运行”走向“高质量”，帮助构建高性能、可扩展、易维护的优雅 Go 代码体系。

20

2026.02.27

Golang 测试与调试专题：确保代码可靠性
Golang 测试与调试专题：确保代码可靠性

本专题聚焦 Golang 的测试与调试体系，系统讲解单元测试、表驱动测试、基准测试与覆盖率分析方法，并深入剖析调试工具与常见问题定位思路。通过实践示例，引导建立可验证、可回归的工程习惯，从而持续提升代码可靠性与可维护性。

4

2026.02.27

漫蛙app官网链接入口
漫蛙app官网链接入口

漫蛙App官网提供多条稳定入口，包括 https://manwa.me、https

348

2026.02.27

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

相关下载

更多
php商城系统
淘源码商城PHP淘宝查信誉
PHP房产程序[BBWPS]
PHP简约自动发卡平台个人版
ERMEB域名PHP离线网络授权系统
Difeye-敏捷的轻量级PHP框架
大泉州汽车网PHP整站程序

精品课程

更多
相关推荐
/
热门推荐
/
最新课程
Node.js 教程
Node.js 教程

共57课时 | 12.6万人学习

CSS3 教程
CSS3 教程

共18课时 | 6.5万人学习

Git 教程
Git 教程

共21课时 | 4万人学习

最新文章

更多
mysql默认事务隔离级别是什么_mysql配置与行为解析
如何查看mysql安装路径_mysql目录结构说明
mysql如何处理旧版本身份认证兼容性_mysql_native_password转换
mysql如何处理升级后关键词冲突_mysql 8.0保留字处理
mysql如何修复错误的权限配置_mysql flush privileges使用时机
mysql字符串字段如何建立索引_mysql字符类型优化
mysql自增主键对索引性能有影响吗_mysql设计分析
mysql如何进行子查询_mysql嵌套查询实现方法
mysql升级过程中出现错误怎么办_mysql异常排查方法
mysql如何启用透明数据加密_mysql TDE静态数据加密
关于我们 免责申明 举报中心 意见反馈 讲师合作 广告合作 最新更新
php中文网：公益在线php培训，帮助PHP学习者快速成长！
关注服务号 技术交流群
PHP中文网订阅号
每天精选资源文章推送

Copyright 2014-2026 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号

  • PHP学习

  • 技术支持

  • 返回顶部