limit - mysql查询

Question

我想知道如何通过id_offers的数量而不是行数来限制输出。例如

 SELECT  A.id_offer, T.tags  
            FROM offer A
            INNER JOIN offer_has_tags Z
            ON A.id_offer = Z.offer_id_offer

            INNER JOIN tags T
            ON Z.tags_id_tags = T.id_tags

            WHERE state = 0 
            ORDER BY date
            DESC LIMIT 0, 10

输出：

  id_offer  tags
    77       xx 
    76       xx 
    76       xx 
    75       xx 
    75       xx 
    74       xx 
    74       xx
    73       xx
    73       xx
    72       xx

编辑:在这种情况下，仅应计为6个优惠。

Answer 1

我不确定这是否是你想要的，但我认为是：

SELECT  A.id_offer, T.tags  
FROM offer A
JOIN offer_has_tags Z
  ON A.id_offer = Z.offer_id_offer
JOIN tags T
  ON Z.tags_id_tags = T.id_tags
JOIN (
    SELECT DISTINCT id_offer
    FROM offer
    WHERE state = 0
    ORDER BY date DESC
    LIMIT 10
) L
  ON A.id_offer = L.id_offer

或者更简单的：

SELECT  A.id_offer, T.tags  
FROM 
  ( SELECT *
    FROM offer
    WHERE state = 0
    ORDER BY date DESC
    LIMIT 10
  ) A
JOIN offer_has_tags Z
  ON A.id_offer = Z.offer_id_offer
JOIN tags T
  ON Z.tags_id_tags = T.id_tags

Answer 2

您可以尝试这样做：

SELECT  A.id_offer, T.tags  
FROM offer A
    INNER JOIN offer_has_tags Z
        ON A.id_offer = Z.offer_id_offer
    INNER JOIN tags T
        ON Z.tags_id_tags = T.id_tags

WHERE (state = 0) AND
      (A.id_offer >= 72 AND A.id_offer <= 77)
ORDER BY date

Answer 3

您只需使用DISTINCT：

选择DISTINCT A.id_offer，T.tags

         FROM offer A             INNER JOIN offer\_has\_tags Z             ON A.id\_offer = Z.offer\_id\_offer

        INNER JOIN tags T
        ON Z.tags_id_tags = T.id_tags

        WHERE state = 0 
        ORDER BY date
        DESC LIMIT 0, 10