使用内部连接更改此查询?

I have been having some trouble working with inner joins I guess:

我想我在处理内部连接时遇到了一些麻烦:

select count(distinct id) 
  from svn1,
       svn2 
 where svn1.revno = svn2.revno 
   and svn1.type = 'Bug' 
   and svn2.authors IN (select authors 
                          from svn2 
                         where revno = '33')

How to make this faster with inner joins? My query with inner joins gives weird results.

如何让内部连接更快?使用内部连接的查询会产生奇怪的结果。

Table Info for svn1: Columns: id revno type Data:
1 22 Bug
1 23 Change
1 24 Bug
2 33 Bug
2 34 Bug

表信息为svn1:列:id revno类型数据:1 22 Bug 1 23 Change 1 24 Bug 2 33 Bug 2 34 Bug

Table Info for svn2: Columns: revno authors Data:
22 A
22 B
22 C
33 D
33 A
33 C

svn2:列:revno作者数据:22 A 22 B 22 C 33 D 33 A 33 C

I want ids of type bug which have a common author with authors of revno 33. i.e ids which also have revno with authors A,D or C in it

我想要类型错误的id，它与revno 33的作者有共同的作者。我。e id中也有作者A,D或C的revno

In general I also want a query to answer give an id find other ids which have an authors in common.

通常，我还需要一个查询来回答，给出一个id，查找具有共同作者的其他id。

6 个解决方案

#1

You need something like this:

你需要这样的东西:

select count(distinct id)
from svn1 inner join svn2 on svn1.revno = svn2.revno
      inner join svn2 second on svn2.authors = second.authors
where svn1.type='Bug' and and second.revno='33'

#2

select count(distinct svn1.id) 
 from svn1
 inner join svn2 on svn1.revno = svn2.revno 
where
   svn1.type='Bug' 
   and svn2.authors IN (select authors 
                          from svn2 
                         where revno='33')

Does revno and authors have indexes?

revno和作者是否有索引?

#3

In general I also want a query to answer give an id find other ids which have an authors in common.

通常，我还需要一个查询来回答，给出一个id，查找具有共同作者的其他id。

select distinct svn1.id 
from svn2
join svn2 link on svn2.author = link.author
join svn1 on link.revno = svn1.revno
where svn2.revno = '33'

---

old below

下面的老

Does this work?

这工作吗?

select count(distinct id) 
from svn1
inner join svn2 on svn1.revno = svn2.revno and svn1.authors = svn2.authors
where sv1.type = 'bug' and sv2.revno = '33'  

#4

select count(distinct id)
  from svn1 s1
  inner join svn2 s2 on s1.revno = s2.revno
  inner join svn2 s2b on s2.authors=sb2.authors and s2b.revno='33'
where
  and svn1.type = 'Bug' 

I think that's equivalent to what your existing query does. It may give you a speed increase; but I can't tell without seeing the DB.

我认为这与现有查询的作用是一样的。它可以让你加速;但我没看到DB就看不出来。

More important than refactoring the query though, do your DB tables have an indexes on the revno field? If not, add one - that'll have a far bigger effect than any tinkering with the query.

比重构查询更重要的是，您的DB表在revno字段上有索引吗?如果没有，添加一个——这将比修改查询产生更大的影响。

#5

You said you wanted the actual ids not the count so I did this.

你说你想要的是实际的id而不是计数，所以我做了这个。

   select distinct s1.id 
      from svn1 s1
      join svn2  s2
     ON s1.revno = s2.revno 
    Where s1.type = 'Bug'  
    and s2.authors IN (select authors  
                              from svn2  
                             where revno = '33') 

Now for the future, you need to stop using implicit joins, they are a very poor practice. They are hard to maintain properly, subject to bugs that explicit joins are not subject to and thus riskier than using explicit joins, they contribute to developers not understanding joins well enough to properly query and they create difficulties when you need to use a left join instead of an inner join and they are 18 years outdated.

现在对于未来，您需要停止使用隐式连接，它们是一个非常糟糕的实践。他们很难维持正常,bug,显式连接不受,因此风险比使用显式连接,它们有助于开发人员不理解连接充分正确地查询和他们创造的困难当你需要使用一个左连接而不是一个内部连接和18年过时的。

#6

I'm not 100% clear on what you're trying to do, but here is my attempt:

我不是百分百清楚你想做什么，但我的尝试是:

select count(distinct id)
from svn1
inner join svn2 on svn1.revno = svn2.revno
where svn1.type = 'Bug'
and svn2.revno = '33'