时间戳

首页 > 解决方案 > 如何在查找中进行划分并在 Mongodb 中出错

mongodb - 如何在查找中进行划分并在 Mongodb 中出错

问题描述

我是 MongoDB 的新手，很难用查找来划分。我想为每个员工制定月薪。我怎样才能做到这一点？提前致谢。任何帮助将不胜感激。另外，我将我的薪水字段转换为浮动。但是，我仍然遇到错误。当我使用它而不使用它时，它显示得很好。我试图每小时找到这些问题的答案，但没有成功。

db.Salary.insertMany([

  {
    "POSITION": "1",
    "BRANCH_SIZE": "HQ",
    "SALARY": "150000"
  },
  {
    "POSITION": "2",
    "BRANCH_SIZE": "HQ",
    "SALARY": "100000"
  },
  {
    "POSITION": "3",
    "BRANCH_SIZE": "HQ",
    "SALARY": "70000"
  },
  {
    "POSITION": "4",
    "BRANCH_SIZE": "HQ",
    "SALARY": "30000"
  },
  {
    "POSITION": "5",
    "BRANCH_SIZE": "HQ",
    "SALARY": "56000"
  }
])

db.Employee.insertMany([

  {
    "EMPLOYEE_NO": "1000",
    "LNAME": "Wyatt",
    "FNAME": " Stefan",
    "STREET": "1255 Harrinton Ave.",
    "CITY": "MANKATO",
    "STATE": "MN",
    "ZIP": "56001",
    "STATUS": "1",
    "DOB": "12-MAY-54",
    "START_DATE": "06-MAY-76",
    "END_DATE": "",
    "BRANCH_NO": "100",
    "BRANCH_SIZE": "HQ",
    "POSITION": "1",
    "RATE": "",
    "COMMISSION": ""
  },
  {
    "EMPLOYEE_NO": "1029",
    "LNAME": "Martin",
    "FNAME": "Edward",
    "STREET": "1250 Harrinton Ave.",
    "CITY": "SPRINGFIELD",
    "STATE": "IL",
    "ZIP": "62701",
    "STATUS": "1",
    "DOB": "08-MAY-68",
    "START_DATE": "02-MAY-95",
    "END_DATE": "",
    "BRANCH_NO": "103",
    "BRANCH_SIZE": "MD",
    "POSITION": "3",
    "RATE": "",
    "COMMISSION": ""
  },
  {
    "EMPLOYEE_NO": "1089",
    "LNAME": "Stewart",
    "FNAME": "Macy",
    "STREET": "3415 Olanwood Court, Suite 202",
    "CITY": "SAINT PAUL",
    "STATE": "MN",
    "ZIP": "55101",
    "STATUS": "2",
    "DOB": "30-APR-29",
    "START_DATE": "24-APR-55",
    "END_DATE": "",
    "BRANCH_NO": "101",
    "BRANCH_SIZE": "BG",
    "POSITION": "4",
    "RATE": "",
    "COMMISSION": ""
  }
])

I convert salary field to float using below code. 
db.Salary.find({SALARY: {$exists: true}}).forEach(function(obj) { 
    obj.SALARY = parseFloat(obj.SALARY);
    db.Salary.save(obj);
});



> db.Employee.aggregate([{
...     $lookup: {
...       from: "Salary",
...       localField: "POSITION",
...       foreignField: "POSITION",
...       as: "MOnthlySalary"}},
...     {$project: {"EMPLOYEE_NO":1,"LNAME":1,"FNAME":1,
...                 "MOnthlySalary": {$divide: ["$SALARY", 12]}
...                }}])


Output:-
{ "_id" : ObjectId("5efa025aeeaa35a209477ebe"), "EMPLOYEE_NO" : "1000", "LNAME" : "Wyatt", "FNAME" : " Stefan", "MOnthlySalary" : null }
{ "_id" : ObjectId("5efa025aeeaa35a209477ebf"), "EMPLOYEE_NO" : "1029", "LNAME" : "Martin", "FNAME" : "Edward", "MOnthlySalary" : null }
{ "_id" : ObjectId("5efa025aeeaa35a209477ec0"), "EMPLOYEE_NO" : "1089", "LNAME" : "Stewart", "FNAME" : "Macy", "MOnthlySalary" : null }

标签： mongodbcalculated-columnscalculated-field

解决方案

db.Employee.aggregate([{
     $lookup: {
       from: "Salary",
       localField: "POSITION",
       foreignField: "POSITION",
       as: "MOnthlySalary"}},
       {$unwind:"$MOnthlySalary"},
       {$match:{}},
     {$project: {"EMPLOYEE_NO":1,"LNAME":1,"FNAME":1,"SALARY":1,
                 "avgSalary": {$divide: ["$MOnthlySalary.SALARY", 12]}
                }}])

变化是$MOnthlySalary.SALARY

输出：

/* 1 */
{
    "_id" : ObjectId("5efa0fffc8a4b73cb0b8e320"),
    "EMPLOYEE_NO" : "1000",
    "LNAME" : "Wyatt",
    "FNAME" : " Stefan",
    "avgSalary" : 12500.0
}

/* 2 */
{
    "_id" : ObjectId("5efa0fffc8a4b73cb0b8e321"),
    "EMPLOYEE_NO" : "1029",
    "LNAME" : "Martin",
    "FNAME" : "Edward",
    "avgSalary" : 5833.33333333333
}

/* 3 */
{
    "_id" : ObjectId("5efa0fffc8a4b73cb0b8e322"),
    "EMPLOYEE_NO" : "1089",
    "LNAME" : "Stewart",
    "FNAME" : "Macy",
    "avgSalary" : 2500.0
}

您可以使用$round四舍五入的 avgSalary。

推荐阅读