i创建一个临时表,该表由几个表格,带有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
,但我看不出什么是可以指出这是主要键的结论的列。
table namesoft_tmp的唯一ID(主键(是什么?
如何将自动生成的ID添加到temp表?
如何基于唯一ID来为选择语句辩护?**
通常,联合的结果所有查询都没有主键;不能保证结果集中没有重复行。
想象一个描述元素表的表 - 称为 elements
。
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 | 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 |
+-------+--------+--------+--------------+-----------+--------+-------+