将一列中的多行选择为不同的列(sql)

4zcjmb1e  于 2021-06-20  发布在  Mysql
关注(0)|答案(2)|浏览(336)

我在mysql中有一个类似这样的表wp\u usermeta:(这是一个wordpress应用程序,但我不想通过wordpress与数据库交互,只想通过mysql)

umeta_id    user_id     meta_key      meta_value
-------------------------------------------------------
1           1           nickname      Rymdblomma
2           1           firstname     Nile
3           1           lastname      White
4           1           description   Coolest person

等等。我想要的是选择所有这4个,但放入4列代替。同时,我从其他表中选择数据,创建一个包含所有属性的临时表。

SELECT  wp_users.ID AS wp_id,
        wp_users.user_email AS email,
        wp_users.user_login AS username,
        wp_users.user_registered AS regDate,
        wp_blogs.path AS subname,
        wp_usermeta.meta_value AS firstname, 
        wp_usermeta.meta_value AS lastname
FROM    wp_users
INNER JOIN wp_blogs ON wp_users.ID = wp_blogs.blog_id
LEFT JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id AND 
(wp_usermeta.umeta_id = 2 OR wp_usermeta.umeta_id = 3);

(我想包括所有meta_值,不只是first和lastname,但为了测试目的,我将其限制为2)
返回如下内容:

wp_id    email    username    regDate    subname    firstname    lastname
-------------------------------------------------------------------------
1        blabla   blabla      blabla     blabla     Nile         Nile
1        blabla   blabla      blabla     blabla     White        White

等等。但是我不想要重复的行,相反我想要这样的东西:

wp_id    email    username    regDate    subname    firstname    lastname
-------------------------------------------------------------------------
1        blabla   blabla      blabla     blabla     Nile         White

我试着到处寻找帮助,但还没有找到任何东西。任何帮助都将不胜感激。

h6my8fg2

h6my8fg21#

要从wp\u usermeta获得firstname和lastname到一行中,只需使用不同的umeta\u id=join条件连接表两次

SELECT  wp_users.ID AS wp_id,
        wp_users.user_email AS email,
        wp_users.user_login AS username,
        wp_users.user_registered AS regDate,
        wp_blogs.path AS subname,
        metafirstname.meta_value AS firstname, 
        metalastname.meta_value AS lastname
FROM    wp_users
INNER JOIN wp_blogs ON wp_users.ID = wp_blogs.blog_id
LEFT JOIN wp_usermeta metafirstname ON wp_users.ID = metafirstname.user_id AND metafirstname.umeta_id = 2
LEFT JOIN wp_usermeta metalastname ON wp_users.ID = metalastname.user_id AND metalastname.umeta_id = 3;
nr7wwzry

nr7wwzry2#

一种方法使用条件聚合:

SELECT u.ID AS wp_id, u.user_email AS email, u.user_login AS username,
       u.user_registered AS regDate,
       b.path AS subname,
       MAX(CASE WHEN um.umeta_id = 2 THEN um.meta_value END) as firstname,
       MAX(CASE WHEN um.umeta_id = 3 THEN um.meta_value END) as lastname
FROM wp_users u INNER JOIN
     wp_blogs b
     ON u.ID = b.blog_id LEFT JOIN
     wp_usermeta umf
     ON u.ID = um.user_id AND 
        um.umeta_id IN (2, 3)
GROUP BY u.ID, u.user_email, u.user_login,
         u.user_registered, b.path;

相关问题