合并具有相同box_id的两个MySQL查询结果数组

没问题，我来帮你搞定这个数组合并的需求！你有两个从MySQL查询得到的数组，需要按相同的box_id合并成嵌套结构，下面是具体的实现方案：

原始数据 #

首先看一下你提供的两个原始数组：

Boxes数组 #

const boxes = [ 
  { box_id: 1, box_name: "Box 1" }, 
  { box_id: 2, box_name: "Box 2" }, 
];

Box设置数组 #

const box_settings = [ 
  { box_id: 1, box_name: "Box 1", box_settings_id: 1, box_number_of_people: 2, box_total_price: 368 }, 
  { box_id: 1, box_name: "Box 1", box_settings_id: 2, box_number_of_people: 4, box_total_price: 736 }, 
  { box_id: 2, box_name: "Box 2", box_settings_id: 3, box_number_of_people: 2, box_total_price: 400 }, 
  { box_id: 2, box_name: "Box 2", box_settings_id: 4, box_number_of_people: 4, box_total_price: 800 }, 
];

期望的合并结果 #

你需要得到的嵌套结构是这样的：

[ 
  { 
    box_id: 1, 
    box_name: "Box 1", 
    box_settings: [ 
      { box_settings_id: 1, box_number_of_people: 2, box_total_price: 368 }, 
      { box_settings_id: 2, box_number_of_people: 4, box_total_price: 736 }, 
    ], 
  }, 
  { 
    box_id: 2, 
    box_name: "Box 2", 
    box_settings: [ 
      { box_settings_id: 3, box_number_of_people: 2, box_total_price: 400 }, 
      { box_settings_id: 4, box_number_of_people: 4, box_total_price: 800 }, 
    ], 
  }, 
];

实现方案（JavaScript） #

这里用JavaScript来实现，逻辑清晰且效率不错：

const boxes = [ 
  { box_id: 1, box_name: "Box 1" }, 
  { box_id: 2, box_name: "Box 2" }, 
];
const box_settings = [ 
  { box_id: 1, box_name: "Box 1", box_settings_id: 1, box_number_of_people: 2, box_total_price: 368 }, 
  { box_id: 1, box_name: "Box 1", box_settings_id: 2, box_number_of_people: 4, box_total_price: 736 }, 
  { box_id: 2, box_name: "Box 2", box_settings_id: 3, box_number_of_people: 2, box_total_price: 400 }, 
  { box_id: 2, box_name: "Box 2", box_settings_id: 4, box_number_of_people: 4, box_total_price: 800 }, 
];

// 第一步：把boxes转成以box_id为键的映射对象，方便快速查找
const boxMap = boxes.reduce((acc, box) => {
  // 为每个box初始化嵌套的box_settings数组
  acc[box.box_id] = { ...box, box_settings: [] };
  return acc;
}, {});

// 第二步：遍历所有设置项，把对应内容放到所属box的settings数组里
box_settings.forEach(setting => {
  // 解构出不需要的box_id和box_name，只保留设置相关的字段
  const { box_id, box_name, ...settingDetails } = setting;
  // 找到对应的box，把设置项push进去
  boxMap[box_id].box_settings.push(settingDetails);
});

// 第三步：把映射对象转回数组，就是最终的嵌套结构
const mergedResult = Object.values(boxMap);

// 打印结果看看
console.log(mergedResult);

代码说明 #

• 用reduce创建映射对象：这一步是为了把boxes数组转成{ [box_id]: boxObject }的结构，这样通过box_id查找对应的box是O(1)的时间复杂度，比每次遍历数组查找要高效很多，数据量大的时候优势更明显。
• forEach遍历设置项：通过解构赋值去掉重复的box_id和box_name字段，只保留设置相关的内容，然后把它添加到对应box的box_settings数组里。
• Object.values转数组：最后把映射对象的值提取出来，就得到了你想要的嵌套数组结构。

这个方案不管是在前端浏览器环境还是后端Node.js环境都能直接使用，非常灵活。

内容的提问来源于stack exchange，提问作者Cedric Hadjian