选择重复SITE_ID的
问题描述:
SELECT
deal.*,
site.name AS site_name
FROM deal
INNER JOIN site ON site.id = site_id
WHERE site_id IN (2, 3, 4, 5, 6)
GROUP BY deal.id DESC, site_id
LIMIT 5
这个查询的伟大工程,除了它的拉动重复site_ids ....选择重复SITE_ID的
我一直没能解决这个问题,这个查询无法正常工作或:
SELECT DISTINCT
site.name AS site_name,
site.woot_off,
woot_deal.*
FROM site
INNER JOIN woot_deal ON woot_deal.site_id = site.id
WHERE site_id IN (2, 3, 4, 5, 6)
GROUP BY woot_deal.id DESC, site_id
LIMIT 5
每个网站都有很多交易,但我只想要最新的每笔交易。此查询正在拉动属于同一站点的5个角色中的3个。
答
select
deal.*,
site.name AS site_name
from (
SELECT max(deal.id) as deal_id, site_id
FROM deal
INNER JOIN site ON site.id = deal.site_id
WHERE site.id IN (2, 3, 4, 5, 6)
GROUP BY site.id
) last_deals
inner join site on site.id = last_deals.site_id
inner join deal on deal.id = last_deals.deal_id
LIMIT 5
答
上一个答案似乎是通过与子查询创建匿名表复杂得多。除非我错过了一个简单的功能组应该做的窍门:
mysql> select * from site;
+----+------------+
| id | name |
+----+------------+
| 1 | lameone |
| 2 | anotherone |
+----+------------+
2 rows in set (0.00 sec)
mysql> select * from deal;
+----+---------+-------+-------+
| id | site_id | name | value |
+----+---------+-------+-------+
| 1 | 1 | best | 10 |
| 2 | 1 | worst | 1 |
| 3 | 2 | best | 10 |
| 4 | 2 | worst | 1 |
+----+---------+-------+-------+
4 rows in set (0.00 sec)
mysql>\p
--------------
select s.name site
, d.name
, max(d.value) value
from deal d
, site s
where s.id = d.site_id
group by s.id
order by 1,2,3
--------------
-> \g
+------------+------+-------+
| site | name | value |
+------------+------+-------+
| anotherone | best | 10 |
| lameone | best | 10 |
+------------+------+-------+
2 rows in set (0.00 sec)
你的表结构的一些信息将有助于? – amitchd 2011-02-27 05:35:22
我假设您的网站和交易之间的关系将是一对多。在这种情况下,它是完美的,它会得到重复的site_ids。 – amitchd 2011-02-27 05:36:14
这意味着每个网站都必须有多笔交易,如果是这种情况,并且您只想要返回一笔交易,则需要提供一些方法告知您要返回的DB _which_交易,无论是在何处或有条款 – tobyodavies 2011-02-27 05:41:54