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

 

Cross Apply,Outer Apply 

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

Apply：

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

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

create table customers
(customerid char(5) not null primary key,
 city  varchar(10) not null
)
create table orders
(orderid int 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,c.city,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输出

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

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

    RAW

    AUTO

    EXPLICIT

    PATH

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

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

   以最容易的开始。

SELECT * FROM salesorder

    产生：

ordernumber customername customerstreet
----------- ------------ --------------
1            parker       first av  
2           lesley       sec av

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

SELECT * FROM salesorder FOR XML AUTO

    它产生：

<salesorder ordernumber="1" customername="parker" customerstreet="first av"/>
<salesorder ordernumber="2" customername="lesley" customerstreet="sec av"/>

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

SELECT * FROM salesorder FOR XML AUTO, ELEMENTS

    它产生：

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

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

SELECT * FROM salesorder AS niceorder FOR XML AUTO, ELEMENTS

    它产生：

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

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

SELECT ordernumber AS order_no FROM salesorder WHERE ordernumber = 1 FOR XML AUTO, ELEMENTS

    它产生：

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

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

代码

SELECT  
    ordernumber,
    (SELECT     customername , 
            customerstreet FOR XML PATH('' ), 
            TYPE, ELEMENTS) 
            as  customer
FROM  
    salesorder 
FOR XML AUTO, ELEMENTS

    它产生：

代码

<salesorder>
     <ordernumber>1</ordernumber>
     <customer>
         <customername>parker</customername>
         <customerstreet>first av</customerstreet>
     </customer>
</salesorder>
<salesorder>
     <ordernumber>2</ordernumber>
     <customer>
         <customername>lesley</customername>
         <customerstreet>sec av</customerstreet>
     </customer>
</salesorder>

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

SELECT (
    SELECT  
        customername
    FROM  
        salesorder 
    FOR  XML AUTO, TYPE, ELEMENTS
) AS orderrequest FOR XML PATH(''), TYPE, ELEMENTS

    它产生：

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

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

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

SELECT
    customername ,
    ( SELECT    * FROM item WHERE item.ordernumber =  
       salesorder.ordernumber FOR  XML AUTO, TYPE, ELEMENTS)
FROM
    salesorder
FOR XML AUTO, 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, TYPE, ELEMENTS 语句。如果你想要在'items'外围有一个标签，只需在子查询后添加as，例如：

• 

  代码

  SELECT
      customername ,
      ( SELECT    * FROM item WHERE item.ordernumber =  
        salesorder.ordernumber FOR  XML AUTO, TYPE, ELEMENTS) 
        AS  orderitems
  FROM
      salesorder
  FOR XML AUTO, 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
  INNER JOIN item ON item.ordernumber =  salesorder.ordernumber
  FOR XML AUTO, ELEMENTS

  

      会产生这样的垃圾：

  

  代码

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

  

• 此外，如果使用连接查询，你很难添加一个外围的标签。因此，对于大多数的控制，使用FOR XML 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, 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="', ';'), '"/>', ''), 1, 1, '')  )N
     WHERE (PointCode IN (SELECT PointCode FROM F_GetRelationPointCollection('0', 1, 1, -1 * ISNULL(0, 0), 1)))
   都是解决函数聚合的问题：

数据库中日期的转换

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