SQL 拆分总计 [英] SQL split totals

问题描述

目前我在 SQL 中有以下结果:

Currently I have the following result in SQL:

<头>

数量	描述	音量	权重
4	鲜花	3,4	4

这是一个 XML 格式.所以我想做什么，我想我需要用户FOR XML PATH"以某种方式，但我不确定如何实现以下结果:

This is in an XML format. So what I want to do, and I think I need to user "FOR XML PATH" in a certain way, but I am not sure how to achieve the following result:

<头>

数量	描述	音量	权重
1	鲜花	0,85	1
1	鲜花	0,85	1
1	鲜花	0,85	1
1	鲜花	0,85	1

所以我需要根据总数量(4)来划分XML路径.对于每 (4) 个产品，我需要创建一个新行.然后除以体积和重量(/qty).

So I need to divide the XML path based on the total qty (4). For each (4) products, I need to create a new row. Then divide the volume and the weight (/qty).

谁能帮我把我推向正确的方向?

Can anyone help me to push me into the right direction?

第一个结果，数量为 4，是临时表中的结果.我将临时表中的数据提取为 XML 格式.这是一个片段

The first result, qty of 4, is a result in a temp table. I extract the data from the temp table into XML format. Here is a snippet

(SELECT "qty" = value('(@col24)[1]', 'varchar(50)'),  "weight"  = value('(@col28)[1]', 'varchar(50)'), "volume" = value('(@col26)[1]', 'decimal(16,2)') FOR XML PATH('product'), ROOT('products'), TYPE)  

数量、重量和体积代表总数.这就是我想要创建的产品"对于每个数量".

The qty, weight and volume represents the totals. This is what I want to devide to create a "product" for each "qty".

推荐答案

您可以使用递归 CTE 来拆分行(如果您的数量大于 100，则可能需要提高递归限制).

You can use a recursive CTE to split the rows (you might need to up the recursion limit if your quantity can be higher than 100).

declare @Test table (qty int, [description] varchar(64), volume decimal(9,2), [weight] decimal(9,2))

insert into @Test (qty, [description], volume, [weight]) values (4, 'Flowers', 3.4, 4);

with cte as (
  select qty, [description], volume, [weight], 1 as rn
  from @Test
  union all
  select qty, [description], volume, [weight], rn + 1
  from cte
  where rn < qty
)
select 1 qty, [description], cast(volume / qty as decimal(9,2)) volume, cast([weight] / qty as decimal(9,2)) [weight]
from cte
for xml path('product'), root('products'), type;
-- option (maxrecursion 200); -- If you need to increase it above the default of 100

注意:如果你设置了 DDL+DML，就像我展示的那样，你的问题会让人们更容易回复.

Note: If you setup the DDL+DML, as I shown, in your questions you make it much easier for people to reply.

这篇关于SQL 拆分总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！