如何用LEFT JOIN更有效地重写UNION

Question

我有两个表…一个注册用户，一个签入用户。用户在注册表中总是只有一个表项，但在签入表中可能有0个或多个表项。对于抽奖选择器，我编写了一个查询，从注册表中选择1个条目，然后从签入表中选择1个条目—只要userID不存在于存储抽奖获胜者的第三个表中，每个子查询都会随机选择一个条目。在返回两个条目后，它将随机选择其中一个作为获胜者。

然而，我相信应该有一个更有效的方式来写这个，所以它只选择一个条目一次....不选择两个条目，然后选择其中一个

我花了很长时间才弄清楚如何正确地编写下面的查询，因为我根本不精通mysql。查询工作，似乎工作效率很高，但我相信应该有一个更好的方式来编写它，也合并查询代码的数量。

希望这里有人能帮助或建议。

表说明:clubusers/clubHistory有多个重叠的列，但表不相同:

register = clubUsers 
checkins = clubHistory
winners = clubRaffleWinners
SELECT * FROM (  
(SELECT ch.user_ID,ch.clID FROM clubHistory AS ch 
LEFT OUTER JOIN clubRaffleWinners AS cr1 ON 
ch.user_ID=cr1.user_ID  
AND cr1.cID=1157 
AND cr1.rafID=18  
AND cr1.crID=1001   
AND cr1.ceID=1167  
AND cr1.chDate1='2022-06-04' 
WHERE  
ch.cID=1157  
AND ch.crID=1001   
AND ch.ceID=1167 
AND ch.chDate='2022-06-04' 
AND cr1.user_ID IS NULL 
GROUP BY ch.user_ID ORDER BY RAND() LIMIT 1 
)
UNION
(SELECT cu.user_ID,cu.clID FROM clubUsers AS cu 
LEFT OUTER JOIN clubRaffleWinners AS cr2 ON 
cu.user_ID=cr2.user_ID  
AND cr2.cID=1157 
AND cr2.rafID=18  
AND cr2.crID=1001   
AND cr2.ceID=1167  
AND cr2.chDate1='2022-06-04' 
WHERE  
cu.cID=1157  
AND cu.crID=1001   
AND cu.ceID=1167 
AND cu.calDate<='2022-06-04' 
AND cr2.user_ID IS NULL 
GROUP BY cu.user_ID ORDER BY RAND() LIMIT 1   
)
) AS foo order by RAND() LIMIT 1 ;

<标题>

更新:正如下面@JettoMartinez所指出的，我当前的查询实际上可以从每个表随机返回相同的用户，因此最终返回的条目将只是相同的用户。我没有意识到这一点，在我的斗争，只是让上面的查询工作。因此，由于另一个原因，我最初的OP要求更优化的查询只是简单地从两个表中选择一个随机条目(其中该用户不在获胜者表中)是适用的。

Answer 1

我可以想到两种方法(请注意，由于我不完全理解这些表，所以我没有使用您在JOIN语句中使用的所有条件，这意味着它可能需要更多的工作):

使用排他子查询:

SELECT 
cu.user_ID,
cu.clID,
ch.cID
FROM 
clubUsers cu
LEFT JOIN clubHistory ch ON ch.user_ID = cu.user_ID
WHERE user_ID NOT IN (
SELECT 
user_ID
FROM
clubRaffleWinners
WHERE
-- other conditions 
)
ORDER BY RAND() LIMIT 1;

使用LEFT "OUTER" JOIN

,当你要求:

SELECT 
cu.user_ID,
cu.clID,
ch.cID -- Or any relevant field from clubHistory, really
FROM 
clubUsers cu
LEFT JOIN clubHistory ch ON ch.user_ID = cu.user_ID
LEFT JOIN clubRaffleWinners cr ON cr.user_ID = cu.user_ID 
AND ... -- other conditions to ensure uniqueness
AND ... -- that could also be in the WHERE part
WHERE 
cr.user_ID IS NULL -- this will filter out the INNER part of the JOIN
ORDER BY RAND() LIMIT 1;

我没有一个数据集来正确测试这个查询，所以请把它们作为一个概念。我也没有在clubHistory中查询，因为我真的看不出这样做的意义。对我来说，将clubRaggleWinners插入clubUsers似乎足够了。

编辑由于clubHistory中的user_ID与抽奖相关，我添加了LEFT JOIN，并在SELECT语句中添加了上述表中的字段，因此user_id在clubHistory和clubUsers行中每个条目重复一次，这意味着每个用户都有1 + number of entries / number of users + number of entries - number of winners次获胜的机会。

此逻辑也可以应用于带有子查询的第一个查询，如果添加的字段需要输出，则可以将查询包装在CTE或子查询中。

Answer 2

从你所描述的，我想确保我理解。

每个报名的人都有资格参赛。

然而，每次他们签入时，他们每次签入都会得到1个条目。所以，对于注册过但从未登记过的人，他们得到1个条目。但是如果有人注册了，并且登记了3次，那么他们总共只会得到3次登记，而仅仅登记了4次。

无论谁是可能的，您都要排除所有已经在抽奖中获胜的人。

您应该能够从下面得到结果。由于这些列在cID、crID、ceID和Date上似乎是相同的过滤，因此我使用了基于已注册的clubUsers的主FROM。

由此，到clubHistory的左联接将允许只在注册时返回一次该人的ID，或者根据签入次数(如示例)多次返回该人的ID。

对于给定的用户，我也直接以相同的标准左加入抽奖中奖历史。如果俱乐部历史加入的条件相同，并且抽奖的条件相同(rafID = 18除外)，似乎表明正在抽一个特定的抽奖，如果该人被找到，或者没有，最终的WHERE帐户要排除，如果它是单个条目，或者通过is NULL测试的多个条目。

查询将返回所有未按RAND()限定符顺序获胜的单个或多个条目，并应用单个LIMIT 1来获得最终获胜者。我不知道你为什么需要看起来像俱乐部的ID，你只关心谁赢了，而不关心是不是俱乐部的历史条目。

SELECT 
cu.user_ID
FROM 
clubUsers AS cu
LEFT JOIN clubHistory ch 
on cu.user_ID = ch.user_ID  
AND cu.cID = ch.cID
AND cu.crID = ch.crID
AND cu.ceID = ch.ceID
AND ch.chDate = '2022-06-04' 
LEFT JOIN clubRaffleWinners AS crw 
ON cu.user_ID = crw.user_ID  
AND cu.cID = crw.cID
AND cu.crID = crw.crID
AND cu.ceID = crw.ceID
AND crw.chDate1 = '2022-06-04'
AND crw.rafID = 18  
WHERE  
cu.cID = 1157  
AND cu.crID = 1001   
AND cu.ceID = 1167 
AND cu.calDate <= '2022-06-04' 
AND crw.user_id IS NULL
order by 
RAND() 
LIMIT 1

出于性能考虑，我将确保以下索引

table               index 
clubUsers           ( cid, crID, ceID, calDate, user_id )
clubHistory         ( user_id, cID, crID, ceID, chDate )
clubRaffleWinners   ( user_id, cID, crID, ceID, chDate1, rafID )

Answer 3

(只是一个注释，但需要格式化)

我将首先尝试将这4个值放在一个表中，而不是在3个表中重复:

cu.cID=1157
AND  cu.crID=1001
AND  cu.ceID=1167
AND  cu.calDate<='2022-06-04'

请提供每张表的SHOW CREATE TABLE;然后我可以评估推荐的索引是否有意义。