根据另一个表中的值运行查询

i have 2 tables, evouchers, pos_vendor_map. pos_vendor_map table have list of POS_ID's. evouchers will have multiple transaction and can be of same POS_ID.

我有2张桌子，evouchers，pos_vendor_map。 pos_vendor_map表有POS_ID列表。 evouchers将具有多个事务，并且可以具有相同的POS_ID。

pos_vendor_map:

pos_vendor_map：

ID  POS_ID     VendorName
1   1          Test1
2   2          Test2

evoucher:

电子奖券：

ID  TRANS_TYPE  TRANS_AMOUNT TRANS_STATUS POS_ID
1   1           2000         1            1
2   1           3000         1            1
3   1           1000         1            2
4   1           5000         1            2

Now i want to get the sum of transactions for each POS_ID from evouchers table. So it will be something like

现在我想从evouchers表中获取每个POS_ID的事务总和。所以它会是这样的

foreach(POS_ID)
{
SUM(TRANS_AMOUNT)
}

currently i am getting results but by giving POS_ID in query, i want it to be dynamic, like to check the total numbers of POS_IDs and then for each run this query.

目前我得到的结果，但通过在查询中给出POS_ID，我希望它是动态的，比如检查POS_ID的总数，然后为每次运行此查询。

SELECT
    (SUM(IF(ev.TRANS_TYPE='1', ev.TRANS_AMOUNT,0)) - SUM(IF(ev.TRANS_TYPE='2', ev.TRANS_AMOUNT,0))) AS CreditStatus,
    (select  pvm.VENDOR_NAME from pos_vendor_map pvm  where pvm.POS_ID = ev.POS_ID) AS VendorName
FROM
    evouchers ev
WHERE
    ev.POS_ID='1' and ev.TRANS_STATUS='1';

Thanks

谢谢

3 个解决方案

#1

Your query should probably look like this:

您的查询应该如下所示：

SELECT SUM(CASE WHEN ev.TRANS_TYPE = 1 THEN ev.TRANS_AMOUNT
                WHEN ev.TRANS_TYPE = 2 THEN -ev.TRANS_AMOUNT
                ELSE 0
           END) as CreditStatus
      -- pvm.VENDOR_NAME 
FROM evouchers ev JOIN
     pos_vendor_map pvm
     ON pvm.POS_ID = ev.POS_ID;

If you want one row per POS_ID, then:

如果你想要每个POS_ID一行，那么：

SELECT SUM(CASE WHEN ev.TRANS_TYPE = 1 THEN ev.TRANS_AMOUNT
                WHEN ev.TRANS_TYPE = 2 THEN -ev.TRANS_AMOUNT
                ELSE 0
           END) as CreditStatus,
       pvm.VENDOR_NAME 
FROM evouchers ev JOIN
     pos_vendor_map pvm
     ON pvm.POS_ID = ev.POS_ID
GROUP BY ev.POS_ID, pvm.VENDOR_NAME

#2

you can find sum from this query

你可以从这个查询中找到总和

select POS_ID,count(POS_ID) total from evoucher group by POS_ID

#3

If you want cumulative sum, you can try something like this:

如果你想累积总和，你可以尝试这样的事情：

 SELECT C.VendorName
 ,A.*
, @r:= CASE WHEN @g=A.POS_ID THEN 
            CASE WHEN A.TRANS_TYPE=1 THEN @r+A.TRANS_AMOUNT WHEN A.TRANS_TYPE=2 THEN @r-A.TRANS_AMOUNT ELSE 0 END
       ELSE A.TRANS_AMOUNT END AS AMOUNT
, @g:=A.POS_ID g
FROM EVOUCHER A
CROSS JOIN (SELECT @r:=0 , @g:=0) B 
INNER JOIN POS_VENDOR C ON A.POS_ID = C.POS_ID
ORDER BY A.POS_ID, A.ID;

Output:

输出：

VendorName  id  trans_type  trans_amount    trans_status    pos_id  AMOUNT  g
Test1   1   1   2000    1   1   2000    1
Test1   2   1   3000    1   1   5000    1
Test1   5   2   1000    1   1   1000    1
Test2   3   1   1000    1   2   1000    2
Test2   4   1   5000    1   2   6000    2
Test2   6   2   500 1   2   500 2

Sample data:

样本数据：

CREATE TABLE EVOUCHER(id int, trans_type int, trans_amount int, trans_status int, pos_id int);
INSERT INTO EVOUCHER VALUES (1,1,2000,1,1);
INSERT INTO EVOUCHER VALUES (2,1,3000,1,1);
INSERT INTO EVOUCHER VALUES (3,1,1000,1,2);
INSERT INTO EVOUCHER VALUES (4,1,5000,1,2);
INSERT INTO EVOUCHER VALUES (5,2,1000,1,1);
INSERT INTO EVOUCHER VALUES (6,2,500,1,2);
CREATE TABLE POS_VENDOR (id int, pos_id int, VendorName VARCHAR(20));
INSERT INTO POS_VENDOR VALUES (1,1,'Test1');
INSERT INTO POS_VENDOR VALUES (2,2,'Test2');

#1