將在同一請求中使用一個SELECT結果在下一個子查詢中的單一SQL查詢

Question

我正在尋找一種可能性（重新）使用一個 SQL 選擇結果來取得同一查詢內不同子選擇的值

首先，我應該透過以下選擇來取得 $ValueToUse：

SELECT `cb_campaignid` as ValueToUse FROM `#__team_groups` WHERE `id` = '$Value'

對於「ValueToUse」結果，如果可能的話，我想在同一個 SQL 查詢語句中進行下一個選擇。

SELECT coalesce (nullif (SUM(Amount), ''), '0') as general_amount, 
(SELECT SUM(c.cb_stand) FROM #__employees c, #__team_users u where c.user_id=u.user_id and u.group=(select id from #__team_groups where cb_campaignid='$ValueToUse')) as teamleden_tussenstand, 
(select title from #__customer_campaigns where id='$ValueToUse') as title, 
(select id from #__team_groups where cb_campaignid='$ValueToUse') as groupID, 
(select goal from #__customer_campaigns where id='$ValueToUse') as goal
 FROM #__customer_payments WHERE published = 1 and campaign_id = '$ValueToUse'

獲得這些結果的最佳方法是什麼？

Answer 1

使用第一個結果當變數怎麼樣？

SELECT @ValueToUse:=
(SELECT `cb_campaignid` as ValueToUse FROM `#__team_groups` WHERE `id` = '$Value');

SELECT coalesce (nullif (SUM(Amount), ''), '0') as general_amount, 
(SELECT SUM(c.cb_stand) FROM #__employees c, #__team_users u where c.user_id=u.user_id and u.group=(select id from #__team_groups where cb_campaignid=@ValueToUse)) as teamleden_tussenstand, 
(select title from #__customer_campaigns where id=@ValueToUse) as title, 
(select id from #__team_groups where cb_campaignid=@ValueToUse) as groupID, 
(select goal from #__customer_campaigns where id=@ValueToUse) as goal
 FROM #__customer_payments WHERE published = 1 and campaign_id = @ValueToUse;