java - informix临时表的唯一ID是什么？

Question

我创建了一个临时表，其中包含多个带有 UNION ALL 语句的表，如下所示。我想稍后将此表映射到 Spring 存储库的实体。换句话说，我想将临时表映射到 spring jpa 或 hibernate 中的实体。

select * from name  UNION ALL 
select * from soft where id >3 
into temp namesoft_tmp

我尝试了以下方法。

select * from namesoft_tmp  

但我看不到可以让我得出这是主键的结论的列是什么。

表名soft_tmp的唯一id(主键)是什么？

如何将自动生成的 ID 添加到临时表？

如何根据唯一id执行select语句？**

Answer 1

一般情况下，UNION ALL查询的结果没有主键；不保证结果集中不存在重复行。

想象一个描述元素表的表格 - 称为元素。

SELECT * FROM elements WHERE atomic_number < 10
UNION ALL 
SELECT * FROM elements WHERE symbol MATCHES '[A-F]*'
INTO TEMP union_all;

此处，元素硼 (B)、碳 (C)、铍 (Be) 和氟 (F) 均列出两次。

但是，您可以使用:

SELECT ROWID, * FROM union_all ORDER BY atomic_number;

获取结果集中的唯一标识符 ROWID。请注意，此唯一标识符在任何给定时间都是唯一的，但不能保证稳定。如果删除行并再次添加它们，则替换行的 ROWID 可能与之前不同。但在您编辑表之前，ROWID 将是唯一的。

+-------+--------+--------+--------------+-----------+--------+-------+
| rowid | atomic | symbol | name         | atomic    | period | group |
|       | number |        |              | weight    |        |       |
+-------+--------+--------+--------------+-----------+--------+-------+
|   257 |      1 | H      | Hydrogen     |    1.0079 |      1 | 1     |
|   258 |      2 | He     | Helium       |    4.0026 |      1 | 18    |
|   259 |      3 | Li     | Lithium      |    6.9410 |      2 | 1     |
|   260 |      4 | Be     | Beryllium    |    9.0122 |      2 | 2     |
|   266 |      4 | Be     | Beryllium    |    9.0122 |      2 | 2     |
|   267 |      5 | B      | Boron        |   10.8110 |      2 | 13    |
|   261 |      5 | B      | Boron        |   10.8110 |      2 | 13    |
|   268 |      6 | C      | Carbon       |   12.0110 |      2 | 14    |
|   262 |      6 | C      | Carbon       |   12.0110 |      2 | 14    |
|   263 |      7 | N      | Nitrogen     |   14.0070 |      2 | 15    |
|   264 |      8 | O      | Oxygen       |   15.9990 |      2 | 16    |
|   265 |      9 | F      | Fluorine     |   18.9980 |      2 | 17    |
|   269 |      9 | F      | Fluorine     |   18.9980 |      2 | 17    |
|   270 |     13 | Al     | Aluminium    |   26.9820 |      3 | 13    |
|   271 |     17 | Cl     | Chlorine     |   35.4530 |      3 | 17    |
|   272 |     18 | Ar     | Argon        |   39.9480 |      3 | 18    |
|   273 |     20 | Ca     | Calcium      |   40.0780 |      4 | 2     |
|   274 |     24 | Cr     | Chromium     |   51.9960 |      4 | 6     |
|   275 |     26 | Fe     | Iron         |   55.8450 |      4 | 8     |
|   276 |     27 | Co     | Cobalt       |   58.9330 |      4 | 9     |
|   277 |     29 | Cu     | Copper       |   63.5460 |      4 | 11    |
|   278 |     33 | As     | Arsenic      |   74.9220 |      4 | 15    |
|   279 |     35 | Br     | Bromine      |   79.9040 |      4 | 17    |
|   280 |     47 | Ag     | Silver       |  107.8700 |      5 | 11    |
|   281 |     48 | Cd     | Cadmium      |  112.4100 |      5 | 12    |
|   282 |     55 | Cs     | Caesium      |  132.9100 |      6 | 1     |
|   283 |     56 | Ba     | Barium       |  137.3300 |      6 | 2     |
|   284 |     58 | Ce     | Cerium       |  140.1200 |      6 | L     |
|   285 |     63 | Eu     | Europium     |  151.9600 |      6 | L     |
|   286 |     66 | Dy     | Dyprosium    |  162.5000 |      6 | L     |
|   287 |     68 | Er     | Erbium       |  167.2600 |      6 | L     |
|   288 |     79 | Au     | Gold         |  196.9700 |      6 | 11    |
|   289 |     83 | Bi     | Bismuth      |  208.9800 |      6 | 15    |
|   290 |     85 | At     | Astatine     |  209.9900 |      6 | 17    |
|   291 |     87 | Fr     | Francium     |  223.0200 |      7 | 1     |
|   292 |     89 | Ac     | Actinium     |  227.0300 |      7 | A     |
|   293 |     95 | Am     | Americium    |  243.0600 |      7 | A     |
|   294 |     96 | Cm     | Curium       |  247.0700 |      7 | A     |
|   295 |     97 | Bk     | Berkelium    |  247.0700 |      7 | A     |
|   296 |     98 | Cf     | Californium  |  251.0800 |      7 | A     |
|   297 |     99 | Es     | Einsteinium  |  252.0800 |      7 | A     |
|   298 |    100 | Fm     | Fermium      |  257.1000 |      7 | A     |
|   299 |    105 | Db     | Dubnium      |  270.1300 |      7 | 5     |
|   300 |    107 | Bh     | Bohrium      |  270.1300 |      7 | 7     |
|   301 |    110 | Ds     | Darmstadtium |  281.1700 |      7 | 10    |
|   302 |    112 | Cn     | Copernicium  |  285.1800 |      7 | 12    |
|   303 |    114 | Fl     | Flerovium    |  289.1900 |      7 | 14    |
+-------+--------+--------+--------------+-----------+--------+-------+