如何从一个表中提取大量多行？

Question

This is an expansion of my original question located here: How do I pull all rows from a table with one unique field and specific values for another field?

I have a table with two fields: user_id and skill_id.

I want to pull out all rows that have a skill_id of a certain number but I have a large number of skill_id's to search for (~30). I was using the self-join suggestion presented in the question linked above but with so many skills to look for, that query is proving extremely slow.

How can I look for a large number of skill_ids without bogging down the query?

EDIT:

Here's an example of what I'm looking for. Using the table below, I want to pull out all rows of users that have skill_id of 10 AND 11 AND 12, etc. (except I'd be looking for more like 30 skills at a time).

TABLE

user_id   |  skill_id
=====================
1         |    10
1         |    11
1         |    12
1         |    13
2         |    10
2         |    12
2         |    13
3         |    15
3         |    16
4         |    10
5         |    45
5         |    46

Answer 1

If I understand your question well, below query might help you. Assuming (user_id, skill_id) is UNIQUE or PK.

SELECT user_id
FROM tab
WHERE skill_id IN (30 entries)
GROUP BY user_id
HAVING SUM(skill_id IN (30 entries)) = 30;

You can test here. http://www.sqlfiddle.com/#!2/f73dfe/1/0