将 sql case 语句嵌入到使用 FOR XML 的 sql 查询中 [英] Embedding a sql case statement into a sql query that is using FOR XML

问题描述

昨天我从一位 SO 用户那里得到了一些很棒的帮助(参见 此处)我朝着我的目标取得了很大的进步.现在我正在尝试确定建议的神奇添加是否可以嵌入到生成 xml 输出的现有查询中.

I received some wonderful assistance from a fellow SO user yesterday (see here ) which allowed me to make a great deal of progress towards my goal. Now I'm attempting to establish whether the magical addition that was suggested can be embedded into an existing query that is producing xml output.

现有查询如下:

PROCEDURE [dbo].[CreateLandingPurchaseOrderDetails]

-- Add the parameters for the stored procedure here


@startDate DATE,
@endDate DATE


AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT (SELECT
            Contacts.ContactId AS '@ContactId',
            VesselOwner AS '@Owner',
            FORMAT(SUM(LandingDetails.Quantity * LandingDetails.UnitPrice), 'N2') AS '@Owed',
            SocietyMemberships.WeeklyDeductionRate AS '@WeeklyDeductionRate',
            SocietyMemberships.FromMinimumReturn AS '@FromMinimumReturn',
            Deductions.DeductionRate AS '@DeductionRate',


            (SELECT DISTINCT
                ld1.ProductId AS '@ProductId',
                FORMAT(AVG(ld1.UnitPrice), 'N2') AS '@Cost',
                FORMAT(SUM(ld1.Quantity), 'N2') AS '@Quantity'


            FROM LandingDetails ld1
            INNER JOIN dbo.LandingHeaders lh1
                ON ld1.LandingId = lh1.LandingId
            WHERE Posted = 0
            AND lh1.VesselOwner = LandingHeaders.VesselOwner
            GROUP BY ld1.ProductId
            FOR XML PATH ('Products'), TYPE)

        FROM dbo.LandingDetails
        INNER JOIN dbo.LandingHeaders
            ON LandingDetails.LandingId = LandingHeaders.LandingId
        INNER JOIN dbo.Vessels
            ON LandingHeaders.VesselId = Vessels.VesselId
        INNER JOIN dbo.Contacts
            ON Vessels.OwnerId = Contacts.ContactId
        INNER JOIN dbo.SocietyMemberships
            ON Contacts.SocietyId = SocietyMemberships.SocietyId
        INNER JOIN dbo.Deductions
            ON Vessels.DeductionId = Deductions.DeductionId
        WHERE LandingHeaders.Posted = 0
        AND LandingDate1 BETWEEN @startDate AND @endDate
        GROUP BY    ContactId,
                    VesselOwner,
                    SocietyMemberships.WeeklyDeductionRate,
                    SocietyMemberships.FromMinimumReturn,
                    Deductions.DeductionRate
        ORDER BY ContactId

        FOR XML PATH ('Owner'), TYPE)

    FOR XML PATH ('PurchaseOrders'), TYPE

END

这会沿着这些行产生 xml 输出；

This Produces xml output along these lines;

<PurchaseOrders>
  <Owner ContactId="39" Owner="Paul Joy" Owed="1,609.39" WeeklyDeductionRate="10.00" FromMinimumReturn="110.00" DeductionRate="0.0150">
    <Products ProductId="33" Cost="5.00" Quantity="0.40" />
    <Products ProductId="34" Cost="1.80" Quantity="0.90" />
    <Products ProductId="41" Cost="2.30" Quantity="1.30" />

我想再向 Owner 元素添加一个属性 ( TotalDeductions ).从我问的上一个问题中，我可以看到 sql 如何计算我所追求的信息以生成 Total deductions 字段.然而，事实证明将这种逻辑添加到 FOR XML 查询中是难以捉摸的.我不愿意说它不能完成，因为在 SQL 中如果有一点横向思考，似乎几乎没有什么是无法完成的.

I would like to add one more attribute to the Owner element ( TotalDeductions ). From the previous question that I asked I can see how it's possible for the sql to calculate the information that I was after in order to produce the Total deductions field. However adding that logic to the FOR XML query has proved elusive. I'm loathe to say it can't be done on the grounds that it seems there is little if anything that can't be done in SQL with a little lateral thought.

如果我只是将查询的大小写部分剪切并粘贴到我的 FOR XML 查询中，编译器会指出 Owed 不是一个有效名称.我明白.但是，如果我以以下方式简单地嵌入 for xml 的所有者部分；

