基于ID列与Flag的Oracle金额汇总需求及问题求助

Hey there! Let's break down how to adjust your SQL query to get the exact output you're expecting.

Understanding Your Requirement #

You need to:

• Keep only records where flg = 'Y'
• For each flg='Y' record, sum all amounts (including those from flg='N' records) that belong to the same ordr, item, and share the same base dtl_item (e.g., dtl_item=1.1 should roll up to dtl_item=1)

What's Wrong with Your Original Query #

Your original query has a couple of key issues:

1. Typo: You used dtl_num instead of dtl_item (matching your input data's column name)
2. Incorrect Grouping: Grouping by dtl_item directly splits dtl_item=1 and dtl_item=1.1 into separate groups, so amounts from flg='N' records don't roll up to the flg='Y' records
3. No Filter for flg='Y': Your query would include groups from flg='N' records, which aren't present in your expected output

Correct SQL Query #

This query uses a window function to sum amounts by the base dtl_item (using FLOOR() to capture the integer part), then filters to keep only flg='Y' records:

SELECT
    ordr,
    item,
    SUM(amt) OVER (
        PARTITION BY ordr, item, FLOOR(dtl_item)
    ) AS amt,
    flg,
    dtl_item
FROM your_table
WHERE flg = 'Y'
ORDER BY ordr, item, dtl_item;

If your database doesn't support FLOOR() (or if your dtl_item uses a different naming pattern), you can use string manipulation to extract the base part. For example, if dtl_item is a string like '1.1':

SELECT
    ordr,
    item,
    SUM(amt) OVER (
        PARTITION BY ordr, item, SPLIT_PART(dtl_item::text, '.', 1)
    ) AS amt,
    flg,
    dtl_item
FROM your_table
WHERE flg = 'Y'
ORDER BY ordr, item, dtl_item;

How This Works #

• The PARTITION BY ordr, item, FLOOR(dtl_item) clause groups records by order, item, and the integer part of dtl_item (so 1 and 1.1 fall into the same group)
• SUM(amt) OVER (...) calculates the total amount for each group, assigning it to every flg='Y' record in the group
• The WHERE flg='Y' clause filters out all flg='N' records, matching your expected output

Testing with Your Data #

When you run this query against your input data, you'll get exactly the expected result:

ordr | item| amt|flg |dtl_item
637262 1 98.58 Y 1
637262 2 1182.7 Y 1
637262 2 2365.4 Y 2
661209 1 960 Y 1
661209 2 96 Y 1

内容的提问来源于stack exchange，提问作者beckham