返回空的MySQL查询

Question

我有两张桌子--帖子和后置。

posts
ID   title   category  post_status  post_type    

1    ABC      cat-1    Publish      Store
2    DEF      cat-2    Publish      Store
3    GHI      cat-3    Publish      Store
4    JKL      cat-2    Publish      Store
5    XYZ      cat-5    Draft        Store
6    MNO      cat-9    Publish      Article

和

postmeta
meta_id post_id  meta_key    meta_value

109       1       city          1
110       1       featured      h
111       2       city          1,2
112       2       featured      both
113       3       city          2,3
114       3       featured      both
115       4       city          1
116       4       featured      n
117       5       city          1,4
118       5       featured      h 
119       6       city          1
120       6       featured      h

我正在尝试运行一个查询，该查询将给出具有以下条件的帖子列表：

1. 其相对于城市的价值有1
2. 它的特征值要么是h，要么两者都是
3. 其职位状态是发布和
4. 其post类型为Store
5. 按头衔订购

我正在尝试的查询是

SELECT DISTINCT posts.ID , posts.*, postmeta.*
            FROM posts, postmeta
            WHERE posts.ID = postmeta.post_id
            AND (postmeta.meta_value  = 'h' OR postmeta.meta_value = 'both') 

AND (postmeta.meta_key = 'post_city_id' AND (postmeta.meta_value LIKE '%,1,%' OR postmeta.meta_value LIKE '%1,%'  OR postmeta.meta_value LIKE '%,1%' OR postmeta.meta_value LIKE '%1%'))

            AND posts.post_status = 'Publish' 
            AND posts.post_type = 'Store'

            ORDER BY (SELECT postmeta.meta_value from postmeta where (posts.ID = postmeta.post_id) and postmeta.meta_key LIKE '%home_featured_type%') asc, posts.post_title LIMIT 0,6

正确的返回将是ID 1和2，即abc和def。但我得到的是空洞的结果。我不知道它在哪里散架。怎么解决这个问题呢？

Answer 1

这里是一个固定的查询，但我仍然不明白神秘的ORDER BY (SELECT)思想。

http://sqlfiddle.com/#!2/5ce5a/19

SELECT DISTINCT posts.ID , posts.*, postmeta_city.*, postmeta_featured.*

FROM       posts

INNER JOIN postmeta AS postmeta_city
     ON     postmeta_city.post_id = posts.ID
    AND     postmeta_city.meta_key = 'city'
    AND (   postmeta_city.meta_value LIKE '%,1,%'
         OR postmeta_city.meta_value LIKE '%1,%'
         OR postmeta_city.meta_value LIKE '%,1%'
         OR postmeta_city.meta_value LIKE '%1%'
        )
INNER JOIN postmeta AS postmeta_featured
     ON     postmeta_featured.post_id = posts.ID
    AND     postmeta_featured.meta_key = 'featured'
    AND (   postmeta_featured.meta_value = 'h'
         OR postmeta_featured.meta_value = 'both'
        )

WHERE posts.post_status = 'Publish' 
  AND posts.post_type = 'Store'

ORDER BY (
    SELECT postmeta.meta_value
    FROM postmeta
    WHERE ( posts.ID = postmeta.post_id )
      AND postmeta.meta_key LIKE '%home_featured_type%'
  ) asc,
  posts.title

LIMIT 0,6;
;

请根据其他人的想法更新意见：

http://sqlfiddle.com/#!2/5ce5a/33

SELECT DISTINCT posts.ID , posts.*, postmeta_city.*, postmeta_featured.*

FROM       posts

INNER JOIN postmeta AS postmeta_city
     ON postmeta_city.post_id = posts.ID
    AND postmeta_city.meta_key = 'city'
    AND FIND_IN_SET('1', postmeta_city.meta_value)

INNER JOIN postmeta AS postmeta_featured
     ON postmeta_featured.post_id = posts.ID
    AND postmeta_featured.meta_key = 'featured'
    AND postmeta_featured.meta_value IN ('h','both')

WHERE posts.post_status = 'Publish' 
  AND posts.post_type = 'Store'

ORDER BY (
    SELECT postmeta.meta_value
    FROM postmeta
    WHERE ( posts.ID = postmeta.post_id )
      AND postmeta.meta_key LIKE '%home_featured_type%'
  ) asc,
  posts.title

LIMIT 0,6;
;

Answer 2

您得到了一个空的结果集，因为您正在AND meta_value列，所以它必须同时等于两个值，这是不可能的。类似于val = '1' AND val = 'both'的东西总是返回false，所有行都不会加入。相反，您必须在两个条件之间使用一个OR：-> 1和-> h/二者。

由于post必须同时包含城市-> 1和特色-> h/二者(它们不是跨列而是跨多行)，因此需要一个HAVING子句和一个GROUP BY，以确保每个post连接有两行，同时满足这两种条件.不是一个也不是那个。

此外，这也是大量的LIKE检查是否存在1。您可以使用设置来代替：

SELECT
    *
FROM
    posts a
INNER JOIN
    postmeta b ON a.ID = b.post_id
    AND
    (
        (b.meta_key = 'city' AND FIND_IN_SET('1', b.meta_value) > 0)
        OR  
        (b.meta_key = 'featured' AND b.meta_value IN ('h', 'both'))
    )
WHERE
    a.post_status = 'Publish'
    AND a.post_type = 'Store'
GROUP BY
    a.ID
HAVING
    COUNT(*) = 2
ORDER BY
    a.title

Answer 3

试试这个：

SELECT DISTINCT posts.ID , posts.*, postmeta.*
            FROM posts AS p INNER JOIN postmeta AS pm
            WHERE p.ID = pm.post_id
            AND (pm.meta_value  in ('h','both') 

AND (pm.meta_key = 'city' AND 
(pm.meta_value LIKE '%,1,%' OR pm.meta_value LIKE '%1,%'  OR pm.meta_value LIKE '%,1%' OR pm.meta_value LIKE '%1%'))

            AND posts.post_status = 'Publish' 
            AND posts.post_type = 'Store'

            ORDER BY p.title LIMIT 0,6

因为您只希望按标题排序，所以不需要在“order”子句中编写任何查询。