将两个MYSQL查询列结果合二为一在ORDER BY中使用

debugcn 发表于 Dev

萨贾德·侯赛因·萨戈尔

我有两个查询来获取用户硬币和宝石的价值。我可以单独获取每个字段值，但我希望它们一起作为附加值..（硬币+宝石）值在 ORDER BY 中使用

使用此 SQL 查询，我可以获得硬币字段值...

SELECT user_id, meta_value as total_coins FROM wp_usermeta WHERE meta_key = '_coin' AND meta_value > 0 ORDER BY meta_value+0 DESC LIMIT 0,10

硬币查询结果

并使用它来获取 gems 字段值...

SELECT user_id, meta_value as total_gems FROM wp_usermeta WHERE meta_key = '_gems' AND meta_value > 0 ORDER BY meta_value+0 DESC LIMIT 0,10

宝石查询结果

我想要的是获取_coin字段值，然后为那个 user_id 获取_gem字段值，然后将这两个值合二为一......比如 690+21 = 711 作为 total_points .. 像这样的东西..

`SELECT ( 

    SELECT meta_value FROM `wp_usermeta` as ue1

    WHERE meta_key = '_coin' AND meta_value > 0 ORDER BY meta_value+0 DESC LIMIT 0,50

) AS coins,
( 

    SELECT meta_value FROM `wp_usermeta` as ue2 

    WHERE ue2.user_id = ue1.user_id AND meta_key = '_gem' AND meta_value > 0 ORDER BY meta_value+0 DESC LIMIT 0,50

) AS gems,

coins+gems AS total_points

FROM wp_usermeta

ORDER BY total_points LIMIT 0, 50`

我知道这个 SQL 不正确，这就是为什么我需要帮助..请帮我纠正这个 SQL

scaisEdge

您应该使用连接同一个表并重复添加代码，而不是使用多个子查询

  SELECT ue1.meta_value coins , ue2.meta_value gems
       , ue1.meta_value + ue2.meta_value total_points
  FROM `wp_usermeta` as ue1
  INNER JOIN `wp_usermeta` as ue2 ON ue1.user ue2.user_id = ue1.user_id
      AND ue1.meta_key = '_coin' 
        AND ue2. meta_key = '_gem'
  WHERE ue1.meta_value > 0 
  AND   ue2.meta_value > 0 
  ORDER BY total_points LIMIT 0, 50

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。