pg数据里面，求写SQL语句，一列变成两列。

我想要修改数据库结构，如下图所示，effects这一列，可能有几个属性用空格分开的。

img

我希望变成effect1、effect2两列。

原始代码如下

sudo -u postgres psql sanguo -c "CREATE TABLE items(id serial, name VARCHAR(20),city VARCHAR(10),effects VARCHAR(30),value int);"
sudo -u postgres psql sanguo -c "INSERT INTO items(name,city,effects,value) VALUES('短戟','北海','武力+2','10'),('司马法','北海','统率+7 军师','50'),('墨子','北海','统率+4 攻城','30'),('管子','北海','政治+8 耕作','45'),('尉缭子','长安','统率+5 军师','40'),('漆叶青粘散','长安','寿命延长','45'),('商君书','长安','政治+7 耕作','40'),('孙子兵法','长沙','统率+10 鬼谋','70'),('麻沸散','长沙','寿命延长','50'),('老君入仙符','长沙','寿命延长','25'),('手戟','长沙','武力+2','10'),('论语','陈留','智力+7 人脉','50'),('金马槊','成都','武力+2','10'),('梅花袖箭','成都','武力+2 一骑','20'),('屠命散','桂阳','寿命延长','25'),('吴钩','桂阳','武力+1','5'),('吴越春秋','会稽','政治+2 水练','35'),('栢弓','蓟','武力+1 远射','25'),('百辟刀','蓟','武力+3','15'),('五石散','蓟','寿命延长','40'),('孙膑兵法','建邺','统率+5 冷静','40'),('养由基之弓','江陵','武力+4 远射','40'),('山海经','江州','智力+1 神速','30'),('斩蛇之剑','洛阳','武力+10 豪杰','70'),('四月民令','洛阳','政治+2 耕作','20'),('韩非子','洛阳','政治+10 兵心','50'),('周书阴符','洛阳','政治+2 冷静','35'),('短戟','濮阳','武力+2','10'),('亭历犬血散','濮阳','寿命延长','45'),('吴子','汝南','统率+6 军师','45'),('檀弓','汝南','武力+1','10'),('庄子','汝南','智力+8 辫舌','50'),('六韬','天水','统率+8 冷静','60'),('李广之弓','天水','武力+4 远射','40'),('伤寒杂病论','宛城','寿命延长','35'),('双铁戟','宛城','武力+4','15'),('大宛马','西凉','确实撤退','40'),('凉州马','西凉','确实撤退','40'),('礼记','西凉','智力+5 名士','35'),('遁甲天书','西凉','智力+3 鬼谋','45'),('史记','小沛','政治+5','20'),('青囊书','小沛','寿命延长','45'),('牛灯','许昌','监视','15'),('九鼎神丹经','许昌','智力+2','15'),('魏公子兵法','邺城','统率+3 练兵','25'),('枣木槊','永安','武力+1','5'),('双股剑','蓟','武力+3','5'),('青龙偃月刀','涿','武力+20','60'),('丈八蛇矛','涿','武力+30','60');"
  • 希望直接用SQL的方式，而不是使用编程语言完成

把一个字段分割成多个字段，用SQL的 SPLIT_PART 函数。

假设想把 "effects" 列分成 "effect1" 和 "effect2"，SQL语句：

  1. 先添加新的列 "effect1" 和 "effect2" 到你的表中：
    ALTER TABLE items ADD COLUMN effect1 VARCHAR(30);
ALTER TABLE items ADD COLUMN effect2 VARCHAR(30);
  2. 再用 SPLIT_PART 函数填充新的列：
UPDATE items SET effect1 = SPLIT_PART(effects, ' ', 1), effect2 = SPLIT_PART(effects, ' ', 2);

这会分割 "effects" 列中的字符串，并把结果分别放入 "effect1" 和 "effect2" 中。

  1. 最后如果你不再需要 "effects" 列，可以删除：
    ALTER TABLE items DROP COLUMN effects;
    主要适用于 PostgreSQL 数据库。

-- 首先新增字段 name1 name2 ，然后执行如下sql拆分，更新

UPDATE items
    SET name1 = substring(effects, '^\S+'),
        lname2 = substring(effects, '^\S+\s+(.*)')

第一步

CREATE TABLE items_new as  
SELECT id, name, city,   
   unnest(string_to_array(effects, ' ')) as effect, 
   value   
FROM items;

DROP TABLE items;

ALTER TABLE items_new   
   ADD COLUMN effect1 VARCHAR(30),
   ADD COLUMN effect2 VARCHAR(30);

UPDATE items_new  
  SET effect1 = split_part(effect, ' ', 1),
      effect2 = split_part(effect, ' ', 2)

第二步

-- 清理多余列
ALTER TABLE items_new 
   DROP COLUMN effect;

提供一个思路：

