Selecting the right column

问题:

I have a query.

with result as
(
select t1.name, t1.number, t2.number from table1 t1, table2 t2 where some conditions
union all
select t1.name, t1.number, t3.number from table1 t1, table3 t3 where some conditions
)select * from result

I need to insert in table5 t1.name and t2.number

table5 has the same columns as t1.

If I do something like

insert in table5(name, number)
select r.name, r.number from result r

what would be considered r.number? t1.number or t2.number? Because columns have the same name. Or is there a way to defferentiate? How can I make it so the query skips every row with t3.number? Can I even do it?

For example I have table1

A ([phone]
B ([phone]
C ([phone]

table2

([phone]
([phone]

first select will get me

A ([phone] ([phone]
B ([phone] ([phone]

table3

([phone]
([phone]
([phone]

result of second select

B ([phone] ([phone]
C ([phone] ([phone]

this will be the result of union all

A ([phone] ([phone]
B ([phone] ([phone]
B ([phone] ([phone]
C ([phone] ([phone]

what I want in the end is

A ([phone]
B ([phone]

the end result should not have this rows

B ([phone] ([phone]
C ([phone] ([phone]

回答1:

Both. In some rows, t2.number is number and in others t3.number is number.

The result of the union all in a single result set. The result set doesn't know the origin of the values in any particular column (although you could include another column with this information).