如何在连接查询中找到不同的记录？ [英] How to find distinct record in join query?

问题描述

我试图通过在sql server 2008 r2中使用连接查询来查找不同的记录但是在选择列表中收到错误Column'tbl_SalaryInformation.SalaryId无效，因为它不包含在聚合函数或GROUP BY中条款。





低于我的查询，所以请帮助我，给我更好的解决方案

 选择  distinct  si。< span class =code-keyword> Date ，si.SalaryId，er.EmpCode，er.FName  from  tbl_EmpRegistration er，tbl_SalaryInformation si 其中 er.EmpCode = si.EmpCode 和 si.PartnerName = '  Pio' 和 er.PartnerName = '  Pio'  group   by  si。日期 

解决方案

看来你需要提高你的SQL技能，请阅读google SQL和group by子句。



除此之外你可以有两个解决方案。



1.完全删除你的Group by Clause这样



< pre lang =SQL> 选择 distinct si。日期，si.SalaryId，er.EmpCode，er.FName 来自 tbl_EmpRegistration呃，tbl_SalaryInformation si 其中 er.EmpCode = si.EmpCode 和 si.PartnerName = ' Pio' 和 er.PartnerName = ' Pio';

2.使用选择条款修改USe Group

  SELECT  T 。日期，T.MaxSalaryID SalaryId，er.EmpCode，er.FName 
  FROM  
（
  SELECT  si。日期，si.EmpCode，MAX（si.SalaryId） MAXSalaryID 
  FROM  tbl_SalaryInformation si 
 组  by  si。 Date ，si.EmpCode 
）T  INNER   JOIN  tbl_EmpRegistration er  on  T.EmpCode = er.EmpCode 

I am trying to find distinct record by using join query in sql server 2008 r2 but getting an error "Column 'tbl_SalaryInformation.SalaryId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
"

And below its my query so please help me and give me better solution for that

select distinct si.Date, si.SalaryId,er.EmpCode, er.FName from tbl_EmpRegistration er,tbl_SalaryInformation si where er.EmpCode=si.EmpCode and si.PartnerName='Pio' and er.PartnerName='Pio' group by si.Date

解决方案

It seems that you need to improve your SQL skill, Please read google SQL and group by clauses.

Apart from that you can have two solutions.

1. Remove your Group by Clause at all Like this

select distinct si.Date, si.SalaryId,er.EmpCode, er.FName from tbl_EmpRegistration er,tbl_SalaryInformation si where er.EmpCode=si.EmpCode and si.PartnerName='Pio' and er.PartnerName='Pio';

2. USe Group by with modifications in select clause

SELECT T.Date, T.MaxSalaryID SalaryId, er.EmpCode, er.FName
FROM
(
SELECT si.Date, si.EmpCode, MAX(si.SalaryId) MAXSalaryID
FROM tbl_SalaryInformation si
Group by si.Date, si.EmpCode
) T INNER JOIN tbl_EmpRegistration er on T.EmpCode = er.EmpCode

这篇关于如何在连接查询中找到不同的记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！