从子选择返回行

发布于 2024-10-18 00:13:53 字数 488 浏览 5 评论 0原文

我有这个 MySQL DB 方案：

users (id, login)
coins (userid, value, curr)

我需要编写 select 它将返回结果：登录名和他拥有的最大硬币以及该硬币的货币。

我尝试过类似的操作：

SELECT login, 
(
  SELECT value, curr
  FROM coins 
  WHERE coins.userid = users.id
  ORDER BY value DESC
  LIMIT 1
) AS ROW(value, curr)
FROM users

它不起作用...我会收到错误，“操作数应包含 1 列”。我期待它，但我不知道任何方法，如何做到这一点。

所以我想：有没有办法从子查询返回多列单行（行）到父查询？

（是的，我可以使用两个子查询，但效果不佳。）

感谢您的宝贵时间。

原文

I have this MySQL DB scheme:

users (id, login)
coins (userid, value, curr)

I need to write select which will return result: login and max coin he have and currency of this coin.

I've tryed something like that:

SELECT login, 
(
  SELECT value, curr
  FROM coins 
  WHERE coins.userid = users.id
  ORDER BY value DESC
  LIMIT 1
) AS ROW(value, curr)
FROM users

Its not working... I'll recieve error, that "Operand should contain 1 column(s)".
I expected it, but I dont know any way, how to make it.

So i guess: Is there any way to return multiple-column-single-line (row) from subquery to parent query?

(Yes, I can use two subqueries, but its not effective.)

Thanks for your time.

分享到QQ

分享到微博

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

发布评论

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

一片旧的回忆 2024-10-25 00:13:53

SELECT u.login, g.MaxVal, c.curr
FROM users u JOIN coins c ON u.id = c.userid 
  JOIN (
    SELECT userid, MAX(`Value`) MaxVal
    FROM coins
    GROUP BY userid
  ) g ON c.userid = g.userid AND c.Value = g.MaxVal

在平局的情况下，上面的查询将返回所有价值最高的硬币，如果您只想选择其中 1 个硬币，您可以在外部查询中添加一个 GROUP BY ：

SELECT u.login, g.MaxVal, c.curr
FROM users u JOIN coins c ON u.id = c.userid 
  JOIN (
    SELECT userid, MAX(`Value`) MaxVal
    FROM coins
    GROUP BY userid
  ) g ON c.userid = g.userid AND c.Value = g.MaxVal 
GROUP BY c.userid

SELECT u.login, g.MaxVal, c.curr
FROM users u JOIN coins c ON u.id = c.userid 
  JOIN (
    SELECT userid, MAX(`Value`) MaxVal
    FROM coins
    GROUP BY userid
  ) g ON c.userid = g.userid AND c.Value = g.MaxVal

In a case of ties, the above query will return all coins with the highest value, if you want to only select 1 of the coins, you can add a GROUP BY to the outer query:

SELECT u.login, g.MaxVal, c.curr
FROM users u JOIN coins c ON u.id = c.userid 
  JOIN (
    SELECT userid, MAX(`Value`) MaxVal
    FROM coins
    GROUP BY userid
  ) g ON c.userid = g.userid AND c.Value = g.MaxVal 
GROUP BY c.userid

回复收藏 0 原文

~没有更多了~