mysql - 选择范围内的值

Question

我正在尝试查找包含大于 7 且小于 13.99 的值的行数。但是，以下查询始终返回 0，这是不可能的。

任何人都可以看到我的查询中存在问题吗？

SELECT COUNT(*) FROM lead_status AS ls 
INNER JOIN leads AS l ON l.id = ls.lead_id 
INNER JOIN contacts AS c ON c.lead_id = l.id 
WHERE ls.discriminator = 'AUTO_POST' AND l.affiliate_id=1003 
AND ls.winner =1 AND l.test =0 AND l.create_date BETWEEN '2011-10-03' AND '2011-10-17' 
AND ls.amount >= 7 AND ls.amount <= 13.99;

Answer 1

一个小的编辑，一些格式和一个怀疑:

SELECT COUNT(*)
FROM   lead_status ls 
INNER  JOIN leads l ON l.id = ls.lead_id    -- ls.id .. l.lead_id ??
INNER  JOIN contacts c ON c.lead_id = l.id 
WHERE  ls.discriminator = 'AUTO_POST'
AND    ls.winner = 1
AND    ls.amount >= 7
AND    ls.amount <  14
AND    l.affiliate_id = 1003 
AND    l.test = 0
AND    l.create_date BETWEEN '2011-10-03' AND '2011-10-17';

您有 ls.lead_id 和 c.lead_id，但有 l.id。这似乎不一致。打字错误？

你写:

greater than 7 and less than 13.99

你的代码说:

greater than or equal to 7 and less than or equal to 13.99

我将其更改为(我怀疑这就是您真正想要的):

greater than or equal to 7 and less than 14