J'ai les tableaux suivants:
member table_user_1 table_user_2 table_user_3 123 1 0 1 456 1 1 0 222 1 0 0 554 0 1 0 124 1 1 0 755 1 0 1 449 1 0 1
Je veux voir tous les utilisateurs syndiqués et ensuite vérifier si l'utilisateur est présent sur la table.
Pour l'union I essayé:
select col6 from table_user_1 union select col5 from table_user_2 union select col7 from table_user_3;
La sortie doit être:
table_user_1 col6 member_records 123 5 456 6 222 4 table_user_2 col5 member_records 554 5 456 6 124 4 table_user_3 col7 member_records 123 5 755 6 449 4
Comment puis-je générer une sortie comme ci-dessus?
4 Réponses :
Vous pouvez utiliser la jointure complète :
select member,
max(t1), max(t2), max(t3)
from ((select col5 as member, 1 as t1, 0 as t2, 0 as t3
from table_user_1
) union all
(select col6, 0 as t1, 1 as t2, 0 as t3
from table_user_2
) union all
(select col7, 0 as t1, 0 as t2, 2 as t3
from table_user_3
)
) x
group by member;
Vous pouvez également utiliser group by et union all code >:
select coalesce(t1.col5, t2.col6, t3.col7) as member,
nvl2(t1.col5, 1, 0) as in_t1,
nvl2(t2.col6, 1, 0) as in_t2,
nvl2(t3.col7, 1, 0) as in_t3
from table_user_1 t1 full join
table_user_2 t2
on t2.col6 = t1.col5 full join
table_user_3 t3
on t3.col7 in (t1.col5, t2.col6);
Dans la deuxième solution, les noms de table sont incorrects. Et vous avez fait 2 comme t3.
Et en première solution, sur t3.col7 dans (t1.col5, t1.col6); devrait être sur t3.col7 dans (t1.col5, t2.col6); je pense
utiliser l'union et la jointure gauche
select a.id as member,nvl2(t1.col6,1,0) as table1,
nvl2(t2.col5,1,0) as tbale2,
nvl2(t3.col7,1,0) as table3 from
(select col6 as id from table_user_1
union
select col5 from table_user_2
union
select col7 from table_user_3
) a left join table_user_1 t1 on a.id=t1.col6
left join table_user_2 t2 on a.id=t2.col5
left join table_user_3 t3 on a.id=t3.col7
@ akk0rd87 ici, le syndicat n'a pas besoin d'union avant le vote négatif, pensez à ce que vous avez suggéré
with table_user_1(col6, member_records) as (
select 123, 5 from dual union all
select 456, 6 from dual union all
select 222, 4 from dual),
table_user_2(col5, member_records) as (
select 554, 5 from dual union all
select 456, 6 from dual union all
select 124, 4 from dual),
table_user_3(col7, member_records) as (
select 123, 5 from dual union all
select 755, 6 from dual union all
select 449, 4 from dual)
select *
from
(select col6, 1 as table_id from table_user_1
union all
select col5, 2 from table_user_2
union all
select col7, 3 from table_user_3
)
pivot(count(*) for table_id in (1 as table_user_1, 2 table_user_2, 3 as table_user_3));
COL6 TABLE_USER_1 TABLE_USER_2 TABLE_USER_3
---------- ------------ ------------ ------------
123 1 0 1
222 1 0 0
554 0 1 0
755 0 0 1
456 1 1 0
449 0 0 1
124 0 1 0
Une option consisterait à utiliser l'agrégation conditionnelle:
select member,
max( case when col0 = 1 then 1 else 0 end ) as table_user_1,
max( case when col0 = 2 then 1 else 0 end ) as table_user_2,
max( case when col0 = 3 then 1 else 0 end ) as table_user_3
from
(
select 1 col0, col6 as member
from table_user_1
union all
select 2 col0, col5
from table_user_2
union all
select 3 col0, col7
from table_user_3 ) q
group by member;