左加入单个随机记录 MySQL [英] Left Join a Single Random Record MySQL

问题描述

我试图从与 Company 表相连的 Sponsor 表中随机获取一行.以下查询几乎有效，但有时会返回一个NULL赞助商.

I am trying to get a single row at random from the Sponsor table joined to the Company table. The follow query almost works but will sometimes return a NULL Sponsor.

谁能看到我在这里做错了什么?

Can anyone see what I am doing wrong here?

SELECT C.ID AS CompID, C.Name AS CompName, S.ID AS SponID, S.Name AS SponName 
FROM Company C 
     LEFT JOIN Sponsor S ON S.ID = (SELECT ID FROM Sponsor WHERE Company = C.ID ORDER BY RAND() LIMIT 1)

数据样本:

Company Table
| ID | Name      |
|  1 | MyCompany |

Sponsor Table
| ID | Company | Name    |
|  1 |     1   | Bruce   |
|  2 |     1   | John    |

以下列方式之一查询结果:

Query Results in one of the following:

| CompID | CompName  | SponsID | SponName |
|    1   | MyCompany |    1    |  Bruce   |

| CompID | CompName  | SponsID | SponName |
|    1   | MyCompany |    2    |  John    |

| CompID | CompName  | SponsID | SponName |
|    1   | MyCompany |  NULL   |  NULL    |

推荐答案

由于 RAND()，您的子查询不是确定性的，因此会针对 Sponsor 中的每一行执行table 并且每次都返回一个随机 ID，该 ID 可能与当前行的 ID 匹配或不匹配.因此，不仅可能没有行与随机 ID 匹配.多行也有可能.

Because of RAND() your subquery is not deterministic and thus is executed for every row in Sponsor table and every time retuns a random ID which might match or not the ID of the current row. So it's not only possible, that no row will match the random ID. It's also possible that multiple rows will.

对于有两个发起人的样本数据，子查询可能返回以下值:

For the sample data with two sponsors the subquery may return folowing values:

• (1, 1) 将匹配第一行 (1=1, 2=1)
• (1, 2) 将匹配两行 (1=1, 2=2)
• (2, 1) 将不匹配任何行 (1=2, 2=1)
• (2, 2) 将匹配第二行 (1=2, 2=2)

为了保证子查询只执行一次，可以使用SELECT子句.然后将结果作为派生表与 Sponsor 表连接:

To guarantee that the subquery is executed only once, you can use it the SELECT clause. Then join the result as derived table with the Sponsor table:

SELECT C.*, S.Name AS SponName 
FROM (
    SELECT C.ID AS CompID, C.Name AS CompName, (
        SELECT ID FROM Sponsor WHERE Company = C.ID ORDER BY RAND() LIMIT 1
    ) as SponID
    FROM Company C
) C
LEFT JOIN Sponsor S ON S.ID = C.SponID

演示:http://rextester.com/LSSJT25902

这篇关于左加入单个随机记录 MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！