postgresql - 基于选择列内容的动态连接(postgres)

Question

这个问题是针对 postgres 的，但我只知道如何编写基本的 mySQL，所以请原谅我解释中的语法错误。 (从另一种技术(mongo)切换，需要知道 postgres 是否可以适应)。

以下是一些示例表:

table ``profile``
+---------------------------------------+
| id  | name       | ver   | os | os_id |
+---------------------------------------+
| 221 | test1      | 0.0.1 | 1  | 12    |
| 222 | test2      | 0.0.2 | 2  | 13    |
| 223 | helloworld | 1.0.0 | 2  | 12    |
+---------------------------------------+

table ``linux``
+------------------------+
| id  | distro | env     |
+------------------------+
| 12  | arch   | openbox |
| 13  | debian | kde     |
+------------------------+

table ``windows``
+-----------------------+
| id | release    | sp  |
+-----------------------+
| 8  | Windows 8  | sp1 |
| 9  | Windows 10 | sp2 |
+-----------------------+

table ``android``
+---------------------------------+
| id | release     | image | root |
+---------------------------------+
| 12 | lollipop    | uri   | 1    |
| 13 | marshmallow | uri   | 0    |
+---------------------------------+

table ``oslist``
+--------------+
| id | name    |
+--------------+
| 1  | android |
| 2  | linux   |
| 3  | windows |
+--------------+

主查询从配置文件表中提取特定行，在本例中为 id 223:

SELECT profile.id, profile.name, profile.ver, profile.os, profile.os_id FROM profile WHERE profile.id=223 ...

但我还需要在结果中添加来自相应操作系统的行数据。进行第二个查询并不理想，因此我希望将其作为子查询或 JOIN。

因此在同一个示例中，查询需要理解 profile.os 是 2，并检查 table.oslist 的 id 为 2，看到它是 linux，然后使用它动态连接适当表的名称:JOIN linux ON linux.id=profile.os_id

结果是这样的:

+---------------------------------------+
| name       | ver   | distro | env     |
+---------------------------------------+
| helloworld | 1.0.0 | arch   | openbox |
+---------------------------------------+

如果配置文件 ID 是 221，结果将是这样的:

+----------------------------------------------+
| name       | ver   | release  | image | root |
+----------------------------------------------+
| test1      | 0.0.1 | lollipop | uri   | 1    |
+----------------------------------------------+

这可能吗？是否需要使用 oslist 表，或者是否可以有一些其他可以将其输入的预写函数(比如 switch-case 之类的东西)？

谢谢!

Answer 1

任何单个查询都不能返回不同数量的列。但是，您可以包含所有可能的值或将结果作为 JSON 返回。从好的方面来说，您拥有的不同操作系统数量有限，因此这是可行的。

SELECT
  profile.name, profile.ver,
  oslist.name AS os,
  android.release AS android_release, android.image, android.root,
  linux.distro, linux.env,
  windows.release AS windows_release, windows.sp
FROM profile
  LEFT JOIN oslist ON (oslist.id = profile.os)
  LEFT JOIN android ON (profile.os = 1 AND android.id = profile.os_id)
  LEFT JOIN linux ON (profile.os = 2 AND linux.id = profile.os_id)
  LEFT JOIN windows ON (profile.os = 3 AND windows.id = profile.os_id)
WHERE profile.id = 223

对于 JSON 输出，请尝试以下操作:

SELECT
  jsonb_strip_nulls(
    jsonb_build_object(
      'name', profile.name,
      'version', profile.ver,
      'os', oslist.name,
      'release', coalesce(android.release, windows.release),
      'image', android.image,
      'root', android.root,
      'distro', linux.distro,
      'env', linux.env,
      'sp', windows.sp
    )
  ) AS result
FROM profile
  LEFT JOIN oslist ON (oslist.id = profile.os)
  LEFT JOIN android ON (profile.os = 1 AND android.id = profile.os_id)
  LEFT JOIN linux ON (profile.os = 2 AND linux.id = profile.os_id)
  LEFT JOIN windows ON (profile.os = 3 AND windows.id = profile.os_id)
WHERE profile.id=223

这会给你

+----------------------------------------------------------------------------------+
| result                                                                           |
+----------------------------------------------------------------------------------+
| { "name": "helloworld", "version": "1.0.0", "distro": "arch", "env": "openbox" } |
+----------------------------------------------------------------------------------+