Subtracting rows based on condition-CodePudding

Suppose I have a table that looks like this:

OrderNumber  OrderType
1            D          
1            D          
1            R 
2            D
2            R
3            D          
3            D          
3            D
3            R          
3            R

The result should be:

OrderNumber  OrderType
1            D          
3            D

Here, an R would indicate to remove one row from the order. We see in the first example we have 2 D's and 1 R, so we remove one D are replaced with 1 D. Is there a way to do this in SQL?

CodePudding user response：

You can use window function. This is sqlite syntax, but mysql should be fairly close.

select A.OrderNumber,A.OrderType

from (select OrderNumber,OrderType,row_number() over(partition by OrderNumber) as RN 
from b where OrderType='D') A
left join

(select sum(case when OrderType='R' then 1 else 0 end) as cnt,OrderNumber
from b group by OrderNumber) B
on A.OrderNumber=B.OrderNumber
where A.rn>B.cnt;

CodePudding user response：

If your mysql version support cte and window function, we can try to use ROW_NUMBER window function make row number for each OrderNumber OrderType

Then use EXISTS subquery to judge OrderType = D row number needs to be greater than the maximum row number from R.

with cte as (
  SELECT *,
         ROW_NUMBER() OVER(PARTITION BY OrderNumber,OrderType) rn,
         COUNT(*) OVER(PARTITION BY OrderNumber,OrderType) cnt
  FROM T
)
SELECT c1.OrderNumber,
       c1.OrderType 
FROM cte c1
WHERE EXISTS (
   SELECT 1 
   FROM cte c2
   WHERE c1.OrderNumber = c2.OrderNumber 
   AND c2.OrderType = 'R'
   AND c1.rn > c2.cnt
)
AND c1.OrderType = 'D'

sqlfiddle