时间戳

首页 > 解决方案 > 查询 MongoDB，在同一查询中使用 GROUP BY 和 INNER JOIN 列出所有唯一的

mongodb - 查询 MongoDB，在同一查询中使用 GROUP BY 和 INNER JOIN 列出所有唯一的

问题描述

我正在做一份大学工作，但我无法在 MongoDB 数据库中进行这些查询，我会将数据库链接放在这里以供那些愿意测试的人使用，因为我没有得到它。

问题：列出当前薪水（即 to_date 等于 9999-01-01）大于 90000 的所有唯一员工的姓氏和名字（使用 GROUP BY 方法），按降序输出两个姓名（按姓氏优先排序）然后是名字）并显示他们当前的薪水（使用 INNER JOIN 方法）。

我在 Studio 3T 上尝试了一些 SQL 转换，但我不知道出了什么问题：

SELECT employees.emp_id, employees.first_name, employees.last_name, salaries.salary FROM employees
INNER JOIN salaries ON employees.emp_id = salaries.emp_id
WHERE to_date=9999-01-01 and salary > 90000
GROUP BY employees.emp_id

部分数据员工：

{ 
    "_id" : ObjectId("5fc157d9ba1b05372b2dacf6"), 
    "birth_date" : "1953-09-02", 
    "emp_id" : NumberInt(10001), 
    "first_name" : "Georgi", 
    "gender" : "M", 
    "hire_date" : "1986-06-26", 
    "last_name" : "Facello", 
    "email_address" : "gfacello@gmail.com"
}
{ 
    "_id" : ObjectId("5fc157d9ba1b05372b2dacf7"), 
    "birth_date" : "1964-06-02", 
    "emp_id" : NumberInt(10002), 
    "first_name" : "Bezalel", 
    "gender" : "F", 
    "hire_date" : "1985-11-21", 
    "last_name" : "Simmel", 
    "email_address" : "fred.bloggs@company.ie"
}
{ 
    "_id" : ObjectId("5fc157d9ba1b05372b2dacf8"), 
    "birth_date" : "1959-12-03", 
    "emp_id" : NumberInt(10003), 
    "first_name" : "Parto", 
    "gender" : "M", 
    "hire_date" : "1986-08-28", 
    "last_name" : "Bamford", 
    "email_address" : "fred.bloggs@company.ie"
}
{ 
    "_id" : ObjectId("5fc157d9ba1b05372b2dacf9"), 
    "birth_date" : "1954-05-01", 
    "emp_id" : NumberInt(10004), 
    "first_name" : "Chirstian", 
    "gender" : "M", 
    "hire_date" : "1986-12-01", 
    "last_name" : "Koblick", 
    "email_address" : "fred.bloggs@company.ie"
}
{ 
    "_id" : ObjectId("5fc157d9ba1b05372b2dacfa"), 
    "birth_date" : "1955-01-21", 
    "emp_id" : NumberInt(10005), 
    "first_name" : "Kyoichi", 
    "gender" : "M", 
    "hire_date" : "1989-09-12", 
    "last_name" : "Maliniak", 
    "email_address" : "fred.bloggs@company.ie"
}
{ 
    "_id" : ObjectId("5fc157d9ba1b05372b2dacfb"), 
    "birth_date" : "1953-04-20", 
    "emp_id" : NumberInt(10006), 
    "first_name" : "Anneke", 
    "gender" : "F", 
    "hire_date" : "1989-06-02", 
    "last_name" : "Preusig", 
    "email_address" : "fred.bloggs@company.ie"
}
{ 
    "_id" : ObjectId("5fc157d9ba1b05372b2dacfc"), 
    "birth_date" : "1957-05-23", 
    "emp_id" : NumberInt(10007), 
    "first_name" : "Tzvetan", 
    "gender" : "F", 
    "hire_date" : "1989-02-10", 
    "last_name" : "Zielinski", 
    "email_address" : "fred.bloggs@company.ie"
}
{ 
    "_id" : ObjectId("5fc157d9ba1b05372b2dacfd"), 
    "birth_date" : "1958-02-19", 
    "emp_id" : NumberInt(10008), 
    "first_name" : "Saniya", 
    "gender" : "M", 
    "hire_date" : "1994-09-15", 
    "last_name" : "Kalloufi", 
    "email_address" : "fred.bloggs@company.ie"
}
{ 
    "_id" : ObjectId("5fc157d9ba1b05372b2dacfe"), 
    "birth_date" : "1952-04-19", 
    "emp_id" : NumberInt(10009), 
    "first_name" : "Sumant", 
    "gender" : "F", 
    "hire_date" : "1985-02-18", 
    "last_name" : "Peac", 
    "email_address" : "fred.bloggs@company.ie"
}
{ 
    "_id" : ObjectId("5fc157d9ba1b05372b2dacff"), 
    "birth_date" : "1963-06-01", 
    "emp_id" : NumberInt(10010), 
    "first_name" : "Duangkaew", 
    "gender" : "F", 
    "hire_date" : "1989-08-24", 
    "last_name" : "Piveteau", 
    "email_address" : "fred.bloggs@company.ie"
}
{ 
    "_id" : ObjectId("5fc157d9ba1b05372b2dad00"), 
    "birth_date" : "1953-11-07", 
    "emp_id" : NumberInt(10011), 
    "first_name" : "Mary", 
    "gender" : "F", 
    "hire_date" : "1990-01-22", 
    "last_name" : "Sluis", 
    "email_address" : "fred.bloggs@company.ie"
}
{ 
    "_id" : ObjectId("5fc157d9ba1b05372b2dad01"), 
    "birth_date" : "1960-10-04", 
    "emp_id" : NumberInt(10012), 
    "first_name" : "Patricio", 
    "gender" : "M", 
    "hire_date" : "1992-12-18", 
    "last_name" : "Bridgland", 
    "email_address" : "fred.bloggs@company.ie"
}
{ 
    "_id" : ObjectId("5fc157d9ba1b05372b2dad02"), 
    "birth_date" : "1963-06-07", 
    "emp_id" : NumberInt(10013), 
    "first_name" : "Eberhardt", 
    "gender" : "M", 
    "hire_date" : "1985-10-20", 
    "last_name" : "Terkki", 
    "email_address" : "fred.bloggs@company.ie"
}

