如何合并不相关的表，但按公共列类型排序？ [英] How to merge unrelated tables, but sort on a common column type?

问题描述

好吧，例如，我有三个表：

Ok, for the example, I have three tables:

**Table A**
id (serial)
timestamp (timestamp)
value1 (double)
value2 (double)

**Table B**
id (serial)
timestamp (timestamp)
text1 (text)
char1 (character)

**Table C**
id (serial)
timestamp (timestamp)
int1 (int)
int2 (int)

id字段每个表都是唯一的，并且充当主键。
在表中放置行但未连接其他表时输入时间戳，每个表可以在不同时间插入行。

The id fields are unique to each table, and act as primary keys. Timestamps are entered when rows are placed in the table, but are not connected to the other tables, and each table can have rows inserted at varying times.

我想要的是一个视图或单个数据集，其中包含每个表的所有记录，并按时间戳排序。

What I want is a view, or a single dataset, that contains all the records from each of the tables, sorted by timestamp.

在此示例中，这意味着数据集将具有以下列：

In this example, this would mean that the dataset would have the following columns:

**Output Table**
timestamp (timestamp)
value1 (double)
value2 (double)
text1 (text)
char1 (character)
int1 (int)
int2 (int)

我了解此结果数据集的每一行中，有4列为空。但是，我需要能够查看按时间戳顺序（以及给定的时间戳范围）排序的所有表中的数据。

I understand that for each row of this resulting dataset, 4 of the columns would be empty. However, I need to be able to view the data from all of the tables sorted in timestamp order (and for a given timestamp range)

我看过联合，但是他们想要通用的列数据类型，因此不合适。
联接似乎需要在一个表和另一个表中的列之间建立连接，所以不合适。

I've looked at unions, but they want common column datatypes, so that didn't fit. Joins appeared to need a connection between columns in one table and another, so that didn't fit.

我只需要创建一个表，在三个表的所有列中，使用单个（时间戳）作为公共排序列。

I just need to create a table, made up of all the columns of the three tables, using a single one (timestamp) as a common sorting column.

最好的方法是什么？

我最初的想法是将每个表中的数据分别提取到数组（PHP / C ++）中，然后在其中执行排序，但是这似乎非常慢，所以我希望有一个更快的SQL解决方案。

My initial idea was to extract the data from each table separately into an array (PHP/C++), then perform a sort there, but this appears to be incredibly slow, so I was hoping for a much faster SQL solution.

注意：每个表可能有成千上万个条目。我的数据库在相关的情况下位于PostgreSQL中。

Note: The tables could have many thousand entries each. My database is in PostgreSQL if it is relevant.

推荐答案

下面的代码可以满足您的需要，并且还可以处理之间的不同排序规则列。解决方案是特定于MySQL的（由于使用了CAST函数，并且存在与归类相关的问题）。

Following code does what you need, and also takes care of different collations between columns. Solution is MySQL specific (due to CAST function being used, and having collation-related problems).

SELECT * FROM (
SELECT
    `timestamp`,
    CAST(`value1` AS CHAR) AS `value1`,
    CAST(`value2` AS CHAR) AS `value2`,
    CAST(NULL AS CHAR) AS `text1`,
    CAST(NULL AS CHAR) AS `char1`,
    CAST(NULL AS SIGNED) AS `int1`,
    CAST(NULL AS SIGNED) AS `int2`
FROM `table_a`
UNION
SELECT
    `timestamp`,
    CAST(NULL AS CHAR) AS `value1`,
    CAST(NULL AS CHAR) AS `value2`,
    CAST(`text1` AS CHAR) AS `text1`,
    CAST(`char1` AS CHAR) AS `char1`,
    CAST(NULL AS SIGNED) AS `int1`,
    CAST(NULL AS SIGNED) AS `int2`
FROM `table_b`
UNION
SELECT
    `timestamp`,
    CAST(NULL AS CHAR) AS `value1`,
    CAST(NULL AS CHAR) AS `value2`,
    CAST(NULL AS CHAR) AS `text1`,
    CAST(NULL AS CHAR) AS `char1`,
    CAST(`int1` AS SIGNED) AS `int1`,
    CAST(`int2` AS SIGNED) AS `int2`
FROM `table_c`) `table_all`
ORDER BY `timestamp`

此外，您可以做到这一点并不意味着您应该执行。最好尝试重新排列（去规范化）数据，否则您可能会一遍又一遍地遇到类似的问题。在UNION结果集中对许多行进行排序并不是有效的一切。

Also, the fact that you can do it, doesn't mean that you should do it. Better try to rearrange, (de)normalize your data, otherwise you might be running into similar issues over and over again. Sorting many rows in UNION result set is everything but efficient...

这篇关于如何合并不相关的表，但按公共列类型排序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！