带联合的SQL select语句列顺序

Is there a way in union tables to ignore first select column order and to match records by column names?

联合表中是否有一种方法可以忽略第一个选择列顺序并按列名匹配记录?

Take for example this:

以此为例:

// TEST1 table:
|  a  |  b  |
-------------
|  3  |  5  |

// TEST2 table:
|  b  |  a  |
-------------
|  4  |  9  |

select a, b from TEST1 union ( select b, a from TEST2)

// The result must be a table like the on below:
|  a  |  b  |
-------------
|  3  |  5  |
-------------
|  9  |  4  |

// BUT, actually is:
|  a  |  b  |
-------------
|  3  |  5  |
-------------
|  4  |  9  |

UPDATE:

The name of the columns and their numbers are the same, just the order is different and I CAN'T change the second select column order.

列的名称和它们的编号是相同的,只是顺序不同,我不能更改第二个选择列顺序。

I'm asking for a way to ignore the default sql union behavior and to tell it to match the column name, not the order of passing

我想要一种方法来忽略默认的sql union行为并告诉它匹配列名,而不是传递的顺序

2 个解决方案

#1

UNION doesn't have this kind of fonctionnality.

UNION没有这种功能。

You have to find a workaround for instance :

您必须找到一个解决方法,例如:

select concat('a',a) as a, concat('b',b) as b from TEST1 union ( select concat('b',b) as b, concat('a',a) as a from TEST2)

You will have something like that :

你会有类似的东西:

|  a  |  b  |
-------------
|  a3  |  b5  |
-------------
|  b4  |  a9  |

Then you can parse it and reorder it programmatically.

然后你可以解析它并以编程方式重新排序。

#2

-1

The order of the fields in the table is irrelevant. It's the order that you specify the fields in your union's sub-queries that counts.

表中字段的顺序无关紧要。这是您指定union的子查询中的字段的顺序。

Currently you have:

目前你有:

    select a, b from TEST1
union      |  |
    select b, a from TEST2)

when it should be

什么时候应该

    select a, b from TEST1
union      |  |
    select a, b from TEST2

The ONLY time that field-ordering in the table definition would matter is if you were doing select *.

表定义中字段排序的唯一时间就是你正在做select *。

#1