sql - 如何返回数据库中现有表中某一行的特定列值？

Question

问题:

我在 PostgreSQL 9.0 中工作，我很难弄清楚如何处理您想要返回特定行的特定列值以用于 CASE WHEN THEN 语句的情况。

我想基本上进入并设置表 A 的值:someRow 的 someColumn 值，等于表 B 的值:行 X 的列 A 值，给定行 X 的列 B 的值。(更多细节在“背景信息”(如果需要理解问题)

这是我想做的(但不知道怎么做):

Update tableA
Set someColumn 
    CASE WHEN given_info_column = 'tableB: row X's column B value' 
    THEN (here I want to return row X's column A value, finding row X using the given column B value) 
    ELSE someColumn END

背景信息:(可选，用于说明)

假设在现有数据库中有一个用户事件表和一个设备表，其中已经存在的事件执行字符串存在于您正在使用的代码库中:(例如)

User_Activity:
id (int) | user_name (string)    | activity_preformed (string)            | category (string) 
---------|-----------------------|----------------------------------------|------------------
1        | Joe Martinez          | checked out iphone: iphone2            | dvc_activity
2        | Jon Shmoe             | uploads video from device: (id: 12345) | dvc_activity
3        | Larry David           | goes to the bathroom                   |other_activity

Device:
seq (int)| device_name (string)  | device_srl_num (int) | device_status (string)|
---------+-----------------------+----------------------+-----------------------+
1        | iphone1               | 12344                | available
2        | iphone2               | 12345                | checked out
3        | android1              | 23456                | available

老板给你的任务是创建一个报告，在一个表格中显示所有设备事件，如下所示:

Device Activity Report
(int)     (int)    (string)       (string)                                 (string)      (int)         (string)
act_seq  |usr_id | usr_name     | act_performed                         |  dvc_name | dvc_srl_num | dvc_status  
---------+-------+--------------+---------------------------------------+-----------+-------------+------------
1        |1      | Joe Martinez | Checked out iphone: iphone2           | iphone2   | 12345       | checked out 
2        |2      | John Shmoe   | uploads video from device: (id: 12345)| android1  | 23456       | available    

为了这个问题的目的，这必须通过向名为 dvc_seq 的用户事件表添加一个新列来完成，这将是设备表的外键。您将通过从用户事件表中查询并加入其中 User_Activity (dvc_seq) = Device (seq) 的两个来创建一个临时表

这很好，对于 User_Activity 表中的新条目非常有用，它将记录链接到关联设备的 dvc_seq 如果事件涉及设备。

问题是您需要在 User_Activity 表中的新 dvc_seq 列中为所有先前与设备相关的条目填写值。由于之前的程序员决定在某些时候使用序列号在 activity_performed 列中指定哪个设备，而其他时候使用设备名称，这会带来一个有趣的问题，您需要根据设备的名称从设备中导出关联的设备序列号或序列号。

所以再一次，我想做的是:(使用这个例子)

UPDATE User_Activity 
    SET dvc_seq 
    CASE WHEN activity_performed LIKE 'checked out iphone:%' 
    THEN (seq column of Device table) 
        WHERE (SELECT 1 FROM Device WHERE device_name = (substring in place of the %))
    ELSE dvc_seq (I think this would be null since there would be nothing here yet)  
    END

你们谁能帮我完成这个？？预先感谢所有回复和建议!

Answer 1

当序列号或名称包含在 activity_performed 中时，下面的查询使用更新连接来更新序列号

UPDATE UserActivity
SET a.dvc_seq = b.seq
FROM UserActivity AS a
JOIN devices b 
    ON UserActivity.activity_performed LIKE '%from device: (id: ' || b.serial_num || '%'
    OR UserActivity.activity_performed LIKE '%: ' || b.name || '%'