postgresql - 使用来自自身的值更新表,但从不同的表中选择标准
问题描述
我有一个只包含外键的联结表,如下所示:
JUNCTION
+-------+-----------+---------+---------+
| id | module_id | comp_id | ins_id |
+-------+-----------+---------+---------+
| 1 | 1 | 2 | 8 |
| 2 | 2 | 4 | 9 |
| 3 | 3 | 4 | 10 |
| 4 | 4 | 1 | 10 |
| 5 | 3 | 5 | 11 |
| 6 | 4 | 1 | 11 |
| 7 | 5 | 42 | 11
+-------+-----------+---------+---------+
引用的表之一是modules
这样的:
MODULES
+-----------+---------+---------+
| id | name | version |
+-----------+---------+---------+
| 1 | default | 1 |
| 2 | bar | 1 |
| 3 | foo | 1 |
| 4 | foo | 3 |
| 5 | foo | 2 |
+-----------+---------+---------+
SQL 中是否有办法更新此表,以便连接表中comp_id
的连接 id4
更新为comp_id 4
(与模块 3 相同的名称,但版本更高),并且连接 id6
获取 comp_id 42
,即comp_id
前面的模块版本 (2),因为它们具有匹配的模块名称和ins_id等等,对于具有相同条件的所有记录?
在散文中:更新联结表中所有具有 comp_id 1 的模块引用,使用具有匹配模块名称的模块的 comp_id 更新它们,将其设置为具有下一个最小版本的模块的 comp_id,并匹配ins_id
(并且可以是受联结表中其他列的约束)
或者,这会在业务逻辑中以编程方式更好地处理吗?
解决方案
我相信我已经解决了:答案是窗口函数(尽管某些连接组合可能能够做到这一点。我已经接近了,但认为这会更优雅和易于理解)
首先,我必须创建一个 CTE,它将模块名称和版本映射到联结表中的值:
WITH module_mapping AS (SELECT j.module_id, j.ins_id, j.comp_id, m.name, m.version
FROM junction AS j
JOIN modules AS m ON m.id = j.module_id
)
使用该 CTE,我可以创建一个窗口函数,该函数将选择具有相同模块的记录:
, win AS (SELECT name, version, module_id, ins_id, comp_id, first_value(comp_id)
OVER (PARTITION BY name, ins_id ORDER BY version DESC
ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING)
FROM module_mapping)
因此,当我进行以下查询时,我可以看到该first_value
列包含我要查找的记录:
WITH module_mapping AS (SELECT j.module_id, j.ins_id, j.comp_id, m.name, m.version
FROM junction AS j
JOIN modules AS m ON m.id = j.module_id
)
, win AS (SELECT name, version, module_id, ins_id, comp_id, first_value(comp_id)
OVER (PARTITION BY name, ins_id ORDER BY version DESC
ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) -- this key part excludes the current row, since it will have the largest (newest) module version
FROM module_mapping)
SELECT * FROM win WHERE comp_id = 1;
+-------+---------+-----------+--------+---------+-------------+
| name | version | module_id | ins_id | comp_id | first_value |
+-------+---------+-----------+--------+---------+-------------+
| "foo" | 3 | 4 | 10 | 1 | 4 |
| "foo" | 3 | 4 | 11 | 1 | 42 |
+-------+---------+-----------+--------+---------+-------------+
所以有了这两个 CTE,我可以调用我的更新 sql:
UPDATE junction AS j
SET comp_id = win.new_comp
FROM win
WHERE j.comp_id = 1 -- only change the default ones
AND j.module_id = win.module_id
AND j.ins_id = win.ins_id;
推荐阅读
- django - 当我使用 NON-gmail smtp 服务器通过 django 发送电子邮件时,我得到了 Google SMTPAuthenticationError,即使我不使用 smtp.gmail.com
- php - 使用php从数据库中获取多维数组时出错
- javascript - 如何在javascript中扁平化对象值
- javascript - React-Router open 新选项卡内容中的链接正在同一个现有选项卡中打开
- python - python通过递归函数获取所有子集
- phpstorm - 如何在 PhpStorm 中设置默认 PHP 版本/解释器?
- arrays - 是否可以在 2 个 for 循环中存储数据
- kotlin - Kotlin 空检查 !! 不短路评价?
- c# - 如何在 C# 中通过 MathNet 查找函数的值
- api - 在 Magento 中,我可以仅在 Magento REST API 上构建我的店面吗?