我有以下表格：
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
我想查看所有用户联合，然后检查用户是否在表上。
对于工会，我尝试过：
select col6 from table_user_1
union
select col5 from table_user_2
union
select col7 from table_user_3;
输出应为：
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
如何生成上述输出？
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
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句