SQL:如何将查询的值用作另一个查询的字段？

Question

用户对象:

  id |  group | object_id 
-----+--------+------------
  1  |  admin | 30
  2  |  user  | 40
  3  |  user  | 50

对象权限:

  id  | name | user   | admin 
------+------+--------+--------
  30  | foo  | false  | true
  40  | bar  | false  | false
  50  | baz  | true   | true

希望以上示例对您有所帮助。我想要类似下面的东西。我想如果有可能会有子查询和可能的 eval。

SELECT u.id
     , o.name
     , o.<u.group>        as permissions
FROM   user_object        u
JOIN   object_permissions o
       ON (u.object_id = o.id);
----------------------------
  id |  name | permissions
-----+-------+--------------
  1  |  foo  | true
  2  |  bar  | false
  3  |  baz  | true

Answer 1

假设 admin/user 是出现在 user_object 组列中的唯一值，试试这个:

SELECT u.id
     , o.name
     , CASE WHEN u.group = 'admin'
          THEN o.admin 
          ELSE o.user
       END AS permissions
FROM   user_object        u
JOIN   object_permissions o
       ON (u.object_id = o.id);