具有内部连接和限制的mysql子查询

pgccezyw  于 2021-06-20  发布在  Mysql
关注(0)|答案(2)|浏览(347)

我有两张table recipes_sa 带列:

recipes_id   recipes_name   recipes_chef
----------   ------------   ------------

以及 chefs_sa 带列:

chefs_id   chefs_name
--------   ----------

我想用他们的厨师的细节,用 INNER JOIN 以及 LIMIT 我做了以下功能:

function getLimitJoinData($data, $tbls, $ids, $abr, $type, $limit) {

            $dataToSelect = implode($data, ',');

            $q = "SELECT $dataToSelect";

            $q.= " FROM (SELECT * FROM $tbls[0] LIMIT $limit) $abr";

            for ($i=1; $i < count($tbls); $i++) { 
                $q .= " ".$type." JOIN ". $tbls[$i] ." ON " . $abr.'.recipes_chef' .' = '. $ids[$i-1][0];   
            }
        }

查询是这样的

SELECT chefs_sa.chefs_name,
       recipes_sa.recipes_name 
FROM (SELECT * FROM recipes_sa LIMIT 8) rec 
INNER JOIN chefs_sa ON rec.recipes_chef = chefs_sa.chefs_id

但是当我运行查询时,我得到了以下警告:
警告:pdo::query():sqlstate[42s22]:找不到列:1054未知列'recipes\u sa.recipes\u name'我不明白为什么
我有专栏 recipes_namerecipes_sa 表,并且从我读到的数据库首先运行“内部查询”(有限制的查询),然后是如何找不到recipes\u name列!!

wfypjpf4

wfypjpf41#

另一种方法是按配方排序,然后限制到最新的8,而不是使用子查询:

SELECT cs.chefs_name, rs.recipes_name 
FROM recipes_sa rs
INNER JOIN chefs_sa cs ON rs.recipes_chef = cs.chefs_id 
ORDER BY rs.recipes_name ASC LIMIT 8
hs1rzwqc

hs1rzwqc2#

你有化名 recipes_sa 作为 rec . 使用以下选项:

SELECT chefs_sa.chefs_name,
       rec.recipes_name 
FROM (SELECT * FROM recipes_sa LIMIT 8) rec 
INNER JOIN chefs_sa ON rec.recipes_chef = chefs_sa.chefs_id

相关问题