我的聚合不受 ROLLUP 影响
我有一个类似于以下内容的查询：
SELECT CASE WHEN (GROUPING(Name) = 1) THEN 'All' ELSE Name END AS Name,
CASE WHEN (GROUPING(Type) = 1) THEN 'All' ELSE Type END AS Type,
sum(quantity) AS [Quantity],
CAST(sum(quantity) * (SELECT QuantityMultiplier FROM QuantityMultipliers WHERE a = t.b) AS DECIMAL(18,2)) AS Multiplied Quantity
FROM @Table t
GROUP BY Name, Type WITH ROLLUP
我试图返回名称、类型、总数量和总数量乘以任意数字的列表。到目前为止一切都很好。我还需要返回每个名称和每个类型的小计行，例如以下
Name Type Quantity Multiplied Quantity
------- --------- ----------- -------------------
a 1 2 4
a 2 3 3
a ALL 5 7
b 1 6 12
b 2 1 1
b ALL 7 13
ALL ALL 24 40
前 3 列很好。不过，我在相乘数量的汇总行中得到空值。我认为发生这种情况的唯一原因是因为 SQL 无法将最后一列识别为聚合，因为我已经将它乘以了某个值。
我能以某种方式解决这个问题而不让事情变得太复杂吗？
如果做不到这一点，我将回到临时表上。
I have a query similar to the following:
SELECT CASE WHEN (GROUPING(Name) = 1) THEN 'All' ELSE Name END AS Name,
CASE WHEN (GROUPING(Type) = 1) THEN 'All' ELSE Type END AS Type,
sum(quantity) AS [Quantity],
CAST(sum(quantity) * (SELECT QuantityMultiplier FROM QuantityMultipliers WHERE a = t.b) AS DECIMAL(18,2)) AS Multiplied Quantity
FROM @Table t
GROUP BY Name, Type WITH ROLLUP
I'm trying to return a list of Names, Types, a summed Quantity and a summed quantity multiplied by an arbitrary number. All fine so far. I also need to return a sub-total row per Name and per Type, such as the following
Name Type Quantity Multiplied Quantity
------- --------- ----------- -------------------
a 1 2 4
a 2 3 3
a ALL 5 7
b 1 6 12
b 2 1 1
b ALL 7 13
ALL ALL 24 40
The first 3 columns are fine. I'm getting null values in the rollup rows for the multiplied quantity though. The only reason I can think this is happening is because SQL doesn't recognize the last column as an aggregate now that I've multiplied it by something.
Can I somehow work around this without things getting too convoluted?
I will be falling back onto temporary tables if this can't be done.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
在获取乘数的子查询中，您有
WHERE a=b。a或b是否来自主查询中的表？如果这些值是静态的（与主查询无关），看起来应该没问题...
如果
a或b值是name或type字段，对于汇总记录，它们可以为NULL。如果是这样，您可以更改为类似于...CAST(sum(quantity * ()) AS DECIMAL(18,2)) 。如果
a或b是主查询中的其他字段，您将得到多个记录，而不仅仅是一个乘数。您可以更改为...CAST(sum(quantity) * (SELECT MAX(multiplier) FROM ...)) AS DECIMAL(18,2))In your sub-query to acquire the multiplier, you have
WHERE a=b. Are eitheraorbfrom the tables in your main query?If these values are static (nothing to do with the main query), it looks like it should be fine...
If the
aorbvalues are thenameortypefield, they can beNULLfor the rollup records. If so, you can change to something similiar to...CAST(sum(quantity * (<multiplie_query>)) AS DECIMAL(18,2)).If
aorbare other field from your main query, you'd be getting multiple records back, not just a single multiplier. You could change to something like...CAST(sum(quantity) * (SELECT MAX(multiplier) FROM ...)) AS DECIMAL(18,2))