一些薪资数据：

{ 
    "_id" : ObjectId("5fc157daba1b05372b2db050"), 
    "emp_id" : NumberInt(10001), 
    "from_date" : "1986-06-26", 
    "salary" : NumberInt(60117), 
    "to_date" : "1987-06-26"
}
{ 
    "_id" : ObjectId("5fc157daba1b05372b2db052"), 
    "emp_id" : NumberInt(10001), 
    "from_date" : "1987-06-26", 
    "salary" : NumberInt(62102), 
    "to_date" : "1988-06-25"
}
{ 
    "_id" : ObjectId("5fc157daba1b05372b2db054"), 
    "emp_id" : NumberInt(10001), 
    "from_date" : "1988-06-25", 
    "salary" : NumberInt(66074), 
    "to_date" : "1989-06-25"
}
{ 
    "_id" : ObjectId("5fc157daba1b05372b2db056"), 
    "emp_id" : NumberInt(10001), 
    "from_date" : "1989-06-25", 
    "salary" : NumberInt(66596), 
    "to_date" : "1990-06-25"
}
{ 
    "_id" : ObjectId("5fc157daba1b05372b2db058"), 
    "emp_id" : NumberInt(10001), 
    "from_date" : "1990-06-25", 
    "salary" : NumberInt(66961), 
    "to_date" : "1991-06-25"
}
{ 
    "_id" : ObjectId("5fc157daba1b05372b2db059"), 
    "emp_id" : NumberInt(10001), 
    "from_date" : "1991-06-25", 
    "salary" : NumberInt(71046), 
    "to_date" : "1992-06-24"
}
{ 
    "_id" : ObjectId("5fc157daba1b05372b2db05b"), 
    "emp_id" : NumberInt(10001), 
    "from_date" : "1992-06-24", 
    "salary" : NumberInt(74333), 
    "to_date" : "1993-06-24"
}
{ 
    "_id" : ObjectId("5fc157daba1b05372b2db05d"), 
    "emp_id" : NumberInt(10001), 
    "from_date" : "1993-06-24", 
    "salary" : NumberInt(75286), 
    "to_date" : "1994-06-24"
}
{ 
    "_id" : ObjectId("5fc157daba1b05372b2db05f"), 
    "emp_id" : NumberInt(10001), 
    "from_date" : "1994-06-24", 
    "salary" : NumberInt(75994), 
    "to_date" : "1995-06-24"
}
{ 
    "_id" : ObjectId("5fc157daba1b05372b2db062"), 
    "emp_id" : NumberInt(10001), 
    "from_date" : "1995-06-24", 
    "salary" : NumberInt(76884), 
    "to_date" : "1996-06-23"
}
{ 
    "_id" : ObjectId("5fc157daba1b05372b2db064"), 
    "emp_id" : NumberInt(10001), 
    "from_date" : "1996-06-23", 
    "salary" : NumberInt(80013), 
    "to_date" : "1997-06-23"
}
{ 
    "_id" : ObjectId("5fc157daba1b05372b2db066"), 
    "emp_id" : NumberInt(10001), 
    "from_date" : "1997-06-23", 
    "salary" : NumberInt(81025), 
    "to_date" : "1998-06-23"
}
{ 
    "_id" : ObjectId("5fc157daba1b05372b2db068"), 
    "emp_id" : NumberInt(10001), 
    "from_date" : "1998-06-23", 
    "salary" : NumberInt(81097), 
    "to_date" : "1999-06-23"
}
{ 
    "_id" : ObjectId("5fc157daba1b05372b2db06a"), 
    "emp_id" : NumberInt(10001), 
    "from_date" : "1999-06-23", 
    "salary" : NumberInt(84917), 
    "to_date" : "2000-06-22"
}
{ 
    "_id" : ObjectId("5fc157daba1b05372b2db06c"), 
    "emp_id" : NumberInt(10001), 
    "from_date" : "2000-06-22", 
    "salary" : NumberInt(85112), 
    "to_date" : "2001-06-22"
}
{ 
    "_id" : ObjectId("5fc157daba1b05372b2db06e"), 
    "emp_id" : NumberInt(10001), 
    "from_date" : "2001-06-22", 
    "salary" : NumberInt(85097), 
    "to_date" : "2002-06-22"
}

员工表图像

工资表图片

MongoDbDump BSON 文件：employees.agz

员工样本数据库（由 Siemens Corporate Research 的 Fusheng Wang 和 Carlo Zaniolo 创建）

谢谢

标签： mongodbgroup-byinner-join

解决方案

您可以使用$project和$lookup。

这是代码

db.Employees.aggregate([
  {
    "$project": {
      _id: 1,
      emp_id: 1,
      first_name: 1,
      last_name: 1
    }
  },
  {
    "$lookup": {
      "from": "Salaries",
      let: { eId: "$emp_id" },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                { $eq: [ "$$eId", "$emp_id" ] },
                { $gt: [ "$salary", 80000 ] },
                { $eq: [ "$to_date", "2002-06-22" ] }
              ]
            }
          }
        }
      ],
      "as": "salary"
    }
  }
])

工作Mongo游乐场

推荐阅读