MySQL子查询简单选择问题

Question

日安，

我有以下查询。

SELECT `codeusage_id` FROM `panel_codeusage` 
WHERE `codeusage_exactdate` LIKE '2015-03%'

这就产生了178项结果。

但我想知道哪种代码用法与活动预订相关联。所以我写了这个子查询。

SELECT `codeusage_id` FROM `panel_codeusage` A
WHERE `codeusage_exactdate` LIKE '2015-03%' AND 
(SELECT `booking_cancel` FROM `panel_bookings` B
WHERE  A.`codeusage_link` = B.`booking_id` AND `booking_cancel` = 0)

但这并没有给我任何结果。有人能告诉我我做错了什么吗？

我编辑了这个查询如下：

SELECT `codeusage_id` FROM `panel_codeusage` A
WHERE `codeusage_exactdate` >= '2015-03-01' AND 
       codeusage_exactdate < '2015-04-01'
       EXISTS (SELECT 1
               FROM `panel_bookings` B
               WHERE  A.`codeusage_link` = B.`booking_id` AND
                     `booking_cancel` = 0
              );

还是没有用。

当我直接在订票表上运行一个较小的查询以检查它是否有任何结果时，我会得到168个结果。

SELECT * FROM `panel_bookings` WHERE `booking_code` != '' AND `booking_day` LIKE '2015-03%' AND `booking_cancel` = 0 

所以我还在做错事..。

Answer 1

您有一个返回0的子查询，MySQL将其解释为false。你需要EXISTS

SELECT `codeusage_id` FROM `panel_codeusage` A
WHERE `codeusage_exactdate` >= '2015-03-01' AND 
       codeusage_exactdate < '2015-04-01' AND
       EXISTS (SELECT 1
               FROM `panel_bookings` B
               WHERE  A.`codeusage_link` = B.`booking_id` AND
                     `booking_cancel` = 0
              );

此外，不要将LIKE用于日期。LIKE用于字符串，而MySQL有大量处理日期的函数。