一千萬個為什麽

搜索

MySQL - 在帶有WHERE子句的子查詢中使用別名

我有一種感覺,我完全把這個MySQL查詢搞砸了,但無論如何我都會問。我想知道為什麽我得到警告 Unknown column'FOO',以及當我運行以下命令時,如何在'where子句'中使這個查詢正常工作:

SELECT sample_id AS FOO 
FROM   tbl_test 
WHERE  sample_id = 521 
       AND sample_id IN (SELECT sample_id 
                         FROM   tbl_test 
                         WHERE  sample_id = FOO 
                         GROUP  BY sample_id) 

Edit This query works fine on a different server and fails as described above on the new server. The old one was v 5.0.45 and the new one is 5.0.75.

最佳答案

SELECT sample_id 
FROM   tbl_test outter 
WHERE  sample_id = 521 
       AND sample_id IN (SELECT sample_id 
                         FROM   tbl_test 
                         WHERE  sample_id = outter.sample_id 
                         GROUP  BY sample_id) 

轉載註明原文: MySQL - 在帶有WHERE子句的子查詢中使用別名