php - MySQL 来自两个高级表的请求

Question

我有来自 MySQL 表的请求。如下所示:

SELECT s . * , if( v.ip IS NULL , 0, 1 ) AS voted, v.vote
FROM `emvc4_records` s
LEFT JOIN `emvc4_records_votes` v ON ( s.id = v.record_id
AND v.day = CURRENT_DATE
AND v.ip = '2130706433' )
WHERE 1 =1
AND `category` =2
AND `caption` LIKE '%My%'
OR `description` LIKE '%My%'
ORDER BY `rating` DESC
LIMIT 0 , 25

我得到这个结果:

id  category    category_name   caption                 description         created                 postby  votes_up    votes_down  rating  status  have_voted  vote
5   1                           My second idea          My second idea      2013-06-04 00:00:00     102     2           0           2       0       0           NULL
7   2                           My first question       My first question   2013-06-04 00:00:00     102     2           0           2       0       0           NULL
9   2                           My second               My second question  2013-06-04 00:00:00     102     1           0           1       0       0           NULL

我的问题:在我的 sql 查询中，我有:“AND category =2”。那么为什么我在输出中得到记录 id=5(它的类别= 1)？

谢谢!

Answer 1

WHERE 子句中的条件组织不正确。添加括号以对所需的条件进行分组。我想 LIKE '%My%' 应该分组。

SELECT s . *
    , if( v.ip IS NULL , 0, 1 ) AS voted
    , v.vote
    FROM `emvc4_records` s
    LEFT JOIN `emvc4_records_votes` v ON ( s.id = v.record_id
        AND v.day = CURRENT_DATE
        AND v.ip = '2130706433' )
    WHERE 1 = 1
        AND `category` = 2
        AND (`caption` LIKE '%My%' OR `description` LIKE '%My%')
    ORDER BY `rating` DESC
    LIMIT 0 , 25

我不明白您为什么添加 1 = 1，但您可以将其删除。