field subtraction sql server-CodePudding

If I would like to subtract the fields from each other,

i.e. in A there are 11 fields described as 'Faktura zakupu' and in B there are 5 fields described as 'Faktura zakupu'. I would like to get a return of records in the form of 6 items 'Faktura zakupu' (11-5 = 6)

I tried the EXCEPT operation, but it does not return the desired results

what operation do i need to perform?

enter image description here

CodePudding user response：

You can add row number to each row in both tables. Then SQL Server can determine that the first (Faktura zakupu, Original) in table A is a duplicate of the first (Faktura zakupu, Original) in table B and remove it during EXCEPT operation:

SELECT Name, StatusReq, ROW_NUMBER() OVER (PARTITION BY Name, StatusReq ORDER BY (SELECT NULL))
FROM a

EXCEPT

SELECT Name, StatusReq, ROW_NUMBER() OVER (PARTITION BY Name, StatusReq ORDER BY (SELECT NULL))
FROM b

It'll return 6 rows from table A... numbered 6 through 11.