主页 / dba / 问题 / 17366
Accepted
shantanuo
Asked: 2012-05-03 08:43:21 +0800 CST

在 LEFT JOIN 中查找重复项

  • 772

这是我的表结构：

create table table_a (emp_name varchar(100), dept_name varchar(100));
insert into table_a values ('amar', 'finance');
insert into table_a values ('akbar', 'human_resource');
insert into table_a values ('amar', 'finance');
insert into table_a values ('anthony', 'information_technology');

create table table_b (emp_name varchar(100), dept_name varchar(100));
insert into table_b values ('amar', 'finance');
insert into table_b values ('akbar', 'human_resource');

mysql> select * from table_a as a left join table_b as b on a.emp_name = b.emp_name and a.dept_name = b.dept_name where b.dept_name is null;
+----------+------------------------+----------+-----------+
| emp_name | dept_name              | emp_name | dept_name |
+----------+------------------------+----------+-----------+
| anthony  | information_technology | NULL     | NULL      |
+----------+------------------------+----------+-----------+
1 row in set (0.00 sec)

上面提到的左连接按预期工作。

但是，我还需要一条“amar”记录，因为表 A 中有 2 个条目，而表 B 中只有 1 个条目。所以，结果应该如下所示......

+----------+------------------------+----------+-----------+
| emp_name | dept_name              | emp_name | dept_name |
+----------+------------------------+----------+-----------+
| anthony  | information_technology | NULL     | NULL      |
| amar     | finance                | NULL     | NULL      |
+----------+------------------------+----------+-----------+
2 row in set (0.00 sec)

在计数准确性比数据完整性更重要的环境中，我需要这个。B表记录号（2）+查询结果（2）要与A表记录号（4）匹配

mysql join

1 个回答

  1. Best Answer

    这可能看起来非常令人费解，这是您需要的查询

    select * from
(select count(1) dupcount,emp_name,dept_name from table_a group by emp_name,dept_name) as a
left join
(select count(1) dupcount,emp_name,dept_name from table_b group by emp_name,dept_name) as b
on a.emp_name = b.emp_name and a.dept_name = b.dept_name where a.dupcount <> IFNULL(b.dupcount,0);

    我加载了你的样本数据，我得到了这个：

    mysql> select * from
    -> (select count(1) dupcount,emp_name,dept_name from table_a group by emp_name,dept_name) as a
    -> left join
    -> (select count(1) dupcount,emp_name,dept_name from table_b group by emp_name,dept_name) as b
    -> on a.emp_name = b.emp_name and a.dept_name = b.dept_name where a.dupcount <> IFNULL(b.dupcount,0);
+----------+----------+------------------------+----------+----------+-----------+
| dupcount | emp_name | dept_name              | dupcount | emp_name | dept_name |
+----------+----------+------------------------+----------+----------+-----------+
|        2 | amar     | finance                |        1 | amar     | finance   |
|        1 | anthony  | information_technology |     NULL | NULL     | NULL      |
+----------+----------+------------------------+----------+----------+-----------+
2 rows in set (0.02 sec)

mysql>

    这是没有 dupcount 值的情况

    mysql> select a.emp_name,a.dept_name,b.emp_name,b.dept_name from
    -> (select count(1) dupcount,emp_name,dept_name from table_a group by emp_name,dept_name) as a
    -> left join
    -> (select count(1) dupcount,emp_name,dept_name from table_b group by emp_name,dept_name) as b
    -> on a.emp_name = b.emp_name and a.dept_name = b.dept_name where a.dupcount <> IFNULL(b.dupcount,0);
+----------+------------------------+----------+-----------+
| emp_name | dept_name              | emp_name | dept_name |
+----------+------------------------+----------+-----------+
| amar     | finance                | amar     | finance   |
| anthony  | information_technology | NULL     | NULL      |
+----------+------------------------+----------+-----------+
2 rows in set (0.00 sec)

mysql>

    试试看 ！！！

    • 1

相关问题