使用Cross Apply的SQL查询有条件地求和 [英] SQL Query using Cross Apply to get Sum Conditionally

问题描述

要产生的输出

以此为参考，但现在具有不同的情况 SQL Server查询:有条件地相加

using this as reference but now with different scenario SQL Server query : get the sum conditionally

说明:

项目"，销售"和备注"列是数据库的给定列，新销售"列是公式化的列，在其中，它获取具有除默认n/a备注之外的具有相同关键字备注的项的总和.

Item, Sales, and remarks columns are given column from a database, New Sales column is a formulated column where in, it is getting the sum of items with the same keyword remarks except the default n/a remarks.

(不管说的话不是完全相同，至少存在上图所示的共同点-第5项包含"new"，但仍与第6项相加，因为它们的相似关键字为"small" "

(regardless the remarks is not fully identical, at least there's a common similarity like what is on the image above - item 5 has "new" in it, still it sums up with item 6 because of their similar keyword found "small")

使用的代码

第一选项-使用分区-这不起作用，因为当备注彼此不同时，将无法正确获得总和(对于item5和item6)

FIRST OPTION- using partition - This doesn't work because when the remarks is not identical to each other it will not get the sum properly (for item5 and item6)

 CASE
     WHEN ([remarks] not  like '%big%') AND ([remarks] not  like '%PAENGS%') 


     THEN sales 
     ELSE SUM(sales) OVER(PARTITION BY [remarks])
     END as 'New Sales'

第二个选项-使用交叉应用-因此，我不得不这样做，但是我迷失了方向，因为它没有获得所需的输出.

SECOND OPTION -using Cross Apply - So it leave me to this, but I was lost as it is not getting the desired output.

 CROSS APPLY
     SELECT
        d.*, 
        NewSales = 
        CASE
        WHEN ([remarks] not like '%big%') or ([remarks] not like '%small%')
        THEN Sales 
        ELSE x.NewSales
        END


        FROM #MSRSuperFinal3  d
        CROSS APPLY(SELECT NewSales = SUM(Sales)
                    FROM #MSRSuperFinal3 
                    WHERE ([remarks] like  '%big%') or ([remarks] like  '%small%')
        )x

我们将不胜感激任何帮助

Any help will be highly appreciated

推荐答案

使用CROSS APPLY

SELECT *
FROM temp t
CROSS APPLY(
    SELECT SUM(sales)
    FROM temp
    WHERE
        remarks LIKE '%' + t.remarks + '%'
        OR t.remarks LIKE '%' + remarks + '%'
)x(NewSales)
WHERE remarks <> 'n/a'

UNION ALL

SELECT *, 
    NewSales = sales
FROM temp
WHERE remarks = 'n/a'
ORDER BY item

---

根据您的评论，这应该是您的最终查询:

---

Based on your comment, this should be your final query:

SELECT * 
FROM #MSRSuperFinal3 t
CROSS APPLY( 
    SELECT 
        SUM(CurrentMonth)
    FROM #MSRSuperFinal3 
    WHERE 
        t.misc LIKE '%' + misc + '%'
        OR misc LIKE '%' + t.misc + '%'
)x(NewSales) 
WHERE 
    ([misc] LIKE '%BIGKAHUNA%') 
    or ([misc] LIKE '%PAENGS%') 

UNION ALL 
SELECT *, 
    NewSales = CurrentMonth 
FROM #MSRSuperFinal3 
WHERE 
    ([misc] not like '%BIGKAHUNA%')
    AND ([misc] not like '%PAENGS%')
    AND ([misc] not like '%ROBINSONS%')
ORDER BY location, name 

这篇关于使用Cross Apply的SQL查询有条件地求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！