重复显示记录

Question

我有1个查询可以从内联合的帮助下从三个表中获取记录。

table name 

tbl_emp as emp 
tbl_sal as sal 
tbl_address as addr 

fields from 3 tables 

Schema: 

tbl_emp - empid,name,comp_id 
tbl_sal - empid - emp_type 
tbl_address - emp_type 

查询

SELECT emp.empid, emp.name, sal.salary, addr.address 
from tbl_emp as emp 
INNER JOIN tbl_sal as sal 
    ON emp.empid = sal.empid 
INNER JOIN tbl_address as addr 
    ON addr.emp_type = sal.emp_type 
where comp_id = '114'; 

结果表明：

1 abc 1000 test 
1 abc 1000 test 
1 abc 1000 test 
1 abc 1000 test 
1 abc 1000 test 
2 xyz 2500 main 
2 xyz 2500 main 
2 xyz 2500 main 
2 xyz 2500 main 
2 xyz 2500 main 

我想导致这样的：

1 abc 1000 test 
2 xyz 2500 main 

请帮我在这。

Answer 1

试试这个

SELECT emp.empid, 
     emp.name, 
     sal.salary, 
     addr.address 
FROM tbl_emp as emp 
INNER JOIN tbl_sal as sal 
    ON emp.empid = sal.empid 
INNER JOIN tbl_address as addr 
    ON addr.emp_type = sal.emp_type 
WHERE comp_id = '114' 
GROUP BY empid; 

Answer 2

使用GROUP BY：

SELECT emp.empid,emp.name,sal.salary,addr.address from tbl_emp as emp INNER JOIN tbl_sal as sal ON emp.empid = sal.empid INNER JOIN tbl_address as addr ON addr.emp_type = sal.emp_type where comp_id = '114' 
group by emp.empid,emp.name,sal.salary,addr.address 

Answer 3

根据您的样本数据，你应该能够使用DISTINCT：

SELECT DISTINCT emp.empid, 
    emp.name, 
    sal.salary, 
    addr.address 
from tbl_emp as emp 
INNER JOIN tbl_sal as sal 
    ON emp.empid = sal.empid 
INNER JOIN tbl_address as addr 
    ON addr.emp_type = sal.emp_type 
where comp_id = '114'; 

DISTINCT跨所有应用数据列将删除所有重复项。你有限的样本将允许应用这个。

您还可以使用GROUP BY来获得结果。我的建议是，如果你申请的GROUP BY将其应用到每一个项目在SELECT列表：

SELECT emp.empid, 
    emp.name, 
    sal.salary, 
    addr.address 
from tbl_emp as emp 
INNER JOIN tbl_sal as sal 
    ON emp.empid = sal.empid 
INNER JOIN tbl_address as addr 
    ON addr.emp_type = sal.emp_type 
where comp_id = '114' 
GROUP BY emp.empid, emp.name, sal.salary, addr.address; 

MySQL使用的扩展GROUP BY它允许在选择列表中的项目要非聚集，不包括GROUP BY子句中。但是，这可能会导致意外的结果，因为MySQL可以选择返回的值。 （见MySQL Extensions to GROUP BY）

从MySQL的文档：

MySQL的扩展使用GROUP BY的，这样的选择列表可参考在GROUP BY子句中未命名的非聚合列。 ...您可以使用此功能通过避免不必要的列排序和分组来获得更好的性能。但是，这非常有用，因为每个未在GROUP BY中命名的非聚合列中的所有值对于每个组都是相同的。服务器可以自由选择每个组中的任何值，因此除非它们相同，否则所选值是不确定的。此外，每个组的值的选择不能通过添加ORDER BY子句来影响。结果集的排序在选择值后发生，而ORDER BY不影响服务器选择的值。

Answer 4

DISTINCT关键字会做你想要什么，这将确保重复被过滤掉

SELECT DISTINCT emp.empid,emp.name,sal.salary,addr.address from tbl_emp as emp INNER JOIN tbl_sal as sal ON emp.empid = sal.empid INNER JOIN tbl_address as addr ON addr.emp_type = sal.emp_type where comp_id = '114'; 

如果你虽然得到重复，就可能要确保您的查询实际上做什么你认为它否则，如果结果集中存在真正的重复项（而不是您的查询创建的重复项），则使用DISTINCT可能会出现问题。

Answer 5

尝试group byemp.empid

SELECT emp.empid,emp.name,sal.salary,addr.address from tbl_emp as emp INNER JOIN tbl_sal as sal ON emp.empid = sal.empid INNER JOIN tbl_address as addr ON addr.emp_type = sal.emp_type where comp_id = '114' group by emp.empid ;