菜鸟笔记5月4日（一些函数如：1,OUTER APPLY 用法 2，FOR XML 用

发布时间：2020-12-15 23:48:57 所属栏目：百科来源：网络整理

导读：Cross Apply,Outer Apply Apply运算符是SQL Server 2005及之后版本新加的功能，是T-SQL特有的，不是ANSI标准运算符。 Apply：把右表表达式应用于左输入中的每一行。右表达式可以引用左输入中的列，对于左表中的每一行都要计算一次右边输入的表达式。 creat

Cross Apply,Outer Apply

Apply运算符是SQL Server 2005及之后版本新加的功能，是T-SQL特有的，不是ANSI标准运算符。

Apply：

把右表表达式应用于左输入中的每一行。

右表达式可以引用左输入中的列，对于左表中的每一行都要计算一次右边输入的表达式。

create table customers
(customerid char(5) not null primary key,
cityvarchar(10) not null
)
create table orders
(orderidint not null primary key,
customerid char(5) null
)
go
insert into customers
select 'FISSA','Madrid'
union
select 'FENDO','Madrid'
union
select 'KRLOS','Madrid'
union
select 'MRPHS','Zion'

insert into orders
select 1,'FRNDO'
union
select 2,'FRNDO'
union
select 3,'KRLOS'
union
select 4,'KRLOS'
union
select 5,'KRLOS'
union
select 6,'MRPHS'
union
select 7,null
go

Example:

为每个客户返回具有最大订单的两个订单

select c.customerid,c.city,A.Orderid
from customers as c
cross apply (select top (2) o.orderid,o.customerid from orders as o where o.customerid=c.customerid order by orderid desc) as A

为每个客户返回具有最大订单的两个订单，没有下过订单的也显示

select c.customerid,A.Orderid
from customers as c
Outer apply (select top (2) o.orderid,o.customerid from orders as o where o.customerid=c.customerid order by orderid desc) as A

使用FOR XML AUTO控制XML输出

这篇文章描述如何通过使用FORXML AUTO更好的控制XML输出格式。例如添加XML标记。用这个来替代难于理解的FORXML EXPLICIT语句。如果你在应用程序中即将反序列化输出的XML，你就会觉得这个信息对你有用。

在For XML从句中，您通常使用下列方式之一：

RAW

AUTO

EXPLICIT

PATH

如果你想完全掌控产生的XML，可以使用FORXML EXPLICIT。但是它理解起来相当的困难，后面还要维护复杂的select语句。FORXML AUTO能产生最可读的SELECT语句，但是它也有缺点，不容易控制生成的XML。但使用一些技巧，例如通过使用额外的PATH选项，你可以做一些超过你预期的事情。RAW选项是很少使用，因此不讨论。PATH选项允许您很容易地混合属性和元素。现在，让我们来使用FORXML AUTO。

在这个例子中，我们使用的是1：N关系的两个简单的数据表。一个表（SalesOrder）包含客户信息的订单，例外一张表（Items）中包含的具体的项。一个订单可以有多个项，一个项往往只属于一个订单。

以最容易的开始。

 
     SELECT*FROMsalesorder 
    

产生：

 
     ordernumbercustomernamecustomerstreet
 ------------------------------------- 
     
     1 
     parkerfirstav
 2lesleysecav

如果你想要使结果集是XML，我们添加FORXML AUTO语句：

 
     SELECT*FROMsalesorderFORXMLAUTO 
    

它产生：

 
     <salesorderordernumber="1"customername="parker"customerstreet="firstav"/> 
     
     <salesorderordernumber="2"customername="lesley"customerstreet="secav"/>

现在，字段是作属性的，大多数情况下希望他们是元素。为了做到这点，添加ELEMENTS参数

 
     SELECT*FROMsalesorderFORXMLAUTO,ELEMENTS 
    

它产生：

 
     <salesorder> 
     
     <ordernumber>1</ordernumber> 
     
     <customername>parker</customername> 
     
     </salesorder>

如果你想要更改'salesorder' 标签，使用：

 
     SELECT*FROMsalesorderASniceorderFORXMLAUTO,ELEMENTS 
    

它产生：

 
     <niceorder> 
     
     <ordernumber>1</ordernumber> 
     
     <customername>parker</customername> 
     
     </niceorder>

当然，这一招也适用的列名：

 
     SELECTordernumberASorder_noFROMsalesorderWHEREordernumber=1FORXMLAUTO,ELEMENTS 
    

它产生：

 
     <salesorder> 
     
     <order_no>1</order_no> 
     
     </salesorder>

如果你想添加其他标签或节点？例如，对有关客户信息添加'customer''标记？但对FOR XML AUTO来说，被证明是很困难的事件。一个可能的解决方案是使用SELF JOIN（join相同的表），但我找到一个更容易办法。经过一番摆弄和修订，我们使用子查询和有点滥用FOR XML PATH命令。

 
      
    
代码 
      SELECT 
      
 ordernumber,
 (SELECT 
      customername,
 customerstreetFORXMLPATH('' 
      ),
 TYPE,ELEMENTS)
 as 
      customer
 FROM 
      
 salesorder
 FORXMLAUTO,ELEMENTS 
     
 
      
    

