如何从两个数据表中减去相似列和值的行？

问题描述：

如何从两个sql数据表中减去值？我有两个数据表，我想首先匹配table1partnum列，如果它与表2partnum匹配，则从table2FinalStockout中减去table1FinalstockIN值然后另外，如果减去值是neagtive，那么如何停止减法然后显示0或null或根本不显示。

什么我试过了：

How to subtract values from two sql datatables?I have two datatable where I want to first match table1 "partnum" columns and if its match with table 2 "partnum" then subtract table1 "FinalstockIN"values from table2"FinalStockout" then display it in another column.Also, how to stop the subtraction if the minus value is neagtive then show 0 or null or dont show at all.

What I have tried:

with spare_parts as (
 
 SELECT s.SN,partnum,partdesc,partmodel,stockin,dateofstockin, (SELECT 
   SUM(stockin)
   FROM Tbl_SpareParts
   GROUP BY partnum
   Having partnum = s.partnum) AS FinalTotalStockIN FROM Tbl_SpareParts s

),
cases as (
 SELECT b.SN,partnum,partdesc,partmodel,outstock,outstockdate,outstockcaseid_billnum,remarks, (SELECT SUM(outstock)
   FROM Tbl_OutCaseID
   GROUP BY partnum
   having partnum = b.partnum) AS FinalTotalStockOut FROM Tbl_OutCaseID b
)
select
 s.SN,s.partnum,s.partdesc, s.partmodel, s.stockin, 
  s.dateofstockin, s.FinalTotalStockIN,
  c.outstock, 
  c.outstockdate, c.outstockcaseid_billnum, 
  c.remarks, c.FinalTotalStockOut,(FinalTotalStockIN - FinalTotalStockOut) as balance
  from
 
  spare_parts s
  join cases c on s.partnum = c.partnum

答

怎么样？

how about this?

select 
IF ISNULL(Table2.FinalStockout-Table1.FinalstockIN) then 0 else (Table2.FinalStockout-Table1.FinalstockIN)
 as FinalstockIN
from Table1 inner join Table2 on Table1.partnum=Table2.partnum

;WITH CTE AS (
SELECT DISTINCT 
        TSP.SN,TSP.partnum,TSP.partdesc,TSP.partmodel,TSP.stockin,TSP.dateofstockin,
        SUM(TSP.stockin)OVER(PARTITION BY TSP.partnum ORDER BY (SELECT 1))AS 
        FinalTotalStockIN,
        TOC.outstock,TOC.outstockdate, TOC.outstockcaseid_billnum, TOC.remarks,
        SUM(TOC.outstock)OVER(PARTITION BY TOC.partnum ORDER BY (SELECT 1)) AS 
        FinalTotalStockOut
  FROM
   DBName1.dbo.Tbl_SpareParts AS TSP 
     INNER JOIN DBName2.dbo.Tbl_OutCaseID TOC 
      ON (TSP.Partnum = TOC.Partnum)
              )

SELECT  SN,Partnum,PartModel,StockIn,DateofStockIn,FinalTotalStockIN,Outstock,
        OutStockDate,OutStockCaseid_billnum,Remarks,FinalTotalStockOut,
        CASE 
         WHEN SIGN((FinalTotalStockIN - FinalTotalStockOut))=1 
          THEN  
           (FinalTotalStockIN - FinalTotalStockOut)
             ELSE
           0 END as Balance

 FROM CTE

如何从两个数据表中减去相似列和值的行？

相关推荐