这个sql内连接有什么问题？

Question

这是我的 select 语句，其中包含两个表的内连接，

if not exists(select EmpId from SalaryDetails 
              where EmpId in (select Emp_Id 
                              from Employee where Desig_Id=@CategoryId))
begin
  // some statements here
end
else
begin
  SELECT e.Emp_Id, e.Identity_No, e.Emp_Name,
      case WHEN e.SalaryBasis=1 THEN 'Weekly' 
           ELSE 'Monthly' end as SalaryBasis,e.FixedSalary,
      (SELECT TOP 1 RemainingAdvance 
       FROM SalaryDetails 
       ORDER BY CreatedDate DESC) as Advance
    FROM Employee as e inner join Designation as d on e.Desig_Id=d.Desig_Id 
    INNER JOIN SalaryDetails as S on e.Emp_Id=S.EmpId 
End

我的结果窗格，

alt text http: //img220.imageshack.us/img220/7774/resultpane.jpg

和我的薪资详细信息表，

替代文本http://img28.imageshack.us/img28/770/salarydettable.jpg

编辑： 我的输出必须是，

16 CR14 Natarajan 每周 150.00 354.00
17 cr12333 盘点周刊 122.00 0.00

Answer 1

您没有过滤任何员工 ID 上的子查询 (SELECT TOP 1 RemainingAdvance FROM SalaryDetails ORDER BY CreatedDate DESC)，因此当按 排序时，它会为您提供整个表中的第一条记录CreatedDate DESC （我猜是 354。）

您可能希望将该表表达式移动到 FROM 子句中，而不是您的 SELECT 中，包括您的员工 ID，并对该表达式进行联接。

SELECT 
    e.Emp_Id,e.Identity_No,e.Emp_Name,case WHEN e.SalaryBasis=1 THEN 'Weekly' ELSE 'Monthly' end as SalaryBasis,e.FixedSalary,
    from Employee as e inner join Designation as d on e.Desig_Id=d.Desig_Id 
    inner join SalaryDetails as S on e.Emp_Id=S.EmpId 
    inner join
    (SELECT EmpID, RemainingAdvance, RANK() OVER (PARTITION BY EmpID ORDER BY CreatedDate DESC) AS SalaryRank FROM SalaryDetails ORDER BY CreatedDate DESC) as Advance ON Advance.EmpID = e.Emp_ID AND Advance.SalaryRank = 1

这只是我的想法，所以可能需要一些调整才能正确运行。另请注意 RANK() 函数的使用 - 如果您使用 TOP 1，您只能获得整个表的第一条记录。您需要的是每个员工 ID 的第一条记录。

如果是我，我可能会将该表表达式设为视图，甚至是标量值函数，获取您的员工 ID 并返回第一个 RemainingAdvance 值，然后您可以使用 TOP 1 并过滤员工 ID。

Answer 2

看起来您甚至没有使用对 Designation 的连接，并且您还缺少在顶部 IF 语句中使用的 WHERE 子句。我还将子查询向下移动到连接中，就像安迪指出的那样。如果没有数据库来测试这一点可能不准确，但我会将其重写为类似的内容；

SELECT e.Emp_Id, e.Identity_No, e.Emp_Name,
  case WHEN e.SalaryBasis=1 
       THEN 'Weekly' 
       ELSE 'Monthly' end as SalaryBasis,
  e.FixedSalary,S.RemainingAdvance as Advance
FROM Employee as e 
  INNER JOIN (
   SELECT TOP 1 EmpId, RemainingAdvance 
   FROM SalaryDetails 
   ORDER BY CreatedDate DESC) as S on e.Emp_Id=S.EmpId 
WHERE e.Desig_Id=@CategoryId

Andy 建议将子查询移至视图中，这是一个很好的建议，更容易阅读，而且如果数据库很大的话，效率可能会更高。

编辑：（答案）

(SELECT sd.empid,
               sd.remainingadvance,
               ROW_NUMBER() OVER (PARTITION BY sd.empid ORDER BY sd.createddate DESC) AS rank
          FROM SALARYDETAILS sd
          JOIN EMPLOYEE e ON e.emp_id = sd.empid
                         AND e.desig_id = @CategoryId) s
            WHERE s.rank = 1

我编辑了杰伊的答案，因为他接近我的输出......