使用多表子句将多行值选择为单行 [英] Select multiple row values into single row with multi-table clauses
问题描述
我已经搜索了论坛，并且看到类似的帖子，但它们仅解决了我需要制定的完整查询的一部分（array_aggr，存在的地方，加入的地方等等）。如果我发布的问题已得到解答，我将很乐意接受对这些线程的引用。
I've searched the forums and while I see similar posts, they only address pieces of the full query I need to formulate (array_aggr, where exists, joins, etc.). If the question I'm posting has been answered, I will gladly accept references to those threads.
我确实找到了此线程 ...与我需要的线程非常相似，除了它是针对MySQL的，我一直在尝试将其纳入psql语法时遇到错误。希望有人可以帮助我将所有事情都汇集在一起。情况如下：
I did find this thread ...which is very similar to what I need, except it is for MySQL, and I kept running into errors trying to get it into psql syntax. Hoping someone can help me get everything together. Here's the scenario:
属性
attrib_id | attrib_name
UserAttribute
user_id | attrib_id | value
下面是数据看起来像的一个小例子：
Here's a small example of what the data looks like:
属性
attrib_id | attrib_name
-----------------------
1 | attrib1
2 | attrib2
3 | attrib3
4 | attrib4
5 | attrib5
UserAttribute -每位最多可以有15个attrib_id /值user_id
UserAttribute -- there can be up to 15 attrib_id's/value's per user_id
user_id | attrib_id | value
----------------------------
101 | 1 | valueA
101 | 2 | valueB
102 | 1 | valueC
102 | 2 | valueD
103 | 1 | valueA
103 | 2 | valueB
104 | 1 | valueC
104 | 2 | valueD
105 | 1 | valueA
105 | 2 | valueB
这就是我想要的
结果
user_id | attrib1_value | attrib2_value
--------------------------------------------------------
101 | valueA | valueB
102 | valueC | valueD
103 | valueA | valueB
104 | valueC | valueD
105 | valueA | valueB
如图所示，我正在寻找包含以下内容的单行：
-user_id from UserAttribute表
-UserAttribute表中的属性值
As shown, I'm looking for single rows that contain: - user_id from the UserAttribute table - attribute values from the UserAttribute table
注意：对于属性表中的两个特定属性名称，我只需要UserAttribute表中的属性值
Note: I only need attribute values from the UserAttribute table for two specific attribute names in the Attribute table
再次感谢您对现有解决方案的帮助或参考。
Again, any help or reference to an existing solution would be greatly appreciated.
更新：
@ronin提供了获取所需结果的查询：
@ronin provided a query that gets the results desired:
SELECT ua.user_id
,MAX(CASE WHEN a.attrib_name = 'attrib1' THEN ua.value ELSE NULL END) AS attrib_1_val
,MAX(CASE WHEN a.attrib_name = 'attrib2' THEN ua.value ELSE NULL END) AS attrib_2_val
FROM UserAttribute ua
JOIN Attribute a ON (a.attrib_id = ua.attrib_id)
WHERE a.attrib_name IN ('attrib1', 'attrib2')
GROUP BY ua.user_id;
基于此，我尝试在 WHEN条件下添加一些 LIKE模式匹配（相对于ua.value），但所有内容最终均以 FALSE值结尾。将开始一个新的问题，看看是否可以合并，如果我不知道的话。谢谢大家的帮助！
To build on that, I tried to add some 'LIKE' pattern matching within the 'WHEN' condition (against the ua.value), but everything ends up as the 'FALSE' value. Will start a new question to see if that can be incorporated if I cannot figure it out. Thanks all for the help!!
推荐答案
如果每个用户的每个属性只有一个值，则可以从创建一个稀疏矩阵：
If each attribute only has a single value for a user, you can start by making a sparse matrix:
SELECT user_id
,CASE WHEN attrib_id = 1 THEN value ELSE NULL END AS attrib_1_val
,CASE WHEN attrib_id = 2 THEN value ELSE NULL END AS attrib_2_val
FROM UserAttribute;
然后使用聚合函数压缩矩阵：
Then compress the matrix using an aggregate function:
SELECT user_id
,MAX(CASE WHEN attrib_id = 1 THEN value ELSE NULL END) AS attrib_1_val
,MAX(CASE WHEN attrib_id = 2 THEN value ELSE NULL END) AS attrib_2_val
FROM UserAttribute
GROUP BY user_id;
响应评论，按属性名称而不是id搜索：
In response to the comment, searching by attribute name rather than id:
SELECT ua.user_id
,MAX(CASE WHEN a.attrib_name = 'attrib1' THEN ua.value ELSE NULL END) AS attrib_1_val
,MAX(CASE WHEN a.attrib_name = 'attrib2' THEN ua.value ELSE NULL END) AS attrib_2_val
FROM UserAttribute ua
JOIN Attribute a ON (a.attrib_id = ua.attrib_id)
WHERE a.attrib_name IN ('attrib1', 'attrib2')
GROUP BY ua.user_id;
这篇关于使用多表子句将多行值选择为单行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
