一张表，需要来自不同行/元组的多个值

发布于 2024-09-06 14:44:25 字数 1530 浏览 8 评论 0原文

我有这样的表：

'profile_values'
userID | fid     | value  
-------+---------+-------
1      | 3       | [email protected]
1      | 45      | 203-234-2345
3      | 3       | [email protected]
1      | 45      | 123-456-7890

并且：

'users'
userID | name       
-------+-------
1      | joe      
2      | jane     
3      | jake

我想加入它们，并有一行包含两个值，例如：

'profile_values'
userID | name  | email          | phone
-------+-------+----------------+--------------
1      | joe   | [email protected]  | 203-234-2345
2      | jane  | [email protected] | 123-456-7890

我已经解决了它，但感觉很笨拙，我想知道是否有更好的方法来做到这一点。意味着解决方案要么更具可读性，要么更快（优化），要么只是最佳实践。

当前解决方案：选择多个表，许多条件语句：

SELECT u.userID AS memberid,
       u.name AS first_name, 
       pv1.value AS fname,
       pv2.value as lname
FROM  users AS u,
      profile_values AS pv1, 
      profile_values AS pv2,
WHERE u.userID = pv1.userID
  AND pv1.fid = 3
  AND u.userID = pv2.userID
  AND pv2.fid = 45;

感谢您的帮助！

原文

I have tables like:

'profile_values'
userID | fid     | value  
-------+---------+-------
1      | 3       | [email protected]
1      | 45      | 203-234-2345
3      | 3       | [email protected]
1      | 45      | 123-456-7890

And:

'users'
userID | name       
-------+-------
1      | joe      
2      | jane     
3      | jake

I want to join them and have one row with two of the values like:

'profile_values'
userID | name  | email          | phone
-------+-------+----------------+--------------
1      | joe   | [email protected]  | 203-234-2345
2      | jane  | [email protected] | 123-456-7890

I have solved it but it feels clumsy and I want to know if there is a better way to do it. Meaning solutions that are either more readable or faster(optimized) or simply best-practice.

Current solution: multiple tables selected, many conditional statements:

SELECT u.userID AS memberid,
       u.name AS first_name, 
       pv1.value AS fname,
       pv2.value as lname
FROM  users AS u,
      profile_values AS pv1, 
      profile_values AS pv2,
WHERE u.userID = pv1.userID
  AND pv1.fid = 3
  AND u.userID = pv2.userID
  AND pv2.fid = 45;

Thanks for the help!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

画离情绘悲伤 2024-09-13 14:44:25

这是一个典型的数据透视查询：

  SELECT u.userid,
         u.name,
         MAX(CASE WHEN pv.fid = 3 THEN pv.value ELSE NULL END) AS email,
         MAX(CASE WHEN pv.fid = 45 THEN pv.value ELSE NULL END) AS phone,
    FROM USERS u
    JOIN PROFILE_VALUES pv ON pv.userid = u.userid
GROUP BY u.userid, u.name

如果您想查看 PROFILE_VALUES 表中没有任何条目的用户，请在“JOIN”之前添加“LEFT”。

It's a typical pivot query:

  SELECT u.userid,
         u.name,
         MAX(CASE WHEN pv.fid = 3 THEN pv.value ELSE NULL END) AS email,
         MAX(CASE WHEN pv.fid = 45 THEN pv.value ELSE NULL END) AS phone,
    FROM USERS u
    JOIN PROFILE_VALUES pv ON pv.userid = u.userid
GROUP BY u.userid, u.name

Add "LEFT" before the "JOIN" if you want to see users who don't have any entries in the PROFILE_VALUES table.

回复收藏 0 原文