建立视图，对字符串进行筛选

使用的是postgres数据库，基础表格如下所示

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'),('老君入仙符','长沙','智力+8 寿命延长','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');"
  • 希望建立一个视图
  • 视图中有几列，1.item_id 2.item_name 3.武力增强值 4. 智力增强值 5.统御增强值 6.政治增强值

sql

CREATE VIEW item_view AS 
SELECT 
    id AS item_id,
    name AS item_name,
    (CASE WHEN effects LIKE '%武力%' THEN CAST(SUBSTRING(effects FROM '[0-9]+') AS INT) ELSE 0 END) AS 武力增强值,
    (CASE WHEN effects LIKE '%智力%' THEN CAST(SUBSTRING(effects FROM '[0-9]+') AS INT) ELSE 0 END) AS 智力增强值,
    (CASE WHEN effects LIKE '%统率%' THEN CAST(SUBSTRING(effects FROM '[0-9]+') AS INT) ELSE 0 END) AS 统御增强值,
    (CASE WHEN effects LIKE '%政治%' THEN CAST(SUBSTRING(effects FROM '[0-9]+') AS INT) ELSE 0 END) AS 政治增强值
FROM items;

这个视图通过对items表中的effects文本进行匹配,提取出数字作为各属性的增强值,如果没有匹配到则为0。
查看视图:
sql

SELECT * FROM item_view;

这样就得到了所需的包括item_id, item_name和四种属性增强值的视图。

是SQL SERVER还是其他数据库语言，不同版本的SQL思路一样，不同的是写法


CREATE VIEW view AS
SELECT id AS item_id, name AS item_name,
       CASE WHEN effects LIKE '%武力%' THEN SUBSTRING(effects, POSITION('武力' IN effects) + 3, POSITION(' ' IN SUBSTRING(effects, POSITION('武力' IN effects) + 3)))::int ELSE NULL END AS 武力增强值,
       CASE WHEN effects LIKE '%智力%' THEN SUBSTRING(effects, POSITION('智力' IN effects) + 3, POSITION(' ' IN SUBSTRING(effects, POSITION('智力' IN effects) + 3)))::int ELSE NULL END AS 智力增强值,
       CASE WHEN effects LIKE '%统率%' THEN SUBSTRING(effects, POSITION('统率' IN effects) + 3, POSITION(' ' IN SUBSTRING(effects, POSITION('统率' IN effects) + 3)))::int ELSE NULL END AS 统御增强值,
       CASE WHEN effects LIKE '%政治%' THEN SUBSTRING(effects, POSITION('政治' IN effects) + 3, POSITION(' ' IN SUBSTRING(effects, POSITION('政治' IN effects) + 3)))::int ELSE NULL END AS 政治增强值
FROM items;

你可以用下面这个SQL查询语句来创建视图，这个视图将从原始表格中选取相关列，并对字符串进行筛选和计算，以便提取武力、智力、统御和政治增强值的数值部分：

CREATE VIEW item_effects AS
SELECT id AS item_id, name AS item_name,
  CASE WHEN effects LIKE '%武力+%' THEN substring(effects, '武力+\d+')::integer ELSE 0 END AS attack_bonus,
  CASE WHEN effects LIKE '%智力+%' THEN substring(effects, '智力+\d+')::integer ELSE 0 END AS intelligence_bonus,
  CASE WHEN effects LIKE '%统率+%' THEN substring(effects, '统率+\d+')::integer ELSE 0 END AS command_bonus,
  CASE WHEN effects LIKE '%政治+%' THEN substring(effects, '政治+\d+')::integer ELSE 0 END AS politics_bonus
FROM items;

上面这个视图中，第1列是 "item_id"，对应原始表格中的 "id" 列；第2列是 "item_name"，对应原始表格中的 "name" 列；第3-6列是武力、智力、统御和政治增强值，根据 "effects" 列中的字符串进行筛选和计算得到。如果 "effects" 列中不包含对应的增强值，则该列的值为 0。

如果以上回答对您有所帮助，点击一下采纳该答案～谢谢


create or replace view items_view as 
select 
    id as item_id, 
    name as item_name, 
    cast(substring(effects from '武力\+(\d+)') as integer) as power_increase,
    cast(substring(effects from '智力\+(\d+)') as integer) as intelligence_increase,
    cast(substring(effects from '统率\+(\d+)') as integer) as command_increase,
    cast(substring(effects from '政治\+(\d+)') as integer) as politics_increase
from items;

To create a view with the desired columns, you can use the following SQL query:

CREATE VIEW item_view AS
SELECT id AS item_id, name AS item_name,
  (CASE WHEN effects LIKE '%武力%' THEN substring(effects FROM '武力\\+(\\d+)')::int ELSE 0 END) AS 武力增强值,
  (CASE WHEN effects LIKE '%智力%' THEN substring(effects FROM '智力\\+(\\d+)')::int ELSE 0 END) AS 智力增强值,
  (CASE WHEN effects LIKE '%统率%' THEN substring(effects FROM '统率\\+(\\d+)')::int ELSE 0 END) AS 统御增强值,
  (CASE WHEN effects LIKE '%政治%' THEN substring(effects FROM '政治\\+(\\d+)')::int ELSE 0 END) AS 政治增强值
FROM items;

This query creates a view named item_view based on the items table. It selects the id column as item_id, the name column as item_name, and uses the effects column to extract the enhancement values for 武力, 智力, 统御, and 政治. The enhancement values are extracted using regular expressions and converted to integers. If an enhancement value is not present in the effects column, it defaults to 0.

Now you can query the item_view to retrieve the desired columns:

SELECT item_id, item_name, 武力增强值, 智力增强值, 统御增强值, 政治增强值
FROM item_view;

This will give you the view with the specified columns: item_id, item_name, 武力增强值, 智力增强值, 统御增强值, and 政治增强值.