在MySQL的两个表中选择查询

小编典典

在MySQL的两个表中选择查询

sql

我正在运行此查询

select * from user_meta JOIN user ON user_meta.userid=user.userid where user_meta.userid=9

但这不是我想要的结果，它返回多行。

我的一张桌子看起来像这个名字user_meta：

umeta_id     userid      meta_key       meta_value
 1              9         mobile        123324
 2              9         address       some address
 3              9         city          some city
 4              9         country       some country
 5              9         occupation    some details
 6              9         website       someurl
 7              9         mobile        123324
 8              9         address       some address
 9              9         city          some city
 10             10        country       some country
 11             10        occupation    some details
 12             10        website       someurl

另一个表看起来像这个名字user：

userid          username      fullname      email              role
  9             someuser       john Doe    123324@gmail.com    admin

我如何使其选择查询，以便userid 9可以从两个表中获取与之相关的所有值，并使它看起来像这样

所需的输出：

userid          username      fullname      email              role      Mobile     address     city     country    occupation     website
  9             someuser       john Doe    123324@gmail.com    admin    123123     someaddres    Somecity    somecountry    some details    someurl

Thank you!

阅读 176

2021-04-15

共1个答案

小编典典

为您提供所需的输出（但这并不灵活，因此，
如果您添加要输出的更多详细信息，将无法进行更改而不进行更改）：

SELECT user.userid, 
        user.username, 
        user.fullname, 
        user.email, 
        user.role, 
        mobile_meta.meta_value AS `Mobile`, 
        address_metameta_value AS `Address`, 
        city_metameta_value AS `City`, 
        country_metameta_value AS `Country`, 
        occupation_metameta_value AS `Occupation`, 
        website_metameta_value AS `Website`
FROM user
LEFT OUTER JOIN user_meta AS mobile_meta ON mobile_meta.userid=user.userid AND mobile_meta.meta_key = 'mobile'
LEFT OUTER JOIN user_meta AS address_meta ON address_meta.userid=user.userid AND address_meta.meta_key = 'address'
LEFT OUTER JOIN user_meta AS city_meta ON city_meta.userid=user.userid AND city_meta.meta_key = 'city'
LEFT OUTER JOIN user_meta AS country_meta ON country_meta.userid=user.userid AND country_meta.meta_key = 'country'
LEFT OUTER JOIN user_meta AS occupation_meta ON occupation_meta.userid=user.userid AND occupation_meta.meta_key = 'occupation'
LEFT OUTER JOIN user_meta AS website_meta ON website_meta.userid=user.userid AND website_meta.meta_key = 'website'
WHERE user.userid=9

2021-04-15