mysql - MySQL Query带有几个“for each”

我有这张桌子：

idTransactions  idCampaignsList idMemberCard    amountOriginal  amountFinal   dateTransaction
1   2   1   50.00   100.00          2012-10-31 12:45:41
2   3   1   0.00    -50.00          2012-10-31 12:47:25
3   2   2   255.00  255.00          2012-10-31 17:19:07
4   1   2   95.00   95.00           2012-11-02 20:38:36
5   3   2   0.00    -400.00         2012-11-02 20:39:50
24  1   4   10.00   2.00            2012-11-03 11:16:3

使用此查询

SELECT  SUM(amountOriginal) AS euro,
SUM(amountFinal) AS deducted,
EXTRACT(YEAR_MONTH FROM(dateTransaction)) AS period
FROM transactions
INNER JOIN campaignsList ON campaignsList.idCampaignsList = transactions.idCampaignsList
INNER JOIN customers ON customers.idCustomer = campaignsList.idCustomer
WHERE   customers.idCustomer = 14
        AND
        transactions.idCampaignsList = 2
GROUP BY period
ORDER BY period

我得到这个结果

euro             deducted   period
305.00           305.00         201210
14860.46            -22758.50   201211
1845.00         -34710.00   201212

在过去的12个月内，“收费”和“出院”的总和。
现在，idCampaignsList可以是1，2，也可以是500，这取决于有多少“campaigns”有我的idCustomer（通过JOIN检索）。
我想要一个查询dinamic，即“对于每个”idCampaigns列表，为我打印amountOriginal和amountFinal的总和。
在previos表中，我希望

idCampaignsList    SUM(amountOriginal)   SUM(amountFinal)  period
1                  50                    50                201210
2                  255                   255               201210
2                  95                    -305              201211
4                  10                    2                 201211

因此，对于每个时段，每个不同的idCampaignsList的总和列，其中idCampaignsList是对等的（从myOtherTable中选择idCampaignsList，其中idCustomer=14）

最佳答案

我想问一下dinamic，每个“竞选活动”列表，打印
我是金额的总和。
我认为你说的several For each是一个GROUP BY transactions.idCampaignsList。
尝试将transactions.idCampaignsList添加到SELECT列表，从transactions.idCampaignsList = 2子句中删除谓词WHERE，并在GROUP BY子句中列出该列，如下所示：

SELECT
  transactions.idCampaignsList
  SUM(amountOriginal) AS euro,
  SUM(amountFinal) AS deducted,
  EXTRACT(YEAR_MONTH FROM(dateTransaction)) AS period
FROM transactions
INNER JOIN campaignsList
        ON campaignsList.idCampaignsList = transactions.idCampaignsList
INNER JOIN customers
        ON customers.idCustomer = campaignsList.idCustomer
WHERE customers.idCustomer = 14
GROUP BY period, transactions.idCampaignsList
ORDER BY period

关于mysql - MySQL Query带有几个“for each”，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/13738104/