mysql - 在 MySQL 中转置具有不同数据类型的数据库

Question

我是 MySQL 或任何其他数据库语言的初学者。我的任务是从 MySQL 数据库创建导入。不幸的是，导入只希望在列中获取值，而数据库对于每个不同的查询都有一列。

借助 stackoverflow 上的一些条目和 MySQL 文档，我能够转置数据库。不幸的是，有不同的数据类型。我还了解了 SQL Fiddle，并花了一些时间在那里重新创建数据库。但我想 sql 表达式的测试在那里更容易。

我使用了函数 select/union all(在了解到 MySQL 与 SQL 不同，因此 unpivot 不起作用之后)。很高兴我能够保留 2 列“id”和“报告日期”，因为我在 stackoverflow 上发现的大多数示例只保留 1 列。

问题是某些列的数据类型为“int”，而其他列的数据类型为“decimal (2)”。 union 命令仅适用于相同的数据类型或以这种方式格式化，以便小数点被截断!如何将 MySQL 表达式调整为具有 int 和 decimal 的一列？我想这是某种加入方式？

真实数据库目前有43列需要切换，其中9列是小数。

从下一个使用的软件的逻辑来看，所有数据都是十进制 (2) 可能没问题，因为我可以更改该软件中的类型。

如果在德语数据库中使用逗号而不是十进制分隔符的点，也会有问题吗？

http://sqlfiddle.com/#!9/59cb76/8

到目前为止使用的代码:

CREATE TABLE Table1
(`id` int, `created_at` datetime, `reporting_date` date, `improvement` int, `internal` int, `external` int, `value` decimal(2));

INSERT INTO Table1
(`id`, `created_at`, `reporting_date`, `improvement`, `internal`, `external`, `value`)
VALUES
(1, '2018-08-08 10:33:00', '31.07.2018', 1, 4, 3, '2.76'),
(2, '2018-08-08 10:34:00', '31.01.2018', 1, 4, 2, '3.12'),
(3, '2018-08-08 10:35:00', '31.12.2017', 1, 4, 5, '4.23');

转置查询:

select id, reporting_Date, 'improvement' Field, improvement Value
from table1
union all
select id, reporting_Date, 'internal' Field, internal Value
from table1
union all
select id, reporting_Date, 'external' Field, external Value
from table1
union all
select id, reporting_Date, 'value' Field, value Value
from table1

基础数据

| id | reporting_Date | improvement | internal | external | value |
|----|----------------|-------------|----------|----------|-------|
|  1 |     2031-07-20 |           1 |        4 |        3 |   2.76|
|  2 |     2031-01-20 |           1 |        4 |        2 |  3.12 |
|  3 |     2031-12-20 |           1 |        4 |        5 |  4.23 |

预期输出:

| id | reporting_Date |       Field | Value |
|----|----------------|-------------|-------|
|  1 |     2031-07-20 | improvement |     1 |
|  2 |     2031-01-20 | improvement |     1 |
|  3 |     2031-12-20 | improvement |     1 |
|  1 |     2031-07-20 |    internal |     4 |
|  2 |     2031-01-20 |    internal |     4 |
|  3 |     2031-12-20 |    internal |     4 |
|  1 |     2031-07-20 |    external |     3 |
|  2 |     2031-01-20 |    external |     2 |
|  3 |     2031-12-20 |    external |     5 |
|  1 |     2031-07-20 |       value |  2.76 |
|  2 |     2031-01-20 |       value | 3.12  |
|  3 |     2031-12-20 |       value |  4,23 |

电流输出

| id | reporting_Date |       Field | Value |
|----|----------------|-------------|-------|
|  1 |     2031-07-20 | improvement |     1 |
|  2 |     2031-01-20 | improvement |     1 |
|  3 |     2031-12-20 | improvement |     1 |
|  1 |     2031-07-20 |    internal |     4 |
|  2 |     2031-01-20 |    internal |     4 |
|  3 |     2031-12-20 |    internal |     4 |
|  1 |     2031-07-20 |    external |     3 |
|  2 |     2031-01-20 |    external |     2 |
|  3 |     2031-12-20 |    external |     5 |
|  1 |     2031-07-20 |       value |     3 |
|  2 |     2031-01-20 |       value |     3 |
|  3 |     2031-12-20 |       value |     4 |

对于格式和初学者的问题，我们深表歉意，感谢您的帮助。

游侠

Answer 1

您的SELECT 查询没有问题。事实上，在官方 MySQL 文档中，这个声明(你的)没有依据；这是错误的(简单明了):

The union command only works on the same datatype or formats it that way so that the decimal points are cut off!

现在，仔细查看 SQL Fiddle 很快就会发现架构中的 DECIMAL 数据类型定义是完全错误的。现在，请注意 MySQL doc 中的以下内容:

The declaration syntax for a DECIMAL column is DECIMAL(M,D). The ranges of values for the arguments are as follows:

M is the maximum number of digits (the precision). It has a range of 1 to 65.

D is the number of digits to the right of the decimal point (the scale). It has a range of 0 to 30 and must be no larger than M.

If D is omitted, the default is 0. If M is omitted, the default is 10.

现在，仔细检查您的表定义:

CREATE TABLE Table1 (id int, created_at datetime, reporting_date date, improvement int, internal int, external int, value decimal(2));

您没有将 value 列定义为具有 2 个精度点的小数，而是一个小数点前最多只有两位数且零精度的小数类型。

因此，即使您以一定的精度插入值，它们在存储时都会被截断。一个简单的 SELECT * FROM Table1 会显示 Value 列中的所有值都失去了精度。检查http://sqlfiddle.com/#!9/59cb76/14

---

现在，您只需将 value 的数据类型定义修改为 DECIMAL(10,2):

模式(MySQL v5.7)

CREATE TABLE table1
    (`id` int, `created_at` datetime, `reporting_date` date, `improvement` int, `internal` int, `external` int, `value` decimal(10,2))
;

INSERT INTO table1
    (`id`, `created_at`, `reporting_date`, `improvement`, `internal`, `external`, `value`)
VALUES
    (1, '2018-08-08 10:33:00', '31.07.2018', 1, 4, 3, '2.76'),
    (2, '2018-08-08 10:34:00', '31.01.2018', 1, 4, 2, '3.12'),
    (3, '2018-08-08 10:35:00', '31.12.2017', 1, 4, 5, '4.23')
;

---

查询#1

select id, reporting_Date, 'improvement' Field, improvement Value
from table1
union all
select id, reporting_Date, 'internal' Field, internal Value
from table1
union all
select id, reporting_Date, 'external' Field, external Value
from table1
union all
select id, reporting_Date, 'value' Field, value Value
from table1;

| id  | reporting_Date | Field       | Value |
| --- | -------------- | ----------- | ----- |
| 1   | 2031-07-20     | improvement | 1     |
| 2   | 2031-01-20     | improvement | 1     |
| 3   | 2031-12-20     | improvement | 1     |
| 1   | 2031-07-20     | internal    | 4     |
| 2   | 2031-01-20     | internal    | 4     |
| 3   | 2031-12-20     | internal    | 4     |
| 1   | 2031-07-20     | external    | 3     |
| 2   | 2031-01-20     | external    | 2     |
| 3   | 2031-12-20     | external    | 5     |
| 1   | 2031-07-20     | value       | 2.76  |
| 2   | 2031-01-20     | value       | 3.12  |
| 3   | 2031-12-20     | value       | 4.23  |

---

View on DB Fiddle