MONGODB:转换SQL Union查询 [英] MONGODB: Convert SQL Union query

问题描述

我正在从MySql转换为MongoDB.现在，我找不到该查询的替换项:

I'm converting from MySql to MongoDB. Now I can't find a replace for this query:

(SELECT id, name, (score_kills - score_deaths) AS points FROM player ORDER BY points DESC LIMIT 10)
UNION
(SELECT id, name, (score_kills - score_deaths) AS points FROM player ORDER BY points ASC LIMIT 10);

这将选择10个最佳和最差玩家，并按计算出的字段points进行排序. 有人可以帮我吗?

This selects the 10 best and worst players sorted by the calculated field points. Can anyone help me with this?

MongoDB播放器中的文档如下:

The documents in player in MongoDB look like this:

{
    _id: ObjectId("1234567890"),
    name: "foo",
    score_kills: 321,
    score_deaths: 43
}

推荐答案

您可以使用聚合框架进行此操作.在一个查询中执行此操作并不是特别有效，因此您可能应该执行与在运行两个查询的SQL中相同的操作-唯一的区别是MongoDB不会为它们联合"您，则必须在应用程序中完成该操作.

You can do this with aggregation framework. It won't be particularly efficient to do it in one query, so you should probably just do the same thing that you're doing in SQL which is running two queries - the only difference is that MongoDB won't "union" them for you, and you'll have to do it in the application.

两个聚合查询将是:

db.collection.aggregate( [
        {$project:{name:1, points:{$subtract:["$score_kills","$score_deaths"]}}},
        {$sort:{points:-1}},
        {$limit:10}
]);

这将使您进入前10名.要获得前10名，您将做相同的事情，但按{points:1}排序将使您排名前10名.

This would give you the top 10. To get the bottom 10 you would do the same thing but sort by {points:1} which will leave you with bottom 10.

这篇关于MONGODB:转换SQL Union查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！