Question

我的一张主片,一是从一伙中选择的子里挑选的,一是一张桌子。例:

person         skill           person_to_skill
id  | name     id | skill      id | p_id | s_id
------------   ------------    ----------------
1   | jim      1  | sewing     1  | 1    | 2
2   | peter    2  | cooking    2  | 2    | 1
3   | susan    3  | singing    3  | 2    | 3
4   | kevin                    4  | 3    | 1
                               5  | 3    | 2
                               6  | 4    | 3

So now we see, sim has only one skill, peter has two and so forth. Now if i select from person, koin skill and then also join person_to_skill, but i only want two persons. How do i manage to do so without grouping and thereby not getting all the skills?

简言之,我要从具有所有技能的“人”中挑选两人。

I tried just using LIMIT but that limits the result rows, not the persons. If i use GROUP BY i only get one skill per person. Is this possible without a subselect?

是否有任何想法?

My Approach so far, changed to work with the example, looks like this:

SELECT p.id,p.name,s.skill
FROM person AS p
LEFT JOIN person_to_skill psk ON (psk.p_id = p.id)
LEFT JOIN skill s ON (s.id = psk.s_id)
ORDER BY p.name
LIMIT 0,2

Answer 1

Limit number of persons at very beginning in subquery then join to them other tables as you ve already done:

SELECT p.id,p.name,s.skill
FROM (select * from person ORDER BY name LIMIT 0,2) AS p
LEFT JOIN person_to_skill psk ON (psk.p_id = p.id)
LEFT JOIN skill s ON (s.id = psk.s_id)

。

If you really can t use subqueries you can do it using two queries. Firstly select users ids:

select id from person ORDER BY name LIMIT 0,2

并且随后在接下来的询问中使用这些孩子:

SELECT p.id,p.name,s.skill
FROM person p
LEFT JOIN person_to_skill psk ON (psk.p_id = p.id)
LEFT JOIN skill s ON (s.id = psk.s_id)
WHERE p.id IN (ids from previous query)

Answer 2

你可以做这样的事情。

SELECT p.id, p.name, group_concat(s.skill separator  , )

and then group by person and limit the number of rows.

友情链接