SQL - 可以在特定值之间求和行吗？

my apologies if this is a duplicate but I could not find an answer to my particular question. I have a table that lists products on a sales order, and their various quantities. Some products are components for other products and are denoted so with a flag. I would like to know if there is a way to have a running total for the parent/normal items that would reset on each parent/normal item.

我很抱歉,如果这是重复但我无法找到我的特定问题的答案。我有一张表格,列出了销售订单上的产品及其各种数量。有些产品是其他产品的组件,并带有标志。我想知道是否有一种方法可以为每个父/正常项重置的父/正常项具有运行总计。

Here is an example of the table data and my desired output:

这是表数据和我想要的输出的示例:

OrderNo Item Qty Regular Line 349443 AFU20451-KIT1 1 Y 1 349443 AFU20451 0 N 2 349443 HAWKE-14252 1 N 3 349443 RGPM-25H4 1 N 4 349443 AV-003-265 1 Y 5 349443 AV-A00090-KIT 1 Y 6 349443 AV-A00091 1 N 7 349443 AV-A00090 1 N 8 349443 AV-00043 1 N 9 349443 AV457/310GR/FP 2 Y 10

订单号项目常规线349443 AFU20451-KIT1 1 Y 1 349443 AFU20451 0 N 2 349443 HAWKE-14252 1 N 3 349443 RGPM-25H4 1 N 4 349443 AV-003-265 1 Y 5 349443 AV-A00090-KIT 1 Y 6 349443 AV-A00091 1 N 7 349443 AV-A00090 1 N 8 349443 AV-00043 1 N 9 349443 AV457 / 310GR / FP 2 Y 10

desired output:

OrderNo Item Qty 349433 AFU20451-KIT1 3 349433 AV-003-265 1 349433 AV-A00090-KIT 4 349433 AV457/310GR/FP 2

订单编号349433 AFU20451-KIT1 3 349433 AV-003-265 1 349433 AV-A00090-KIT 4 349433 AV457 / 310GR / FP 2

As you can see, I would like to reset the sum every time it says Y, only include the parent item (I could get around this as I can keep the order of the items the same, could maybe use row number). I have been trying to use Over and Partition by in order to do this, but to no avail. Let me know if this is even possible or if you need any further information.

正如你所看到的,我想在每次说Y时重置总和,只包括父项(我可以绕过这个,因为我可以保持项目的顺序相同,也许可以使用行号)。我一直试图使用Over和Partition来做到这一点,但无济于事。如果有可能,或者您需要任何进一步的信息,请告诉我。

4 个解决方案

#1

with cte as 
 ( 
   select OrderNo, 
      -- only return the main item
      case when Regular = 'Y' then Item end AS Item, 
      Qty,
      -- assign a unique number to each `YNNN..` component group
      -- needed for GROUP BY in next step
      sum(case when Regular = 'Y' then 1 else 0 end)
      over (partition by OrderNo
            order by Line
            rows unbounded preceding) as grp
   from myTable
 )
select OrderNo,
   -- find the matching value for the main component
   max(Item), 
   sum(Qty)
from cte
group by OrderNo, grp

#2

Current representation is against 1st Codd's rule.

目前的代表是反对第一个Codd的规则。

Rule 1: The information rule: All information in a relational data base is represented explicitly at the logical level and in exactly one way – by values in tables.

规则1:信息规则:关系数据库中的所有信息都在逻辑级别明确表示,并以完全一种方式表示 - 表中的值。

But I believe you can still create FUNCTION/PROCEDURE and iterate row one by one with IF statement for Y/N. E.g. you create new table, IF Y - add new row to table, IF N - add +1 to QTY to latest row.

但我相信您仍然可以创建FUNCTION / PROCEDURE并使用IF语句逐个迭代行以进行Y / N.例如。你创建新表,如果是 - 向表中添加新行,IF N - 向最新行添加+1到QTY。

#3

I would create two separate tables: manufacturer & part, to get the values so you don't have to hand-jam each inventory, or care about where they fall in the invoice list.

我会创建两个单独的表:制造商和零件,以获取值,这样您就不必手动堵塞每个库存,或者关心它们落入发票清单的位置。

[1 []2

Then, all you would need to do is compare the values to the part table to get this data. It's more work upfront, but will pay off to have this all saved and stored. A future sample query would look something like:

然后,您需要做的就是将值与零件表进行比较以获取此数据。这是更多的前期工作,但是将所有这些都保存和存储将得到回报。未来的示例查询看起来像:

SELECT OrderNo.OrderTable, Item.OrderTable, Sum(Qty.OrderTable) AS Quantity
FROM OrderTable INNER JOIN Part ON OrderTable.Item = Table.PartName
GROUP BY OrderNo.OrderTable, Item.OrderTable, Regular.OrderTable, Part.ParentID;

#4

try this:

select orderno, item, sum(qty) over(partition by regular order by regular)
from your_table 
group by  orderno, item, regular