请帮我理解SQL中的这个查询

I'm reading SQL Antipatterns and found this query really hard to understand:

我正在阅读SQL Antipatterns,发现这个查询真的很难理解:

SELECT
    bp1.product_id, b1.date_reported AS latest, b1.bug_id 
FROM
    Bugs b1
    JOIN 
    BugsProducts bp1 ON (b1.bug_id = bp1.bug_id) 
    LEFT OUTER JOIN
    (
      Bugs AS b2
      JOIN 
      BugsProducts AS bp2 ON b2.bug_id = bp2.bug_id
    )
     ON bp1.product_id = bp2.product_id AND 
         (b1.date_reported < b2.date_reported OR b1.date_reported = b2.date_reported
          AND
          b1.bug_id < b2.bug_id
         )
WHERE
    b2.bug_id IS NULL;

Please explain this to me SQL experts.. Thank you!

请向我解释一下SQL专家..谢谢!

2 个解决方案

#1

Show me bug/products where there are no later bugs, based on date/id

根据日期/ ID,向我显示没有后续错误的错误/产品

You can simplify the code to pseudo code

您可以将代码简化为伪代码

SELECT
    pair1 data
FROM
    pair1
    LEFT OUTER JOIN
    pair2
     ON same product, 1st date <= 2nd date, 1st internal id <= 2nd internal id
WHERE
    no such pair2

Edit, FYI: the author is SO user Bill Karwin https://*.com/users/20860

编辑,仅供参考:作者是SO用户Bill Karwin https://*.com/users/20860

#2

It appears the query in the question is the good pattern. I find it confusing and would rewrite it as:

看来问题中的查询是好的模式。我发现它令人困惑,并将其重写为:

SELECT
    bp1.product_id, b1.date_reported AS latest, b1.bug_id 
FROM Bugs b1
INNER JOIN BugsProducts bp1 ON (b1.bug_id = bp1.bug_id) 
WHERE NOT EXISTS 
(
  SELECT * FROM Bugs AS b2
  INNER JOIN BugsProducts AS bp2 ON (b2.bug_id = bp2.bug_id)
  WHERE (bp1.product_id = bp2.product_id) 
     AND 
     (
        (b1.date_reported < b2.date_reported) OR 
        (b1.date_reported = b2.date_reported AND b1.bug_id < b2.bug_id)
     )

I strongly suspect that this code has the same performance (on MySQL anyway) as the query in the question.

我强烈怀疑这段代码与问题中的查询具有相同的性能(无论如何在MySQL上)。

Just in case people are wondering:

以防人们想知道:

http://dev.mysql.com/doc/refman/5.5/en/exists-and-not-exists-subqueries.html

Traditionally, an EXISTS subquery starts with SELECT *, but it could begin with SELECT 5 or SELECT column1 or anything at all. MySQL ignores the SELECT list in such a subquery, so it makes no difference

传统上,EXISTS子查询以SELECT *开头,但它可以从SELECT 5或SELECT column1开始,或者任何东西。 MySQL忽略了这样一个子查询中的SELECT列表,所以没有区别

What was the antipattern associated with this 'good' pattern anyway, would really like to know.

无论如何,与这种“好”模式相关的反模式是什么,真的很想知道。

#1