php - 试图合并两个嵌套查询以提高效率

Question

我有两个嵌套查询，第二个在我遍历第一个的结果时运行。我很想用 MySQL 服务器代替 PHP 来完成这项工作，但我不知道如何将这两者结合起来。

第一个查询:

SELECT post_id,
       MAX(CASE WHEN meta_key = 'size' THEN meta_value END) size,
       MAX(CASE WHEN meta_key = 'adlink' THEN meta_value END) adlink,
       MAX(CASE WHEN meta_key = 'frontpage' THEN meta_value END) frontpage,
       MAX(CASE WHEN meta_key = 'expiration' THEN meta_value END) expiration,
       MAX(CASE WHEN meta_key = 'image1' THEN meta_value END) image1,
       MAX(CASE WHEN meta_key = 'image2' THEN meta_value END) image2,
       MAX(CASE WHEN meta_key = 'image3' THEN meta_value END) image3,
       MAX(CASE WHEN meta_key = 'image4' THEN meta_value END) image4,
       MAX(CASE WHEN meta_key = 'iframe' THEN meta_value END) iframe,
       MAX(CASE WHEN meta_key = 'location' THEN meta_value END) location,
       MAX(CASE WHEN meta_key = 'sublocation' THEN meta_value END) sublocation
    FROM wp_postmeta WHERE post_id in 
      (SELECT post_id from wp_postmeta 
       WHERE meta_value LIKE 'Run Of Site') GROUP BY Post_id

然后，遍历结果，我们从另一个表中获取图像，如'image1'等，只是第一组结果中的帖子id号:

SELECT meta_value FROM wp_postmeta 
   WHERE post_id IN
     (SELECT meta_value from wp_postmeta 
      WHERE post_id = " . $row{'post_id'} ." AND meta_key like 'image%')

这是查看 PHP 迭代的完整代码(我知道它需要更好的格式以提高可读性):

$rosads = mysql_query("SELECT post_id,
       MAX(CASE WHEN meta_key = 'size' THEN meta_value END) size,
       MAX(CASE WHEN meta_key = 'adlink' THEN meta_value END) adlink,
       MAX(CASE WHEN meta_key = 'frontpage' THEN meta_value END) frontpage,
       MAX(CASE WHEN meta_key = 'expiration' THEN meta_value END) expiration,
       MAX(CASE WHEN meta_key = 'image1' THEN meta_value END) image1,
       MAX(CASE WHEN meta_key = 'image2' THEN meta_value END) image2,
       MAX(CASE WHEN meta_key = 'image1' THEN meta_value END) image3,
       MAX(CASE WHEN meta_key = 'image2' THEN meta_value END) image4,
       MAX(CASE WHEN meta_key = 'iframe' THEN meta_value END) iframe,
       MAX(CASE WHEN meta_key = 'location' THEN meta_value END) location,
       MAX(CASE WHEN meta_key = 'sublocation' THEN meta_value END) sublocation
    FROM wp_postmeta WHERE post_id in (SELECT post_id from wp_postmeta WHERE meta_value LIKE 'Run Of Site') GROUP BY Post_id");
if (!$rosads) { // add this check.
    die('Invalid query: ' . mysql_error());
}

    while ($row = mysql_fetch_array($rosads)) {   // #########  ITERATE THROUGH THE LIST

        if ($row{'size'} == "Premium"){
            if ($row{'iframe'}){
                $premium = $premium . '<div name="PREMIUM'.$row{'post_id'}.'" style="float:left;">'.$row{'iframe'}.'</div>'."\n";
            }else{
                $images = mysql_query("SELECT meta_value FROM wp_postmeta where post_id IN(SELECT meta_value from wp_postmeta WHERE post_id = " . $row{'post_id'} ." AND meta_key like 'image%')");
                $premium = $premium . "<div name=\"".$row{'post_id'}."\" class=\"multipleslides\" >";
                while ($row2 = mysql_fetch_array($images)) {
                    $premium = $premium . "\t<a target=\"_blank\" href=\"" . $row{'adlink'} ."\"><img src=\"/wp-content/uploads/" .$row2{'meta_value'} ."\"></a>\r\n";
                }
                $premium = $premium . "</div><br>";
            }
        /* ### TEST STUFF ####
        echo $row{'post_id'} . ", " . $row{'size'} . ", " . $row{'adlink'} . ", " . $row{'frontpage'} . ", " . $row{'expiration'} . ", " . $row{'image1'} . ", " . $row{'image2'} . ", " . $row{'image3'} . ", " . $row{'image4'} . ", " . 1$row{'location'} . ", " . $row{'sublocation'} . ", " . $row{'iframe'} . "<hr>";
        */
        } // ###### END THIS ITERATION ####

Answer 1

您也可以将查询用作列，但为了安全起见，请确保您的子查询应该返回一条记录我在子查询中添加了 LIMIT 1 或者如果有超过 1 个结果，您可以删除限制并可以使用 GROUP_CONCAT(meta_value) 但注意它的默认限制为 1024 个字符

SELECT wpm.post_id,
       MAX(CASE WHEN wpm.meta_key = 'size' THEN wpm.meta_value END) size,
       MAX(CASE WHEN wpm.meta_key = 'adlink' THEN wpm.meta_value END) adlink,
       MAX(CASE WHEN wpm.meta_key = 'frontpage' THEN wpm.meta_value END) frontpage,
       MAX(CASE WHEN wpm.meta_key = 'expiration' THEN wpm.meta_value END) expiration,
       MAX(CASE WHEN wpm.meta_key = 'image1' THEN wpm.meta_value END) image1,
       MAX(CASE WHEN wpm.meta_key = 'image2' THEN wpm.meta_value END) image2,
       MAX(CASE WHEN wpm.meta_key = 'image3' THEN wpm.meta_value END) image3,
       MAX(CASE WHEN wpm.meta_key = 'image4' THEN wpm.meta_value END) image4,
       MAX(CASE WHEN wpm.meta_key = 'iframe' THEN wpm.meta_value END) iframe,
       MAX(CASE WHEN wpm.meta_key = 'location' THEN wpm.meta_value END) location,
       MAX(CASE WHEN wpm.meta_key = 'sublocation' THEN wpm.meta_value END) sublocation,
(
SELECT 
  meta_value 
FROM
  wp_postmeta 
WHERE post_id IN 
  (SELECT 
    meta_value 
  FROM
    wp_postmeta 
  WHERE post_id = wpm. post_id
    AND meta_key LIKE 'image%')
LIMIT 1) images       

    FROM wp_postmeta wpm WHERE wpm.post_id IN 
      (SELECT post_id FROM wp_postmeta 
       WHERE meta_value LIKE 'Run Of Site') GROUP BY wpm.post_id

对于 group_concat()

(
SELECT 
 GROUP_CONCAT(meta_value)
FROM
  wp_postmeta 
WHERE post_id IN 
  (SELECT 
    meta_value 
  FROM
    wp_postmeta 
  WHERE post_id = wpm. post_id
    AND meta_key LIKE 'image%')
) images