mysql仅计算连接查询中的不同值

WOW! That's a weird title, but I'm happy you're looking, cause I couldn't find another way to say it.

哇!这是一个奇怪的标题,但我很高兴你看,因为我找不到另一种说法。

I have a table of people and a table of links to photos and videos. I join the people to the media, and of course a person can have more than one piece of media.

我有一张人的桌子和一张照片和视频的链接表。我和人民一起去媒体,当然一个人可以有多个媒体。

I am attempting to grab the first 30 people and all of their media in one query, but when I say LIMIT 0,30. Of course I'll only get the first 30 media files, which could be 10 people.

我试图在一个查询中抓住前30个人及其所有媒体,但是当我说LIMIT 0,30时。当然,我只会得到前30个媒体文件,可能是10个人。

Before I make the query, I don't know how many media files each person is going to have. Is there a way I can say LIMIT DISTINCT(uid 0,30)????

在我进行查询之前,我不知道每个人将拥有多少媒体文件。有没有办法可以说LIMIT DISTINCT(uid 0,30)????

or something like that?

或类似的东西?

2 个解决方案

#1

Can you use subqueries in your version of MySQL?

你可以在你的MySQL版本中使用子查询吗?

select *
from media m
inner join
     ( select uid
     from users_tbl
     limit 0,30) map
  on map.uid = m.uid
inner join users_tbl u
  on u.uid = m.uid

#2

FWIW, here's another query that should return the same result:

FWIW,这是另一个应该返回相同结果的查询:

SELECT *
FROM media m INNER JOIN users_tbl u USING (uid)
WHERE u.uid IN (SELECT uid FROM users_tbl ORDER BY uid LIMIT 0,30);

Note that when you use LIMIT, you should always use an ORDER BY. Otherwise there is no guarantee which thirty users you'll get.

请注意,使用LIMIT时,应始终使用ORDER BY。否则,无法保证您将获得30个用户。

#1