SELECT WHERE IN(子查询)慢
我已经尝试了解决方案 这里,但它不起作用.
I've tried the solution here, but it doesn't work.
我的桌子是这样的:
`Index` uid dept
...........................
1 001 dept1
2 001 dept2
3 001 dept3
4 002 dept2
5 002 dept3
6 002 dept4
7 003 dept1
8 003 dept5
9 004 dept1
10 004 dept6
我想检索具有特定 dept
的所有行.也就是说,如果我想检索 dept1
,我想检索除 uid=002 之外的所有行,因为 uid=002 没有 dept1
.
I want to retrieve all the rows with a particular dept
. That is, If I want to retrieve dept1
, I want to retrieve all rows except uid=002, since there's no dept1
for uid=002.
即使使用索引,查询字符串也很慢:
The query string is slow even when using index:
SELECT id FROM table WHERE uid IN
(SELECT uid WHERE dept='dept1')
我之前没有使用 WHERE IN 的版本如下:
My previous version without using WHERE IN is as following:
首先检索所有带有 dept=dept1 的 uid.
然后对第一个查询中检索到的所有 uid 使用 for 循环.
Retrieves all the uid with dept=dept1 first.
Then use a for-loop for all uid retrieved in the first query.
对于在第一个查询中检索到的少量 (100) 行,此方法非常快.但是,这似乎不是一个好的解决方案,因为它会创建大量查询(每个查询都非常快).
This method is very fast for a small amount(100) of rows retrieved in the first query. However, it seems that it's not a good solution because it creates a lot of queries(each of them is extremely fast).
推荐答案
试试这个:
select a.id from Table1 a
inner join Table1 b on a.uid = b.uid and b.dept = 'dept1';
演示:http://sqlfiddle.com/#!2/05774/4
相关文章