基于选择列内容的动态联接（postgres）

debugcn 发表于 Dev

赤壁

这个问题是针对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 |
+--------------+

主查询从配置文件表中提取特定的行，在此示例中，标识为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在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表，或者是否可以将其他一些预写函数馈入oslist表（就像开关箱之类的东西）？

谢谢！

迈尔斯·埃兰

任何单个查询都不能返回不同数量的列。但是，您可以包括所有可能的值，也可以将结果作为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" } |
+----------------------------------------------------------------------------------+

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。