时间戳

首页 > 解决方案 > 如何使用三个表选择差异？

mysql - 如何使用三个表选择差异？

问题描述

我需要运行一个脚本来修复我的表 company_menu 中的一些行。但是，我无法构建此查询来获取这些寄存器。我在此链接中构建架构：http ://sqlfiddle.com/#!9/5ab86b

下面我展示了预期的结果。

公司

ID 姓名
1 公司 1
2 公司 2
3 公司 3

菜单项

ID 姓名
1 家
2 图表
3 用户
4 项目

company_menu

ID company_id menu_item_id
1 1 1
2 1 2
3 1 3
4 1 4
5 2 1
6 2 3

这是我预期的结果：

ID company_id menu_item_id
1 2 2
2 2 4
3 3 1
4 3 2
5 3 3
6 3 4 
CREATE TABLE companies(
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50)
 );
 
CREATE TABLE menu_items(
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50)
 );
 
CREATE TABLE company_menu(
  id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  company_id INT,
  menu_item_id INT,
  FOREIGN KEY(company_id) REFERENCES companies(id),
  FOREIGN KEY(menu_item_id) REFERENCES menu_items(id)
);

INSERT INTO companies (name) VALUES ("Company 1"),("Company 2"),("Company 3");
INSERT INTO menu_items (name) VALUES ("home"),("charts"),("users"),("projects");
INSERT INTO company_menu (company_id, menu_item_id) VALUES (1, 1),(1, 2),(1,3),(1,4);
INSERT INTO company_menu (company_id, menu_item_id) VALUES (2, 1),(2,3);

标签： mysqljoinpivot-table

解决方案

我能想到的两种方法。不知道哪个更有效率。两者都以完整的 compannies-menu_items 连接开始以获取所有可能的组合，然后删除现有的：

  1. WHERE NOT EXISTS
select c.id company_id, m.id menu_item_id
from companies c
join menu_items m
where not exists (
  select * from company_menu where company_id = c.id and menu_item_id = m.id
);
  1. LEFT JOIN+ IS NULL:
select c.id company_id, m.id menu_item_id
from companies c
join menu_items m
left join company_menu cm on cm.company_id = c.id and cm.menu_item_id = m.id
where cm.id is null;

两者都可以在任何公司或 menu_item 列上排序。

http://sqlfiddle.com/#!9/5ab86b/11

推荐阅读