当mssql中的sum数据使用join三个表时，输出不正确 [英] when sum data in mssql using join three tables , output was not correct

问题描述

SELECT p.posid,  v.locgrpcde,p.paydte,p.tendercde,
(
    SELECT SUM(p.localamt - (p.changeamt+p.nochgamt)) AS net_amount
)
FROM psbillp p JOIN msPos pv ON p.posid = pv.posid
JOIN msLocation v ON pv.loccde = v.loccde
WHERE v.locgrpcde='GNG10 GROUP' AND paydte='2014-04-01 00:00:00.000'
AND tendercde='CASH'
GROUP BY p.posid, v.locgrpcde,p.paydte,p.tendercde;

结果是



G10-POS01 GNG10 GROUP 2014-04-01 00 ：00：00.000现金121970.00



但我真正想要的价值是118540.00而不是121970.00，



请，任何人都可以帮助我吗？







从OP解决方案中添加



这是Excel文件。



locgrpcde paydte netamount tendercde

result is

G10-POS01 GNG10 GROUP 2014-04-01 00:00:00.000 CASH 121970.00

but i really want value is 118540.00 instead of 121970.00,

Please, can anyone help me?


[edit]
Added from OP "solution"

This is Excel File.

locgrpcde paydte netamount tendercde

GNG10 GROUP 2014-4-1 0:00:00.000 50 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 200 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 180 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 170 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 160 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 300 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 320 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 340 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 350 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 360 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 400 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 380 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 450 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 410 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 500 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 480 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 460 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 550 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 580 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 680 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 700 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 750 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 710 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 800 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 720 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 810 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 900 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 930 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 950 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 730 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 680 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 1050 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 1150 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 1200 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 1300 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 1450 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 1500 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 1030 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 1510 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 1520 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 1610 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 1750 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 1900 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 1950 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 1980 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 1830 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 1280 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 2130 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 2190 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 2200 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 2130 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 2300 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 2390 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 2640 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 2650 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 2670 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 2970 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 3550 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 3600 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 3680 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 3900 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 3590 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 3150 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 2800 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 1320 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 920 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 5950 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 10000 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 11800 CASH

[/ edit]

[/edit]

推荐答案

尝试

Try

SELECT
    p.posid
   ,v.locgrpcde
   ,p.paydte
   ,p.tendercde
   ,(p.localamt - p.changeamt - p.nochgamt) AS net_amount
FROM
   psbillp p 
   INNER JOIN msPos pv ON p.posid = pv.posid
   INNER JOIN msLocation v ON pv.loccde = v.loccde
WHERE
   v.locgrpcde = 'GNG10 GROUP'
   AND paydte = '2014-04-01 00:00:00.000'
   AND tendercde = 'CASH'
GROUP BY
    p.posid
   ,v.locgrpcde
   ,p.paydte
   ,p.tendercde;

代替。

instead.

这篇关于当mssql中的sum数据使用join三个表时，输出不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！