返回空结果集的 MySql 查询

Question

您好，我有以下两个表:

设备表:

deviceID    accountID  InstallTime    fName         lName   
1            2          NULL          Dave          O'Riordan   
2            2          NULL          Kathrine      Hutter  
3            2          NULL          Guy           Swider

a2_deal 表:

deviceID  companyID  contactID  stage   event   state   serverTime  clientTime  monetary
1            1          1        -1     CALL    OUT     1349445600  1349445600  4600
1           14         12        -1     CALL    OUT     1349449200 1349449200   5400
2           16         19        -1     CALL    OUT     1349445600 1349445600   4456
2           21         20        -1     CALL    OUT     1349449200 1349449200   4000
3           17         22        -1     CALL    OUT     1349447160 1349447160   12000
1            1          1         1     CALL    OUT     1349082060 1349082060   NULL
1           14         12         1     CHECK   IN      1349083380 1349083380   NULL
1            1          1         1     CHECK   IN      1349082660 1349082660   NULL
1           14         12         1     CHECK   OUT     1349085382 1349085382   NULL
1            1          1         1     CHECK   OUT     1349082700 1349082700   NULL

我做了如下查询:

SELECT deal.deviceID, devices.fName, devices.lName, SUM(event) AS visits
 FROM `a2_deal` AS deal
 LEFT JOIN `devices` AS devices ON deal.deviceID = devices.deviceID
 WHERE `event` LIKE 'CHECK' 
 AND `serverTime` >= 1349082060
 AND `serverTime` <= 1349190000
 GROUP BY deal.deviceID
 ORDER BY visits

它运行良好并给我结果....但是，当我尝试根据条件从 a2_deal 表中的 stage 列检索数据时，它没有给我任何结果(空结果集)...

SELECT deal.deviceID, devices.fName, devices.lName, SUM(stage) AS closes
FROM `a2_deal` AS deal
LEFT JOIN `devices` AS devices ON deal.deviceID = devices.deviceID
WHERE `stage` =  -1
AND `serverTime` >= 1349082060
AND `serverTime` <= 1349190000
GROUP BY deal.deviceID
ORDER BY closes

任何建议我做错了什么......谢谢你

Answer 1

查询

WHERE `stage` =  -1
AND `serverTime` >= 1349082060
AND `serverTime` <= 1349190000

没有返回任何结果，因为所有值都带有 stage = -1有超出定义范围的时间。

附带说明，您可以使用 serverTime BETWEEN <start> AND <end> .