sql - MySQL/SQL - 子查询的结果何时可用？

Question

假设我有这个查询

SELECT * FROM (
 SELECT * FROM table_a   
   WHERE id > 10 )
  AS a_results LEFT JOIN 
   (SELECT * from table_b
      WHERE id IN 
       (SElECT id FROM a_results)

  ON (a_results.id = b_results.id)

我会收到错误“a_results 不是表”。我可以在任何地方使用重用子查询的结果吗？

编辑:已经注意到这个查询没有意义......它没有，是的。这只是为了说明我所问的问题； “真正的”查询实际上看起来像这样:

      SELECT SQL_CALC_FOUND_ROWS * FROM
            (  SELECT wp_pod_tbl_hotel . *
            FROM wp_pod_tbl_hotel, wp_pod_rel, wp_pod
            WHERE wp_pod_rel.field_id =12
            AND wp_pod_rel.tbl_row_id =1
            AND wp_pod.tbl_row_id = wp_pod_tbl_hotel.id
            AND wp_pod_rel.pod_id = wp_pod.id
        )  as
              found_hotel LEFT JOIN (

    SELECT COUNT(*) as review_count, avg( (
                    location_rating + staff_performance_rating + condition_rating + room_comfort_rating + food_rating + value_rating
                                        ) /6 ) AS average_score, hotelid
                                        FROM (

                                            SELECT r. * , wp_pod_rel.tbl_row_id AS hotelid
                                                FROM wp_pod_tbl_review r, wp_pod_rel, wp_pod
                                                WHERE wp_pod_rel.field_id =11
                                                AND wp_pod_rel.pod_id = wp_pod.id
                                                AND r.id = wp_pod.tbl_row_id
                                                AND wp_pod_rel.tbl_row_id
                                                IN (                                                

SELECT wp_pod_tbl_hotel .id
            FROM wp_pod_tbl_hotel, wp_pod_rel, wp_pod
            WHERE wp_pod_rel.field_id =12
            AND wp_pod_rel.tbl_row_id =1
            AND wp_pod.tbl_row_id = wp_pod_tbl_hotel.id
            AND wp_pod_rel.pod_id = wp_pod.id

                                                    )
      ) AS hotel_reviews
           GROUP BY hotel_reviews.hotelid
          ORDER BY average_score DESC
                 AS sorted_hotel ON (id = sorted_hotel.hotelid)

正如您所看到的，组成found_query表的子查询在其他地方向下重复作为另一个子查询，所以我希望重新使用结果

Answer 1

您不能使用这样的子查询。

我不确定我是否理解您的查询，但这还不够吗？

SELECT * FROM table_a a
LEFT JOIN table_b b ON ( b.id = a.id )
WHERE a.id > 10

它将返回 table_a 中 id > 10 中的所有行以及 table_b 中 LEFT JOIN 行，其中 id 匹配。