我想创建一个表，其中有多行具有相同的属性，但列不同

u5i3ibmn 于 2021-06-15 发布在 Mysql

关注(0)|答案(4)|浏览(324)

这个问题在这里已经有答案了：

mysql-如何将列解压到行(3个答案）
去年关门了。
对不起，我应该更具体地回答我的问题：
这是我现在的问题
我有一张table，上面有：

Customer_ID        H2_1      H2_2      H2_3     H2_4      H2_5
123                 7         3         13       5         2
456                 7         3         13       5         2
789                 7         3         13       5         2

我需要它看起来像这样：

Customer_ID         H2_Code
123                    7
123                    3
123                   13
123                    5
123                    2
456                    7
456                    3
456                   13
456                    5
456                    2
789                    7
789                    3
789                   13
789                    5

sql mysql

来源：https://stackoverflow.com/questions/54111442/i-want-to-create-a-table-that-has-multiple-rows-of-the-same-attribute-with-a-dif

4条答案

按热度按时间

jecbmhm31#

请注意，标准化模式通常如下所示：

Customer_ID    H2    val    
123             1      7     
123             2      3     
123             3     13     
123             4      5     
123             5      2
456             1      7     
456             2      3     
456             3     13     
456             4      5     
456             5      2
789             1      7     
789             2      3     
789             3     13     
789             4      5     
789             5      2

赞(0）回复(0）举报 2021-06-16

7gs2gvoe2#

您可能希望合并不同的查询，这些查询获取每个h2\u的内容，然后按客户id订购
架构（mysql v5.7）

CREATE TABLE tableName (
  `Customer_ID` INTEGER,
  `H2_1` INTEGER,
  `H2_2` INTEGER,
  `H2_3` INTEGER,
  `H2_4` INTEGER,
  `H2_5` INTEGER
);

INSERT INTO tableName
  (`Customer_ID`, `H2_1`, `H2_2`, `H2_3`, `H2_4`, `H2_5`)
VALUES
  ('123', '7', '3', '13', '5', '2'),
  ('456', '7', '3', '13', '5', '2'),
  ('789', '7', '3', '13', '5', '2');

查询#1

SELECT customer_Id, H2_1 AS H2_Code FROM tableName
UNION ALL
SELECT customer_Id, H2_2 FROM tableName
UNION ALL
SELECT customer_Id, H2_3 FROM tableName
UNION ALL
SELECT customer_Id, H2_4 FROM tableName
UNION ALL
SELECT customer_Id, H2_5 FROM tableName
ORDER BY customer_Id;

| customer_Id | H2_Code |
| ----------- | ------- |
| 123         | 3       |
| 123         | 13      |
| 123         | 5       |
| 123         | 2       |
| 123         | 7       |
| 456         | 3       |
| 456         | 13      |
| 456         | 5       |
| 456         | 2       |
| 456         | 7       |
| 789         | 7       |
| 789         | 3       |
| 789         | 13      |
| 789         | 5       |
| 789         | 2       |

db fiddle视图

赞(0）回复(0）举报 2021-06-16

6qftjkof3#

你好像想要 union all :

select Customer_ID, H2_1 as h2_code from t
union all
select Customer_ID, H2_2 as h2_code from t
union all
select Customer_ID, H2_3 as h2_code from t
union all
select Customer_ID, H2_4 as h2_code from t
union all
select Customer_ID, H2_5 as h2_code from t;

请注意 union all 需要扫描表5次。对于中小型表，这不应该是一个问题。对于较大的表，有稍微复杂一些的机制只扫描一次表。

赞(0）回复(0）举报 2021-06-16

55ooxyrt4#

你需要 UNION ALL 要合并所有列：

SELECT Customer_ID, H2_1 AS H2_Code
FROM table t
UNION ALL
SELECT Customer_ID, H2_2
FROM table t
UNION ALL
SELECT Customer_ID, H2_3
FROM table t 
UNION ALL
SELECT Customer_ID, H2_4
FROM table t 
UNION ALL
SELECT Customer_ID, H2_5
FROM table t

赞(0）回复(0）举报 2021-06-15

我来回答

我想创建一个表，其中有多行具有相同的属性，但列不同

4条答案

相关问题

热门标签

最新问答