If I simply cut and paste the case part of the query to my FOR XML query the compiler points out that Owed isn't a valid name. That I understand. However if I simply embed the owner part of the for xml in the following fashion;

WITH cte AS (
 'embed the part of the FOR XML producing the owner element here
)

SELECT 
    ContactId,
    Owed,
    WeeklyDeductionRate,
    FromMinimumReturn,
    DeductionRate,
    CASE 
       WHEN Owed - (Owed * DeductionRate + WeeklyDeductionRate) > FromMinimumReturn 
       THEN Owed * DeductionRate + WeeklyDeductionRate
       ELSE Owed * DeductionRate END
    AS TotalDeductions
FROM cte

然后暂时搁置编译错误，我不会生成我想要的 xml.

Then leaving aside the compile errors for the moment I'd not be producing the xml that I'm after.

我是否终于找到了一些实际上无法在 SQL 中完成的事情，或者我是否只是错过了我应该拥有的明显的横向思考"?

Have I finally found something that actually can't be done in SQL, or have I simply missed the obvious 'lateral thought' that I should have had?

谢谢

推荐答案

我认为在正确的位置插入原始 case 表达式应该可行.试试这个:

I think inserting the original case expression at the correct place should work. Try this:

PROCEDURE [dbo].[CreateLandingPurchaseOrderDetails]

-- Add the parameters for the stored procedure here
@startDate DATE, @endDate DATE

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT (
       SELECT
            Contacts.ContactId AS '@ContactId',
            LandingHeaders.VesselOwner AS '@Owner',
            FORMAT(SUM(LandingDetails.Quantity * LandingDetails.UnitPrice), 'N2') AS '@Owed',
            SocietyMemberships.WeeklyDeductionRate AS '@WeeklyDeductionRate',
            SocietyMemberships.FromMinimumReturn AS '@FromMinimumReturn',
            Deductions.DeductionRate AS '@DeductionRate',

          CASE 
             WHEN SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) - (SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) * DeductionRate + WeeklyDeductionRate) > FromMinimumReturn 
             THEN SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) * DeductionRate + WeeklyDeductionRate
             ELSE SUM(LandingDetails.Quantity * LandingDetails.UnitPrice) * DeductionRate 
          END AS '@TotalDeductions',

            (SELECT DISTINCT
                ld1.ProductId AS '@ProductId',
                FORMAT(AVG(ld1.UnitPrice), 'N2') AS '@Cost',
                FORMAT(SUM(ld1.Quantity), 'N2') AS '@Quantity'

            FROM LandingDetails ld1
            INNER JOIN dbo.LandingHeaders lh1
                ON ld1.LandingId = lh1.LandingId
            WHERE Posted = 0
            AND lh1.VesselOwner = LandingHeaders.VesselOwner
            GROUP BY ld1.ProductId
            FOR XML PATH ('Products'), TYPE)

        FROM dbo.LandingDetails
        INNER JOIN dbo.LandingHeaders
            ON LandingDetails.LandingId = LandingHeaders.LandingId
        INNER JOIN dbo.Vessels
            ON LandingHeaders.VesselId = Vessels.VesselId
        INNER JOIN dbo.Contacts
            ON Vessels.OwnerId = Contacts.ContactId
        INNER JOIN dbo.SocietyMemberships
            ON Contacts.SocietyId = SocietyMemberships.SocietyId
        INNER JOIN dbo.Deductions
            ON Vessels.DeductionId = Deductions.DeductionId
        WHERE LandingHeaders.Posted = 0
        AND LandingDate1 BETWEEN @startDate AND @endDate
        GROUP BY    ContactId,
                    LandingHeaders.VesselOwner,
                    SocietyMemberships.WeeklyDeductionRate,
                    SocietyMemberships.FromMinimumReturn,
                    Deductions.DeductionRate
        ORDER BY ContactId

        FOR XML PATH ('Owner'), TYPE)

    FOR XML PATH ('PurchaseOrders'), TYPE

END

查询可能可以改进，但没有表定义和一些示例数据，测试起来有点困难.

The query can probably be improved but without table definitions and some sample data it's a bit difficult to test it.

查看这个 SQL Fiddle 以获得一个稍微调整的版本，它应该给相同的结果.

Check out this SQL Fiddle for a slightly tweaked version that should give the same result.

这篇关于将 sql case 语句嵌入到使用 FOR XML 的 sql 查询中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！