mysql - sql查询似乎没有返回结果

Question

某些在 mysql 命令行客户端中工作正常的查询似乎没有返回phpMyAdmin 中没有任何内容(没有错误，没有结果)。我的印象是它与嵌套查询有关。这是一个例子:

select * from  
  (select  
   (select min(data) from census2 where  
    census2.monkey=samplecollection.monkeyid and  
    date(collectiontime)=date(census2.timestamp)) census  
 from samplecollection,biograph,hormone,plate  
 where plate.hormone='Testosterone' and hormone.plateid=plate.plateid and  
 not specialcontentcode like '%UR%' and thermos!='F' and  
 biograph.id=monkeyid and samplecollection.sampleid=hormone.sampleid)  
 t1 limit 3; 
+--------+ 
| census | 
+--------+ 
| GFF    |    
| GRF    |    
| GRF    |    
+--------+   
3 rows in set (5.09 sec) 

如果我提取内部查询(并对其施加限制)，那么我会得到结果。

Answer 1

您的查询结构如果过于复杂且明显未优化，则可能会导致您遇到的问题。

这是经过一些重构的相同查询:

SELECT *
FROM samplecollection SC
INNER JOIN (SELECT C2.monkeyid
                 ,MIN(C2.data) AS [census]
            FROM census2 C2
            INNER JOIN samplecollection SC2 ON SC2.monkeyid = C2.monkey
                                              AND DATE(SC2.collectiontime) = DATE(C2.timestamp)
                                              AND SC2.thermos != 'F'
                                              AND SC2.specialcontentcode NOT LIKE '%UR%'
            GROUP BY C2.monkeyid) T ON T.monkeyid = SC.monkeyid
INNER JOIN biograph B ON B.id = SC.monkeyid
INNER JOIN hormone H ON H.sampleid = SC.sampleid
INNER JOIN plate P ON P.plateid = H.plateid
                     AND P.hormone = 'Testosterone'
LIMIT 3

答案来晚了，但对于某些人来说，了解如何在使用 JOIN 子句时简化一些非常复杂的查询结构可能会很有用。

希望这会有所帮助。