限制 - mysql查询
我想知道如何限制输出的id_offers数量而不是行数。例如限制 - mysql查询
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个报价。
我不知道这是否是你想要什么,但我认为它是:
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
在子查询中给我一个语法错误:SELECT UNIQUE id_offer FROM offer and another issue:这个版本的MySQL还不支持'LIMIT&IN/ALL/ANY/SOME子查询' – anvd 2012-01-11 22:32:25
我忘了MySQL不允许在子查询中使用LIMIT。转换为连接(如我的更新答案)可能会解决问题。并且s/UNIQUE/DISTINCT/ – bhamby 2012-01-11 22:37:26
不幸的是没有工作。它显示所有优惠的标签。 GROUP_CONCAT可能有助于统计id_offers的数量? – anvd 2012-01-11 22:45:58
你可以试试这个:
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
我不知道提供的ID – anvd 2012-01-11 22:13:24
您只需使用DISTINCT:
SELECT DISTINCT A.id_offer,T.tags
从招募一个 INNER JOIN offer_has_tagsž 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
你在某处有需要的独特之处。不太能够跟随你的领域名称和他们的关系,所以我不能提供在哪里。 – lhagemann 2012-01-09 15:41:07
'state'属于哪个表? – 2012-01-11 23:11:57
它属于报价表。谢谢 – anvd 2012-01-11 23:15:42