Mysql在double LEFT OUTTER JOIN with UNION ALL中标识一行来自哪个表

Question

我下面的查询工作，但有两件事我想从查询，我不知道怎么做。

1. 如何告诉哪个LEFT JOIN最后返回的行是来自?
2. 是否可以返回每个LEFT JOIN的总数?

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

在我的两个左连接选择语句中，我尝试:

(SELECT ch.user_ID as chUserID, ch.clID FROM clubHistory AS ch 
and
(SELECT cu.user_ID as cuUserID, cu.clID FROM clubUsers AS cu 

但是每一个单独的结果，经过几十次的尝试，返回一个user_ID或chUserID。当我删除ORDER BY RAND() LIMIT 1-唯一返回的两列是user_ID, clID或chUserID, clID，即使合并的结果是两个表的完整列表。这可能吗?

和上面的#2，是否有可能从每个LEFT JOIN中提取出最终order by rand() limit 1的总数??

Answer 1

对于1，添加一个额外的列，该列包含一个值，该值标识它是UNION的哪个子查询。

SELECT * FROM (
(SELECT 'history' AS which, ch.user_ID, ch.clID FROM clubHistory AS ch 
LEFT OUTER JOIN clubRaffleWinners AS cr ON 
ch.user_ID = cr.user_ID  
AND cr.cID=1157 
AND cr.rafID=18  
AND cr.chDate1 = '2022-06-04' 
WHERE  ch.cID=1157  
AND ch.crID=1001   
AND ch.ceID=1167  
AND ch.chDate = '2022-06-04' 
AND cr.user_ID IS NULL 
GROUP BY ch.user_ID ) 
UNION ALL 
(SELECT 'users' AS which, cu.user_ID, cu.clID FROM clubUsers AS cu 
LEFT OUTER JOIN clubRaffleWinners AS cr1 ON 
cu.user_ID = cr1.user_ID  
AND cr1.cID=1157 
AND cr1.rafID=18  
AND cr1.chDate1 = '2022-06-04' 
WHERE  cu.cID=1157  
AND cu.crID=1001   
AND cu.ceID=1167  
AND cu.calDate = '2022-06-04' 
AND cr1.user_ID IS NULL 
GROUP BY cu.user_ID )
) as winner ORDER BY RAND() LIMIT 1 ;

请一次只问一个问题。