mysql - 弥补用原生nativeQuery和MAX(ID)获取2个对象

Question

我有一个名为 Story 的表:

sID |  sName  |  sView  
 1  |   s1    |    1  
 2  |   s2    |    11  
 3  |   s3    |    142 
 4  |   s4    |    152

表格章节:

 chID  |  sID  |  chName  |  chContent
   1   |   1   |    ch1   |    aaa  
   2   |   2   |    ch2   |    aaa  
   3   |   3   |    ch3   |    aaa  
   4   |   1   |    ch4   |    aaa  
   5   |   3   |    ch5   |    aaa  
   6   |   1   |    ch6   |    aaa  
   7   |   2   |    ch7   |    aaa  

Navite查询:

SELECT s.*, MAX(c.chID) as chapterID FROM Story s 
       LEFT JOIN Chapter c ON s.sID = c.sID
       GROUP BY s.sID

结果:

 sID |  sName  |  sView  |  chapterID  
  1  |   s1    |    1    |      6
  2  |   s2    |    11   |      7
  3  |   s3    |    142  |      5
  4  |   s4    |    152  |     null

但是你应该得到一个“SELECT”。 *， C。 * FROM ....'，谁能帮我!!!

Answer 1

您可以用不同的方式编写它。例如，您可以使用它:

SELECT s.*, c.* FROM Story s 
       LEFT JOIN (SELECT * FROM Chapter c INNER JOIN (SELECT MAX(c.chID) as chapterID FROM Story s 
       LEFT JOIN Chapter c ON s.sID = c.sID
       GROUP BY s.sID) d ON c.chID = d.chapterID) c ON s.sID = c.sID