mysql - 为什么我的查询不返回任何行

Question

结构表展示here

如果我使用查询:

select idn
from balans as outerB WHERE idn!='' group by idn order by 
ifnull((select sum(innerB.amount) from balans as innerB 
   where innerB.idn = outerB.idn 
   and status='up'), 0) -
ifnull((select sum(innerB.amount) from balans as innerB 
   where innerB.idn = outerB.idn 
   and status='down'), 0) desc
limit 15

我得到 2 行。

但是如果我添加条件 >0 :

select idn from Balans as outerB WHERE idn!='' AND
(
(select sum(innerB.amount) from Balans as innerB 
   where innerB.idn = outerB.idn 
   and type='up') -
(select sum(innerB.amount) from Balans as innerB 
   where innerB.idn = outerB.idn 
   and type='down')
) > 0
group by idn order by 
ifnull((select sum(innerB.amount) from Balans as innerB 
   where innerB.idn = outerB.idn 
   and type='up'), 0) -
ifnull((select sum(innerB.amount) from Balans as innerB 
   where innerB.idn = outerB.idn 
   and type='down'), 0) DESC
limit 15

结果我得到 0 行...

请告诉我错误在哪里？为什么我得到 0 行？

Answer 1

您没有在 WHERE 条件中包含 IFNULL()。这就是您获得 0 行而不是 2 行的原因。子查询返回的总和为 NULL(这就是 SUM() 在没有匹配行时返回的内容，尽管人们可能认为 0 更符合逻辑，但它返回 NULL。)

这个添加的条件，我会把它放在 HAVING 子句中，而不是 WHERE (在这种情况下对结果没有影响，但它可能是更高效):

select idn from Balans as outerB WHERE idn !=''
group by idn 
HAVING
ifnull((select sum(innerB.amount) from Balans as innerB 
   where innerB.idn = outerB.idn 
   and type='up'), 0) -
ifnull((select sum(innerB.amount) from Balans as innerB 
   where innerB.idn = outerB.idn 
   and type='down'), 0) > 0
order by 
ifnull((select sum(innerB.amount) from Balans as innerB 
   where innerB.idn = outerB.idn 
   and type='up'), 0) -
ifnull((select sum(innerB.amount) from Balans as innerB 
   where innerB.idn = outerB.idn 
   and type='down'), 0) DESC
limit 15 ;

---

我看不出有什么理由用相关的子查询来编写这个查询。您可以使用派生表和简单连接(还将 type 更正为 status):

SELECT di.idn
FROM 
    ( SELECT idn
      FROM Balans
      GROUP BY idn
      HAVING idn > ''
    ) AS di
  LEFT JOIN
    ( SELECT idn, SUM(amount) AS up
      FROM Balans
      WHERE status = 'up'
      GROUP BY idn
    ) AS bu
      ON bu.idn = di.idn
  LEFT JOIN
    ( SELECT idn, SUM(amount) AS down
      FROM Balans
      WHERE status = 'down'
      GROUP BY idn
    ) AS bd 
      ON bd.idn = di.idn 
WHERE COALESCE(bu.up, 0) - COALESCE(bd.down, 0) > 0 ;

在 SQL-Fiddle 测试