#把effects列重命名为new_column1，并添加一个名为new_column2的新列。可以在上述SQL语句中指定需要的列名和数据类型。
ALTER TABLE items  
RENAME COLUMN effects TO new_column1,  
ADD COLUMN new_column2 VARCHAR(30);  
UPDATE items  
SET new_column2 = effects;  
ALTER TABLE items  
DROP COLUMN effects;
不知道你这个问题是否已经解决, 如果还没有解决的话:
  • 以下回答来自chatgpt:

    我可以提供一种使用SQL语句将单列拆分成两列的方法。首先，我们需要创建一个新表格来存储拆分后的数据，并且将原始表格的数据插入到新表格中。然后，我们使用字符串函数和正则表达式来从effects列中提取出effect1和effect2的值并插入到新表格的对应列中。

    以下是一个示例解决方案：

    1. 创建一个新表格来存储拆分后的数据： sql CREATE TABLE new_table ( id SERIAL PRIMARY KEY, effect1 VARCHAR(255), effect2 VARCHAR(255) );

    2. 将原始表格的数据插入到新表格中： sql INSERT INTO new_table (effect1, effect2) SELECT CASE WHEN split_part(effects, ' ', 1) <> '' THEN split_part(effects, ' ', 1) END, CASE WHEN split_part(effects, ' ', 2) <> '' THEN split_part(effects, ' ', 2) END FROM original_table;

    3. 删除原始表格的effects列（可选）： sql ALTER TABLE original_table DROP COLUMN effects;

    请注意，上述示例假设原始表格名为original_table，新表格名为new_table，effect1和effect2的数据类型为VARCHAR(255)。您需要根据实际情况进行修改。

    如果您的数据库不是PG数据库，那么您需要根据相应数据库的语法进行调整。此外，如果effects列中的数据含有其他特殊字符或需要更复杂的处理逻辑，您可能需要修改SQL语句中的split_part函数和正则表达式来适应您的情况。

    希望以上解决方案能对您有所帮助。如果您有任何其他问题，请随时提问。


如果你已经解决了该问题, 非常希望你能够分享一下解决方案, 写成博客, 将相关链接放在评论区, 以帮助更多的人 ^-^

可以使用REGEXP：

SELECT effects,TRIM(REGEXP_REPLACE(effects1, '(\(\d+%\))$', '')) AS test_column 
     ,(REGEXP_MATCHES(effects2, '(\d+%)\)$'))[1] AS percentage_column 
FROM t;

你的需求应该是将一个字段值拆分两个字段。

SELECT   
 original_table.*,   
 (SELECT SUBSTRING_INDEX(effects, ' ', -1)) AS effects1,   
 (SELECT SUBSTRING_INDEX(effects, ' ', -2)) AS effects2,   
FROM   
 original_table;

一个名为my_table的表，其中包含一个名为my_column的列，现在需要将该列拆分为两个新列new_col1和new_col2


SELECT my_column AS new_col1, SUBSTRING(my_column FROM 6 FOR LENGTH(my_column) - 5) AS new_col2
FROM my_table;

可以字符串分割成临时表

引用chatgpt内容作答：
要将"effects"列拆分成"effect1"和"effect2"两列，可以使用以下SQL语句：

-- 添加effect1和effect2列
ALTER TABLE items ADD COLUMN effect1 VARCHAR(20);
ALTER TABLE items ADD COLUMN effect2 VARCHAR(20);

-- 更新effect1和effect2的值
UPDATE items
SET effect1 = split_part(effects, ' ', 1),
    effect2 = CASE WHEN effects LIKE '% %' THEN split_part(effects, ' ', 2) ELSE NULL END;

-- 删除原来的effects列
ALTER TABLE items DROP COLUMN effects;

这个SQL脚本首先在"items"表中添加了"effect1"和"effect2"两个新列，然后使用UPDATE语句将原来的"effects"列的值拆分到新的列中，最后删除原来的"effects"列。

请注意，这个脚本假设每个"effects"列的属性之间是用空格分隔的。如果属性之间使用其他分隔符，需要相应地修改split_part函数的第二个参数。

执行完上述SQL语句后，"items"表的结构将如下所示：

------------------------------------------------
| id | name    | city   | value | effect1 | effect2 |
------------------------------------------------
| 1  | 短戟     | 北海  | 10    | 武力+2  |         |
| 2  | 司马法  | 北海  | 50    | 统率+7  | 军师    |
| 3  | 墨子     | 北海  | 30    | 统率+4  | 攻城    |
| ... | ...   | ...    | ...    | ...     | ...     |
------------------------------------------------

每个项目的效果现在分别存储在"effect1"和"effect2"列中。如果原始的"effects"列中只有一个属性，"effect2"列将为NULL。

以下答案参考newbing,回答由博主波罗歌编写：
可以使用 PostgreSQL 中的字符串函数和处理数据的方式，将 effects 列中的多个属性拆分成两列。

首先，我们可以使用 split_part 函数将 effects 列按照空格进行拆分，得到具体属性的列表。然后，使用 CASE WHEN 语句将这些属性转移到 effect1 和 effect2 列中。如果有两个及以上的属性，将第一个属性赋值给 effect1，将第二个属性赋值给 effect2，其他情况下（只有一个属性或者没有属性）将对应列设置为 NULL。

以下是相应的 SQL 代码示例：

ALTER TABLE items ADD COLUMN effect1 VARCHAR(20);
ALTER TABLE items ADD COLUMN effect2 VARCHAR(20);

UPDATE items SET
  effect1 = CASE WHEN split_part(effects, ' ', 1) IS NOT NULL THEN split_part(effects, ' ', 1) ELSE NULL END,
  effect2 = CASE WHEN split_part(effects, ' ', 2) IS NOT NULL THEN split_part(effects, ' ', 2) ELSE NULL END;

执行以上 SQL 代码，会向 items 表中添加 effect1 和 effect2 两列，并根据原始数据的 effects 列的属性进行拆分和赋值。

注意，以上 SQL 代码会修改数据库结构，添加新列，并更新现有数据的新列值。请先备份现有数据，并谨慎操作。
如果我的回答解决了您的问题，请采纳！