如何从表中拆分数据并插入新关系？

Wraith 发表于 Dev

幽灵

我有带模型的表，仅命名这两个字段

| MODEL |    NAME   |
---------------------
| ABC   |  Abcds123 |
| ABC   |  cde99    |
| ABC   |  oo344    |
| ABC   |  POPOL0   |
| OKL   |  24352233 |
| OKL   |  abcabcab |
| OKL   |  qiueiwow |
| OKL   |  0909o9o9 |
| OKL   |  0909o9o9 |
| OKL   |  0909o9o9 |

我想要做的是获取模型值并获取所有唯一名称并放入新的两个表：模型表和项目表

| ID    | MODEL_NAME |
---------------------
| 1     |  ABC       |
| 2     |  OKL       |

| ID    | MODEL_ID | MODEL_NAME |
---------------------------------
| 1     |  1       | Abcds123
| 2     |  1       | cde99
| 3     |  1       | cde99
| 4     |  2       | 24352233
| 5     |  2       | abcabcab

这可能吗？我试过这样：

SET @rownr=0;
SELECT DISTINCT 
      `model`, 
      (@rownr := @rownr + 1) AS rowNumber 
FROM `TABLE`  
GROUP BY `model`

但我不知道接下来是什么以及如何从同一张表中获取名称

缺口

只要您愿意接受按字母顺序排列，就可以通过这两个查询得到您想要的结果。输出基于您问题中的数据。

查询 1

CREATE TABLE models AS
SELECT (@rownr := @rownr + 1) AS id, model
FROM (SELECT DISTINCT model FROM table1) t
CROSS JOIN (SELECT @rownr := 0) v
ORDER BY model;
SELECT * FROM models

输出：

id  model
1   ABC
2   OKL

查询 2

CREATE TABLE model_names AS
SELECT (@rownr := @rownr + 1) AS id, m.id AS model_id, t1.name AS model_name
FROM (SELECT DISTINCT model, name FROM table1 ORDER BY model, name DESC) t1
JOIN models m ON t1.model = m.model
CROSS JOIN (SELECT @rownr := 0) v
ORDER BY model_id, model_name;
SELECT * FROM model_names

输出：

id  model_id    model_name
1   1           Abcds123
2   1           cde99
3   1           oo344
4   1           POPOL0
5   2           0909o9o9
6   2           24352233
7   2           abcabcab
8   2           qiueiwow

dbfiddle 上的演示

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。