我正试图在MySql 5.7中实现三个临时表的完全联接。我正在模仿这个例子(如何在MySQL中执行FULL OUTER JOIN？(，但我找不到也不知道如何用三个表而不是两个表来实现这一点。
我写过：
##########################################################################################
#### QUERY GOALS: Number of Members Gained to X trigger & Number of Members Lost from X trigger
# to do
## add in parameters for ladders
##########################################################################################
#### DROP TABLE IF EXISTS
DROP TABLE IF EXISTS cte_leenk_ladder_history;
DROP TABLE IF EXISTS cte_ladder_history_self_join_lh1;
DROP TABLE IF EXISTS cte_ladder_history_self_join_lh2;
DROP TABLE IF EXISTS cte_ladder_history_join_lh1_lh2_current_trigger_record;
DROP TABLE IF EXISTS cte_distinct_completed_trigger_and_moved;
DROP TABLE IF EXISTS cte_completed_trigger_prior_to_date_no_change;
DROP TABLE IF EXISTS cte_completed_trigger_between_dates_no_change;
DROP TABLE IF EXISTS cte_completed_trigger_between_dates_then_change;
#set @ladder_value = 'ladder_advocacy';
#### CREATE PARAMETERS FOR LADDERS
CREATE TEMPORARY TABLE cte_leenk_ladder_history
SELECT
member_id
,ladder_change
,date_trigger_event
,@ladder_value
,lh.ladder_config_id as ladder_config_id
,trigger_name
,coalesce(lh.ladder_advocacy,lh.ladder_elected,lh.ladder_policy,lh.ladder_organizing,lh.ladder_organizing,lh.ladder_engagement) as ladder_value
,CASE WHEN lh.ladder_advocacy is not null THEN 'ladder_advocacy'
WHEN lh.ladder_elected is not null THEN 'ladder_elected'
WHEN lh.ladder_policy is not null THEN 'ladder_policy'
WHEN lh.ladder_organizing is not null THEN 'ladder_organizing'
WHEN lh.ladder_collective is not null THEN 'ladder_collective'
WHEN lh.ladder_engagement is not null THEN 'ladder_engagement'
END
AS ladder_name
FROM leeds_new.leenk_ladder_history as lh
inner join leeds_new.leenk_ladder_config as lc on lh.ladder_config_id = lc.ladder_config_id
where lh.ladder_change = 1 and lc.active = 1 and lc.trigger = 1
limit 25000;
#### SET UP MASTER TABLE
create temporary table cte_ladder_history_self_join_lh1
select
member_id as member_id_lh1,
ladder_config_id as ladder_config_id_lh1,
trigger_name as trigger_name_lh1,
date_trigger_event as date_trigger_event_lh1,
ladder_name as ladder_name_lh1,
ladder_value as ladder_value_lh1,
ladder_change as ladder_change_lh1
from
cte_leenk_ladder_history as lh1;
create temporary table cte_ladder_history_self_join_lh2
select
member_id as member_id_lh2,
trigger_name as trigger_name_lh2,
date_trigger_event as date_trigger_event_lh2,
ladder_name as ladder_name_lh2,
ladder_value as ladder_value_lh2,
ladder_change as ladder_change_lh2
from
cte_leenk_ladder_history as lh2;
#### Current ladder history
create temporary table cte_ladder_history_join_lh1_lh2_current_trigger_record
select
cte_ladder_history_self_join_lh1.member_id_lh1,
cte_ladder_history_self_join_lh1.ladder_config_id_lh1,
cte_ladder_history_self_join_lh1.trigger_name_lh1,
cte_ladder_history_self_join_lh1.date_trigger_event_lh1,
cte_ladder_history_self_join_lh1.ladder_name_lh1,
cte_ladder_history_self_join_lh1.ladder_value_lh1,
cte_ladder_history_self_join_lh1.ladder_change_lh1,
cte_ladder_history_self_join_lh2.member_id_lh2,
cte_ladder_history_self_join_lh2.trigger_name_lh2,
cte_ladder_history_self_join_lh2.date_trigger_event_lh2,
cte_ladder_history_self_join_lh2.ladder_name_lh2,
cte_ladder_history_self_join_lh2.ladder_value_lh2,
cte_ladder_history_self_join_lh2.ladder_change_lh2,
@member_id:=member_id_lh2 AS member_id,
@row_number:=(CASE
WHEN @member_id = member_id_lh1
THEN @row_number + 1
ELSE 1
END) AS rank
from
cte_ladder_history_self_join_lh1 #previous or current ladder history
left join cte_ladder_history_self_join_lh2 on cte_ladder_history_self_join_lh1.member_id_lh1 = cte_ladder_history_self_join_lh2.member_id_lh2
and cte_ladder_history_self_join_lh2.ladder_name_lh2 = cte_ladder_history_self_join_lh1.ladder_name_lh1
and cte_ladder_history_self_join_lh1.ladder_value_lh1 <> cte_ladder_history_self_join_lh2.ladder_value_lh2
and cte_ladder_history_self_join_lh1.date_trigger_event_lh1 < cte_ladder_history_self_join_lh2.date_trigger_event_lh2 # lh2 shows second begin date / end date for lh if exists
where @row_number:=(CASE
WHEN @member_id = member_id_lh1
THEN @row_number + 1
ELSE 1
END) = 1
order by member_id_lh1 desc, date_trigger_event_lh2 desc;
#### count people who completed X trigger level prior to date and havent changed ever
create temporary table cte_completed_trigger_prior_to_date_no_change
select
cte_ladder_history_join_lh1_lh2_current_trigger_record.trigger_name_lh1 as trigger_name__prior_to_date_no_change, #try removing trigger name grouping for ladder level counts
cte_ladder_history_join_lh1_lh2_current_trigger_record.ladder_value_lh1,
count(cte_ladder_history_join_lh1_lh2_current_trigger_record.trigger_name_lh1) as count_trigger_prior_no_change
from cte_ladder_history_join_lh1_lh2_current_trigger_record
where date_trigger_event_lh1 < CAST('2018-01-01' AS DATE)
and member_id_lh2 is null
group by trigger_name_lh1, ladder_value_lh1
order by ladder_value_lh1 asc;
#### count people who completed X trigger level between dates that never became something else
create temporary table cte_completed_trigger_between_dates_no_change
select
cte_ladder_history_join_lh1_lh2_current_trigger_record.trigger_name_lh1 as trigger_name__between_dates_no_change,
cte_ladder_history_join_lh1_lh2_current_trigger_record.ladder_value_lh1,
count(cte_ladder_history_join_lh1_lh2_current_trigger_record.trigger_name_lh1) as count_trigger_between_no_change
from cte_ladder_history_join_lh1_lh2_current_trigger_record
where date_trigger_event_lh1 BETWEEN CAST('2018-01-01' AS DATE) AND CAST('2018-12-01' AS DATE)
and member_id_lh2 is null
group by trigger_name_lh1, ladder_value_lh1
order by ladder_value_lh1 asc;
#### count people who completed X trigger level between dates AND became something else (people who moved on)
create temporary table cte_completed_trigger_between_dates_then_change
select
cte_ladder_history_join_lh1_lh2_current_trigger_record.trigger_name_lh1 as trigger_name__between_dates_change,
cte_ladder_history_join_lh1_lh2_current_trigger_record.ladder_value_lh1,
count(cte_ladder_history_join_lh1_lh2_current_trigger_record.trigger_name_lh1) as count_trigger_between_then_change
from cte_ladder_history_join_lh1_lh2_current_trigger_record
where date_trigger_event_lh1 BETWEEN CAST('2018-01-01' AS DATE) AND CAST('2018-12-01' AS DATE)
and member_id_lh2 is not null
group by trigger_name_lh1, ladder_value_lh1
order by ladder_value_lh1 asc;
#### join the counts together - do this 3 times, for each table
/* t1 t2 */
select * from cte_completed_trigger_prior_to_date_no_change
left join cte_completed_trigger_between_dates_no_change on cte_completed_trigger_prior_to_date_no_change.trigger_name__prior_to_date_no_change = cte_completed_trigger_between_dates_no_change.trigger_name__between_dates_no_change
union
select * from cte_completed_trigger_prior_to_date_no_change
right join cte_completed_trigger_between_dates_no_change on cte_completed_trigger_prior_to_date_no_change.trigger_name__prior_to_date_no_change = cte_completed_trigger_between_dates_no_change.trigger_name__between_dates_no_change
/* t1 t3 */
select * from cte_completed_trigger_prior_to_date_no_change
left join cte_completed_trigger_between_dates_then_change on cte_completed_trigger_prior_to_date_no_change.trigger_name__prior_to_date_no_change = cte_completed_trigger_between_dates_then_change.trigger_name__between_dates_change
union
select * from cte_completed_trigger_prior_to_date_no_change
right join cte_completed_trigger_between_dates_then_change on cte_completed_trigger_prior_to_date_no_change.trigger_name__prior_to_date_no_change = cte_completed_trigger_between_dates_then_change.trigger_name__between_dates_change
/* t2 t3 */
select * from cte_completed_trigger_between_dates_no_change
left join cte_completed_trigger_between_dates_then_change on cte_completed_trigger_between_dates_no_change.trigger_name__between_dates_no_change = cte_completed_trigger_between_dates_then_change.trigger_name__between_dates_change
union
select * from cte_completed_trigger_between_dates_no_change
right join cte_completed_trigger_between_dates_then_change on cte_completed_trigger_between_dates_no_change.trigger_name__between_dates_no_change = cte_completed_trigger_between_dates_then_change.trigger_name__between_dates_change
有问题的联接位于查询的末尾。我得到了一个"语法错误"。我也尝试过这种方法(如何在MySQL中对多个表进行FULL OUTER JOIN(，以及这种方法((我的(SQL与三个表的完全联接(
想法？
您将UNION放错了位置。
/* t1 t2 */
select * from cte_completed_trigger_prior_to_date_no_change
left join cte_completed_trigger_between_dates_no_change on cte_completed_trigger_prior_to_date_no_change.trigger_name__prior_to_date_no_change = cte_completed_trigger_between_dates_no_change.trigger_name__between_dates_no_change
right join cte_completed_trigger_between_dates_no_change on cte_completed_trigger_prior_to_date_no_change.trigger_name__prior_to_date_no_change = cte_completed_trigger_between_dates_no_change.trigger_name__between_dates_no_change
union all
/* t1 t3 */
select * from cte_completed_trigger_prior_to_date_no_change
left join cte_completed_trigger_between_dates_then_change on cte_completed_trigger_prior_to_date_no_change.trigger_name__prior_to_date_no_change = cte_completed_trigger_between_dates_then_change.trigger_name__between_dates_change
right join cte_completed_trigger_between_dates_then_change on cte_completed_trigger_prior_to_date_no_change.trigger_name__prior_to_date_no_change = cte_completed_trigger_between_dates_then_change.trigger_name__between_dates_change
union all
/* t2 t3 */
select * from cte_completed_trigger_between_dates_no_change
left join cte_completed_trigger_between_dates_then_change on cte_completed_trigger_between_dates_no_change.trigger_name__between_dates_no_change = cte_completed_trigger_between_dates_then_change.trigger_name__between_dates_change
right join cte_completed_trigger_between_dates_then_change on cte_completed_trigger_between_dates_no_change.trigger_name__between_dates_no_change = cte_completed_trigger_between_dates_then_change.trigger_name__between_dates_change