MYSQL 带有 SUM 结果以及 GROUP BY 和 SUM Again

Question

我有一个查询，需要在对筹集的金额求和后对 trid 列进行求和

SELECT
  `ppm_campaign_id`.`meta_value` AS `campaign_id`,
  `trasnlations`.`trid`          AS `trid`,
  `ppm_campaign_value`.`meta_value` AS `raised`
FROM (((`thf_posts` `posts`
     LEFT JOIN `thf_postmeta` `ppm_campaign_id`
       ON (((`ppm_campaign_id`.`meta_key` = 'campaign_id')
            AND (`ppm_campaign_id`.`post_id` = `posts`.`ID`))))
    LEFT JOIN `thf_postmeta` `ppm_campaign_value`
      ON (((`ppm_campaign_value`.`meta_key` = 'campaign_value')
           AND (`ppm_campaign_value`.`post_id` = `posts`.`ID`))))
   LEFT JOIN `thf_icl_translations` `trasnlations`
     ON ((`trasnlations`.`element_id` = `ppm_campaign_id`.`meta_value`)))
WHERE ((`ppm_campaign_id`.`meta_value` IS NOT NULL)
       AND (`trasnlations`.`trid` IS NOT NULL))
GROUP BY `ppm_campaign_id`.`meta_value`

结果

campaign_id trid    raised
1022        564     4137.5
1031        564     3937.5
1698        653     3010
1700        655     10
1702        657     750
1712        653     3030
1713        655     20
1727        657     20
2163        682     0.9
2164        682     50
2166        683     200
2168        684     50

现在我想要合并 trid 的总和，因此对 trid = 682 求和，在两个 Campaign_id 中，raised 的结果应为 50.9

预期结果

 campaign_id    trid    raised
    1022        564     8075
    1031        564     8075
    1698        653     3010
    1700        655     10
    1702        657     750
    1712        653     3030
    1713        655     20
    1727        657     20
    2163        682     50.9
    2164        682     50.9
    2166        683     200
    2168        684     50

Answer 1

考虑到查询的输出存储在 temptable 中。请尝试以下查询。希望对您有所帮助。

SELECT t1.campaign_id, t1.trid, t2.raised_sum 
FROM temptable t1 JOIN (
SELECT trid, SUM(raised) as raised_sum 
FROM temptable GROUP BY trid) t2 
ON t1.trid=t2.trid
GROUP BY t1.campaign_id, t1.trid, t2.raised_sum;

如果可以提供有关查询中包含的表和示例数据的更多详细信息，我们可以尝试直接从中获取数据。