STUFF函数的用法

2019-10-22 06:57栏目:计算机论坛
TAG:

   FOR XML PATH ,其实它便是将查询结果集以XML情势表现,将多行的结果,映今后同风流浪漫行。

    上面大家来写多个事例:

        假使大家有个职业流程表:

    图片 1图片 2

CREATE TABLE [dbo].[Workflow_Action](
    [WorkflowSchema] [nvarchar](128) NULL,
    [ActionSchema] [nvarchar](128) NULL,
    [ActionName] [nvarchar](64) NULL
)

INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('material-price','confirm','审核通过')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('material-price','reject','审核驳回')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('material-price','executing','执行价格')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('material-price','non-executing','不执行价格')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('oa-meeting-apply','confirm','审核通过')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('oa-meeting-apply','reject','审核驳回')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('oa-officialSeal-apply','confirm','审核通过')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('oa-officialSeal-apply','reject','审核驳回')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('oa-officialSeal-apply','returned','归还公章')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('purchase','commit','提交审核')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('purchase','reject','采购驳回')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('purchase','confirm','审核通过')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('purchase','order','采购下单')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('purchase','recommit','重新提交审核')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('purchase','part-consignment','部分收货')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('purchase','consignment','完成收货')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('purchase-request','commit','提交审核')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('purchase-request','confirm','审核通过')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('purchase-request','reject','申请驳回')
INSERT INTO [dbo].[Workflow_Action] ([WorkflowSchema],[ActionSchema],[ActionName]) VALUES ('purchase-request','recommit','重新提交审核')

数据表和数目

    图片 3

 

    大器晚成、简要介绍

      接下去,大家用这么些艺术查询这一个表的数据。

    select * from [dbo].[Workflow_Action] for xml path
      它可以将查询结果转换为一段XML格式的代码

    图片 4

   --for xml path 后面可以写东西,for xml path(Schema),这样写的话,可以将节点<row>变成<Schema>
    select WorkflowSchema as WS,ActionSchema as SC,ActionName as AN from [dbo].[Workflow_Action] for xml path('Schema')

       图片 5

    --我们还可以单独输出某一个字段的值
    SELECT '[ '+ActionName+' ]' FROM [dbo].[Workflow_Action] FOR XML PATH('')

    图片 6

 

    二、实际应用  

    --我们看看一个操作对应的而多格流程
    --一共是两层,里面一层查出单独的ActionName,拼成一行,然后使用where条件连接外层
    SELECT WorkflowSchema,
      (SELECT ActionName+',' FROM [dbo].[Workflow_Action] 
      WHERE WorkflowSchema=A.WorkflowSchema    --必须加的条件
      FOR XML PATH('')) AS ActionList
    FROM [dbo].[Workflow_Action] A 
    GROUP BY WorkflowSchema
    

    --where 连接条件必须要,如果去掉,就会查出所有的ActionName,如同上面示例一样
    

    --现在我们优化一下格式,会发现最后多了一个‘,’符号,用LEFT函数去掉他,继续在外面接一层查询
    select B.WorkflowSchema,
        LEFT(B.ActionList,LEN(B.ActionList)-1) as ActionList 
           from (
                 SELECT WorkflowSchema,
                   (SELECT ActionName+',' FROM [dbo].[Workflow_Action] 
                      WHERE WorkflowSchema=A.WorkflowSchema 
                     FOR XML PATH('')) AS ActionList
                FROM [dbo].[Workflow_Action] A 
    GROUP BY WorkflowSchema) as B

     图片 7

 

   接下来,大家再讲三个别的的函数,实现均等的功效,STUFF函数。

    sql stuff函数用于删除钦赐长度的字符,并得以在制定的源点处插入另风姿洒脱组字符。sql stuff函数中假设早先地点或长度值是负数,恐怕只要最早地方大于第贰个字符串的长短,将赶回空字符串。假若要去除的长度超越第二个字符串的尺寸,将去除到第三个字符串中的第2个字符。

    一、作用

      删除内定长度的字符,并在内定的起源处插入另后生可畏组字符。

    二、语法

      STUFF ( character_expression , start , length ,character_expression )

        参数

        character_expression  一个字符数据表明式。character_expression 能够是常量、变量,也得以是字符列或二进制数据列。

        start   四个整数值,钦命删除和插入的开端地方。假使start 或 length 为负,则赶回空字符串。假诺 start 比第贰个character_expression 长,则赶回空字符串。start 能够是 bigint 类型。

        length  三个板寸,钦定要删减的字符数。假使 length 比第多少个 character_expression 长,则最多删除到最终一个character_expression 中的最后三个字符。length 能够是 bigint 类型。

        再次来到类型  

        如果 character_expression 是受扶助的字符数据类型,则赶回字符数据。假设 character_expression 是二个受扶助的 binary 数据类型,则赶回二进制数据。

    三、备注

      1、假诺开第三地点或长度值是负数,或许郁郁苍苍旦起头地点大于第三个字符串的长度,将回到空字符串。如若要去除的尺寸抢先第叁个字符串的尺寸,将去除到第4个字符串中的第二个字符。

      2、假使结果值超越重临类型援救的最大值,则发出错误。

    四、sql stuff函数

    --实例一
     select STUFF('abcdefg',1,0,'1234')       --结果为'1234abcdefg'
     select STUFF('abcdefg',1,1,'1234')       --结果为'1234bcdefg'
     select STUFF('abcdefg',2,1,'1234')       --结果为'a1234cdefg'
     select STUFF('abcdefg',2,2,'1234')       --结果为'a1234defg'

    --实例二、SQL 将列转成字符串并用逗号分隔
    --同样的,我们也用到了for xml path这个方法
    SELECT STUFF((SELECT ',' + ActionName FROM [dbo].[Workflow_Action] FOR XML PATH('')),1,1,'') AS WA

     图片 8

    --实例三、最后我们实现,上面for xml path的功能
    --先查出两个字段,然后对ActionName这个字段进行转化,where条件记得加上,不加就会显示出所有的ActionName
    select WorkflowSchema,
           ActionName=(STUFF((select ',' + ActionName 
                              from [dbo].[Workflow_Action] a 
                              where a.WorkflowSchema=b.WorkflowSchema for xml path('')),1,1,''))    --where条件必须加上 
    from [dbo].[Workflow_Action] b group by WorkflowSchema

     图片 9

 

    相比以上三种做法,能够自行相比哪一种艺术进一步便民。

版权声明:本文由493333王中王开奖结果发布于计算机论坛,转载请注明出处:STUFF函数的用法