限制 - 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：

SELECT DISTINCT A.id_offer, T.tags
来自报价 A
内连接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