诸如此类的事

SELECT * FROM A
      INNER JOIN ( SELECT * FROM A WHERE A.FIELD1='X' ORDER BY A.FIELD2 LIMIT 10) X
             ON (A.KEYFIELD=X.KEYFIELD)
      LEFT JOIN B ON (A.FIELD = B.FIELD)
      LEFT JOIN C ON (A.FIELD = C.FIELD)

试试这个：

SELECT g.gemid, g.title, r.tot, gemdetail.filename
FROM (SELECT * FROM gems WHERE grade = '7' LIMIT 10) g
LEFT JOIN (SELECT gemid, COUNT(*) AS tot FROM gemreply GROUP BY gemid) r
          ON r.gemid = g.gemid
LEFT JOIN gemdetail ON g.gemid = gemdetail.gemid
LEFT JOIN users ON g.userid = users.userid
ORDER BY g.gemid;

这应该行得通。

WITH LAST_ORDERS AS(SELECT*FROM ORDERS WHERE ID IN(SELECT MAX(ID)FOR ORDERS GROUP BY CUSTOMER_ID))SELECT Customers.id，stomers.first_name，Customers.last_name，last_orders.order_date，last_orders.Order_Status from Customers Join Last_Orders on Customers.id=last_orders.Customer_id Order by Customer_id；

十年后，我可以在一对多JOIN的左侧表上的嵌套查询中LIMIT记录，其中左侧表将返回多条记录--即使连接的右侧表上的字段上有WHERE或ORDER BY子句。

• 在内部查询中，具有满足WHERE子句所需的具有联接的所有表。在那里应用限制。
• 在外部查询中，重新连接到所有相同的表以添加多对多字段

SELECT
    alias.field_from_A,
    alias.field_from_B
FROM (
    SELECT DISTINCT
     /* ONLY fields from LEFT table (or tables
        with a one to one relationship with it) will
        allow LIMIT to apply only to the LEFT table. DISTINCT is needed due to JOIN*/
      A.field_from_A
    FROM
      A
      /* JOIN to your hearts content on tables
         which need to satisfy WHERE and ORDER BY */
      LEFT JOIN B ON A.field_from_A = B.field_from_B
      WHERE A.field_from_A = 'value' AND B.field_from_B = 'value 2'
      ORDER BY A.field_from_A, B.field_from_B
      LIMIT 0, 10
    ) alias
/* JOIN the one to many tables here to add the field info */
LEFT JOIN B ON A.field_from_A = B.field_from_B
/* the WHERE and ORDER BY need to be applied again */
WHERE alias.field_from_A = 'value' AND B.field_from_B = 'value 2'
ORDER BY alias.field_from_A, B.field_from_B