php - 连接两个表后打印标题和计数 ID 的 SQL 语句

Question

SELECT b.post_title
     , a.post_id
     , Deriv1.Count
FROM  wp_posts b
    , wp_postmeta a
LEFT OUTER JOIN (    
   SELECT meta_value, COUNT( * ) AS Count
   FROM  wp_postmeta
   GROUP BY meta_value
   ) Deriv1 
ON a.post_id = Deriv1.meta_value
WHERE a.meta_value = 1
  AND a.meta_key = 'type-select' 
  AND b.post_status = 'publish'
  and post_type = 'car-cc'

SQl STATEMENT当前的问题当我提供meta_value =1时它正在获取所有值，即 post_id其中没有 meta_value =1也已列出。

http://sqlfiddle.com/#!2/1115a7/1

Answer 1

我认为问题是您没有加入 wp_posts 表和 wp_postmeta 表。将其添加到您的 WHERE 子句中:

AND a.post_id = b.ID

或者，只需在两个表上执行 INNER JOIN:

SELECT b.post_title
     , a.post_id
     , Deriv1.Count
FROM  wp_posts b INNER JOIN 
        wp_postmeta a ON a.post_id = b.ID
LEFT OUTER JOIN (    
   SELECT meta_value, COUNT( * ) AS Count
   FROM  wp_postmeta
   GROUP BY meta_value
   ) Deriv1 
ON a.post_id = Deriv1.meta_value
WHERE a.meta_value = 1
  AND a.meta_key = 'type-select' 
  AND b.post_status = 'publish'
  and post_type = 'car-cc'

顺便说一句——您想要每个组的计数吗？如果是这样，您的查询可以简化:

SELECT b.post_title, a.post_id, COUNT( * ) as Total
FROM  wp_posts b INNER JOIN 
            wp_postmeta a ON a.post_id = b.ID
    WHERE a.meta_value = 1
      AND a.meta_key = 'type-select' 
      AND b.post_status = 'publish'
      and post_type = 'car-cc'
GROUP BY b.post_title, a.post_id

这是一些更新的fiddle 。不确定您想要的输出。

祝你好运。