sql - 为每一行分配默认值的简单连接表(包括 sql fiddle)

Question

我确信这个问题有一个非常简单的答案，但我很难找到它。

我有一张表，其中包含属于用户的设置。

| user_id | setting  | value  |     
-------------------------------     
| 1       | paying?  | true   |     
| 2       | paying?  | false  |     
| 2       | demo?    | true   |     
| 2       | active?  | true   |

我还有一个包含默认设置的表格，以防特定用户不存在该设置。

| setting  | value  |
---------------------
| paying?  | true   |
| demo?    | true   |
| active?  | false  |
| hidden?  | false  |

我需要做的是返回每个用户的所有设置(仅当没有分配给该用户时才使用默认设置):

| user_id | category | value  |
-------------------------------
| 1       | paying?  | true   |
| 1       | demo?    | true   |
| 1       | active?  | false  |
| 1       | hidden?  | false  |
| 2       | paying?  | false  |
| 2       | demo?    | true   |
| 2       | active?  | true   |
| 2       | hidden?  | false  |

此连接不会为每个用户返回结果。我做错了什么？

SELECT u.user_id, COALESCE(u.category, g.category) as category, COALESCE(u.value, g.value) as value
FROM user_settings as u
FULL OUTER JOIN global_settings as g on u.category = g.category
ORDER BY u.user_id, u.category

SQLFiddle here

Answer 1

使用 global_settings 交叉连接不同的用户，并将 user_settings 表左连接到此。

SELECT u.user_id, COALESCE(us.category, g.category) as category,
COALESCE(us.value, g.value) as value
FROM (select distinct user_id from user_settings) as u
cross join global_settings g
left join user_settings as us on us.category = g.category and us.user_id=u.user_id
order by 1,2