SQL inner join去重复
使用inner join后产生一对多 如何才能去掉一对多产生的重复数据
tDepartment：
1HDB-001混蛋部
2HDB-002草泥马部
3HDB-003神马部
tCompanyEmployee：
11HD-001混蛋1号男
21HD-002混蛋2号男
32HD-003草泥马1号 男
43HD-004神马1号男
53HD-005神马2号男
tEmployee：
111adminadmin
221admin2admin2
332admin3admin3
443admin4admin4
553admin5admin5
语句：
select distinct
[tEmployee].[EmpName]as'User',[tEmployee].[EmpPassword],
[tDepartment].[DeptId],[tDepartment].[DeptName],[tDepartment].[DeptNO],
[tCompanyEmployee].[EmpName],[tCompanyEmployee].[EmpNO],[tCompanyEmployee].[Sex]
from [tEmployee]
inner join
(
[tDepartment] inner join [tCompanyEmployee]
on [tDepartment].[DeptID]=[tCompanyEmployee].[DeptID]
)
on [tEmployee].[DeptID]=[tDepartment].[DeptID] and [tEmployee].[DeptID]=[tCompanyEmployee].[DeptID]
结果：
adminadmin1混蛋部HDB-001混蛋1号HD-001男
adminadmin1混蛋部HDB-001混蛋2号HD-002男
admin2admin21混蛋部HDB-001混蛋1号HD-001男
admin2admin21混蛋部HDB-001混蛋2号HD-002男
admin3admin32草泥马部HDB-002草泥马1号HD-003男
admin4admin43神马部HDB-003神马1号HD-004男
admin4admin43神马部HDB-003神马2号HD-005男
admin5admin53神马部HDB-003神马1号HD-004男
admin5admin53神马部HDB-003神马2号HD-005男
[解决办法]
SELECT distinct * from table
查询后面加上distinct 去除重复数据
[解决办法]
select distinct
[tEmployee].[EmpName]as'User',[tEmployee].[EmpPassword],
[tDepartment].[DeptId],[tDepartment].[DeptName],[tDepartment].[DeptNO],
[tCompanyEmployee].[EmpName],[tCompanyEmployee].[EmpNO],[tCompanyEmployee].[Sex]
from [tEmployee]
inner join [tDepartment] on [tEmployee].[DeptID]=[tDepartment].[DeptID] inner join [tCompanyEmployee] on [tEmployee].[DeptID]=[tCompanyEmployee].[DeptID]
select distinct * from
(
select
[tEmployee].[EmpName]as'User',[tEmployee].[EmpPassword],
[tDepartment].[DeptId],[tDepartment].[DeptName],[tDepartment].[DeptNO],
[tCompanyEmployee].[EmpName],[tCompanyEmployee].[EmpNO],[tCompanyEmployee].[Sex]
from [tEmployee]
inner join [tDepartment] on [tEmployee].[DeptID]=[tDepartment].[DeptID] inner join [tCompanyEmployee] on [tEmployee].[DeptID]=[tCompanyEmployee].[DeptID]
)