它产生：

 
代码 
      <salesorder> 
      
      <ordernumber>1</ordernumber> 
      
      <customer> 
      
      <customername>parker</customername> 
      
      <customerstreet>firstav</customerstreet> 
      
      </customer> 
      
      </salesorder> 
      
      <salesorder> 
      
      <ordernumber>2</ordernumber> 
      
      <customer> 
      
      <customername>lesley</customername> 
      
      <customerstreet>secav</customerstreet> 
      
      </customer> 
      
      </salesorder>

注意使用附加的'TYPE’参数。这将确保子查询的结果将返回的是一个XML类型（作为整个XML类型的结果的一部分），而不是NVARCHAR(MAX)类型。如果您要对整个结果添加外围标签，也是简单的小把戏：

 
     SELECT 
     (
 SELECT 
     
 customername
 FROM 
     
 salesorder
 FOR 
     XMLAUTO,TYPE,ELEMENTS
 )ASorderrequestFORXMLPATH(''),ELEMENTS

它产生：

 
     <orderrequest> 
     
     <salesorder> 
     
     <customername>parker</customername> 
     
     </salesorder> 
     
     <salesorder> 
     
     <customername>lesley</customername> 
     
     </salesorder> 
     
     </orderrequest>

为什么我们在子查询中不使用FOR XML AUTO？试试，它会产生一个错误。当子查询是查询一个实际的表时，您才能使用FOR XML AUTO（上述显然不是）。

如果您想对所生产的XML完全控制，子查询是条出路。比方说，我们希望，每个订单，客户的名字和所有的项都属于订单。为此，您使用这样的相关子查询：

 
     SELECT 
     
 customername,
 ( 
     SELECT*FROMitemWHEREitem.ordernumber= 
     
 salesorder.ordernumberFOR 
     XMLAUTO,ELEMENTS)
 FROM 
     
 salesorder
  
     FORXMLAUTO,ELEMENTS

它产生：

 
代码 
      <salesorder> 
      
      <customername>parker</customername> 
      
      <item> 
      
      <itemnumber>10</itemnumber> 
      
      <description>pen</description> 
      
      <ordernumber>1</ordernumber> 
      
      </item> 
      
      <item> 
      
      <itemnumber>11</itemnumber> 
      
      <description>paper</description> 
      
      <ordernumber>1</ordernumber> 
      
      </item> 
      
      </salesorder>

当使用关联子查询，你可以使用规则的FOR XML AUTO,ELEMENTS语句。如果你想要在'items'外围有一个标签，只需在子查询后添加as，例如：

 
代码 
        SELECT 
        
 customername,
 ( 
        SELECT*FROMitemWHEREitem.ordernumber= 
        
 salesorder.ordernumberFOR 
        XMLAUTO,ELEMENTS)
 AS 
        orderitems
 FROM 
        
 salesorder
  
        FORXMLAUTO,ELEMENTS

它产生：

 
代码 
        <salesorder> 
        
        <customername>parker</customername> 
        
        <orderitems> 
        
        <item> 
        
        <itemnumber>10</itemnumber> 
        
        <description>pen</description> 
        
        <ordernumber>1</ordernumber> 
        
        </item> 
        
        <item> 
        
        <itemnumber>11</itemnumber> 
        
        <description>paper</description> 
        
        <ordernumber>1</ordernumber> 
        
        </item> 
        
        </orderitems> 
        
        </salesorder>

为什么我们不只是简单的连接item表和order表。这有时会导致不必要的和不可预测的情况，涉及到产生XML布局：

例如：

 
       SELECT 
       
 item.description,
 salesorder.customername
  
       FROM 
       
 salesorder
  
       INNERJOINitemONitem.ordernumber= 
       salesorder.ordernumber
 FORXMLAUTO,ELEMENTS

会产生这样的垃圾：

 
代码 
        <item> 
        
        <description>pen</description> 
        
        <salesorder> 
        
        <customername>parker</customername> 
        
        </salesorder> 
        
        </item> 
        
        <item> 
        
        <description>paper</description> 
        
        <salesorder> 
        
        <customername>parker</customername> 
        
        </salesorder> 
        
        </item>

此外，如果使用连接查询，你很难添加一个外围的标签。因此，对于大多数的控制，使用FORXML AUTO和关联子查询。

根据以上解决一些问题会变得很容易：

如下：

1，

SELECT * FROM (SELECT DISTINCT id,address,phone FROM tbinfo) A OUTER APPLY(
SELECT [values]= STUFF(REPLACE(REPLACE(
(
SELECT value FROM tbinfo N
WHERE id = A.id
FOR XML AUTO
),'<N value="',','),'"/>',''),1,'')
)N

2，

SELECT * FROM
(SELECT DISTINCT PointCode,
BillCode
FROM T_TY_PayMentDetail WITH(NOLOCK)) A
OUTER APPLY
(SELECT [PayWay]=
STUFF(REPLACE(REPLACE((SELECT Payway as PayWay
FROM T_TY_PayMentDetail N WITH(NOLOCK)
WHERE PointCode = A.PointCode
AND BillCode = a.BillCode
GROUP BY PayWay FOR XML AUTO),'<N PayWay="',';'),'') )N
WHERE (PointCode IN (SELECT PointCode FROM F_GetRelationPointCollection('0',-1 * ISNULL(0,0),1)))
都是解决函数聚合的问题：

数据库中日期的转换

YYYY/MM/DD
Select Convert(varchar(10),Getdate(),111)
--YYYYMMDD
Select Convert(varchar(10),112)
--HH:MM:SS
Select Convert(varchar(8),108)
--HH:MM:SS:mmm
Select Convert(varchar(12),114)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!