在查询中查询其中 table2.amount 的总和(如果 stat=1)大于表 1 费用的总和 [英] query inside query where sum of table2.amount (if stat=1) is greate than sum of table 1 expense

问题描述

我有两张桌子

表:交易

id . user_id . amount . status
1 - - 100 -- --- 500 - ----- 1
2 - - 100 -- --- 100 - ----- 0
3 - - 110 -- --- 200 - ----- 1

表格:广告系列

id . user_id . bid . status . budget . expense . size
1 -- 100 ---- 80  --- 1 ------- 200 ------ 200 ---- 5 --
2 -- 109 ---- 75  --- 1 ------- 050 ------ 030 ---- 2 --
3 -- 100 ---- 65  --- 1 ------- 700 ------ 065 ---- 2 --
4 -- 107 ---- 77  --- 0 ------- 020 ------ 020 ---- 2 --
5 -- 90 ----- 87  --- 1 ------- 120 ------ 090 ---- 7 --

我需要使用 php 和 mysql 过滤后的 campaign.id

I need campaign.id after filters using php and mysql

条件:如果从用户收到的总资金/交易大于用户的总费用.货币也是已批准的货币(1 = 已批准，0 = 待定).广告系列是在手段 1，谁提出了最高出价

condition: if total money/transaction received from a user is greater than total expenses by a user. Money is also approved money (1=approved, 0=pending). Campaign is on means 1, who placed maximum bid

(sum(transaction.amount) WHERE transaction.status = 1 of that any user) > sum(campaign.expense of that user)

出价 = 最高(出价)

bid = max(bid)

营地状态 = 1

一步解决

 mysql_query("SELECT campaign.* FROM campaign c,transaction t  
            WHERE budget>expense AND status='1' AND size='2' 
            ON c.user_id=t.user_id 
            HAVING SUM (CASE WHEN transaction.status=1 THEN transaction.amount ELSE 0 END) > SUM(campaign.expense)"));

不工作

或者，如果在 3 个步骤中有任何解决方案 -

Alternatively if any solution in 3 steps -

第一步

   mysql_query("SELECT id,user_id FROM campaign 
        WHERE budget>expense AND status='1' AND size='2'")

第二步

  keep id,user_id of 1st step WHERE SUM(transaction.amount status=1)
             > SUM(campaign.expense)

第三步

  mysql_query("SELECT id,user_id FROM campaign 
        WHERE step2 satisfied")

请帮忙，解决了我就睡觉

Please help, I will sleep after solving it

推荐答案

据我了解，您希望实现以下目标:

From what I understand, you want to achieve the following:

SELECT 
    c.*
FROM campaign c
JOIN (SELECT
        SUM(amount) AS summed
    FROM transaction t
    WHERE status = 1
) temp t
WHERE c.status = 1
HAVING t.summed > SUM(c.expense)
ORDER BY c.bid DESC
LIMIT 1

这篇关于在查询中查询其中 table2.amount 的总和(如果 stat=1)大于表 1 费用的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！