mysql - 使用 IF 控制流函数根据行的内容返回行

Question

好的，我有这个代码:

SELECT 
    COUNT(a.auction_id) AS amount, a.owner_id
FROM 
    auctions AS a
    INNER JOIN users AS u ON a.owner_id=u.user_id
    LEFT JOIN winners AS w ON a.auction_id=w.auction_id
WHERE 
    FROM_UNIXTIME(a.start_time) BETWEEN '2013-07-01 00:00:00' AND '2013-07-31 23:59:59' 
AND 
    a.owner_id IN (100012,100027,100030,100063,100065)
AND 
    a.active=1 
AND 
    a.approved=1 
AND 
    a.is_relisted_item=0 
AND 
    a.is_draft=0    
GROUP BY 
    a.owner_id
HAVING 
    amount > 4;

我想做的是类似下面的东西，但我只是不知道如何写..

如果a.list_in = 'store' AND a.close=1，那么它必须出现在获奖者表中才能包含在结果集中。因此，基本上，winners 表中的 w.winner_id 不应该为 NULL，这样才能将其包含为结果。

所以基本上，如果 a.list_in = 'store' 就可以，应该包含在内，但是如果 a.list_in = 'store' AND a.lined=1 那么要包含它，w.winner_id 不能为 NULL。

我该怎么做？

Answer 1

    SELECT 
    COUNT(a.auction_id) AS amount, a.owner_id
FROM 
    auctions AS a
    INNER JOIN users AS u ON a.owner_id=u.user_id
    LEFT JOIN winners AS w ON a.auction_id=w.auction_id
WHERE 
    FROM_UNIXTIME(a.start_time) BETWEEN '2013-07-01 00:00:00' AND '2013-07-31 23:59:59' 
AND 
    a.owner_id IN (100012,100027,100030,100063,100065)
AND 
    a.active=1 
AND 
    a.approved=1 
AND 
    a.is_relisted_item=0 
AND 
    a.is_draft=0 
AND (NOT(a.list_in = 'store' AND a.closed=1 ) OR (w.auction_id IS NOT NULL))   
GROUP BY 
    a.owner_id
HAVING 
    amount > 4;

这应该有效

在where中添加了一个额外的条件

AND (NOT(a.list_in = 'store' AND a.closed=1 ) OR (w.auction_id IS NOT NULL))