这个sql语法是否正确？

Question

我能将SUM()的ISNULL()内....考虑我下面的SQL Server的select语句

SELECT e.Emp_Id,e.Identity_No,e.Emp_Name,case WHEN e.SalaryBasis=1 
THEN 'Weekly' ELSE 'Monthly' end as SalaryBasis,e.FixedSalary, 
    ISNULL(Adv.Daily_Wage,0) as Advance from Employee as e 
    inner join Designation as d on e.Desig_Id=d.Desig_Id 
    Left Outer Join Payroll as Adv on e.Emp_Id=Adv.Emp_Id where e.Is_Deleted=0 

本声明做工精细....但是当我申请SUM()内的ISNULL()

SELECT e.Emp_Id,e.Identity_No,e.Emp_Name,case WHEN e.SalaryBasis=1 
    THEN 'Weekly' ELSE 'Monthly' end as SalaryBasis,e.FixedSalary, 
     ISNULL(SUM(Adv.Daily_Wage),0) as Advance from Employee as e 
     inner join Designation as d on e.Desig_Id=d.Desig_Id 
     Left Outer Join Payroll as Adv on e.Emp_Id=Adv.Emp_Id 
     where e.Is_Deleted=0 

我得到了错误，

'Employee.Emp_Id'列在无效选择列表，因为它不是包含在合计 函数或GROUP BY子句中的 。

任何建议....

Answer 1

您需要GROUP BY在选择其他列。像

SELECT e.Emp_Id, 
     e.Identity_No, 
     e.Emp_Name, 
     case 
      WHEN e.SalaryBasis=1 THEN 'Weekly' 
      ELSE 'Monthly' 
     end as SalaryBasis,e.FixedSalary, 
     ISNULL(SUM(Adv.Daily_Wage),0) as Advance 
from Employee as e inner join 
     Designation as d on e.Desig_Id=d.Desig_Id Left Outer Join 
     Payroll as Adv on e.Emp_Id=Adv.Emp_Id 
where e.Is_Deleted=0 
GROUP BY e.Emp_Id, --This section is what you are missing 
     e.Identity_No, 
     e.Emp_Name, 
     case 
      WHEN e.SalaryBasis=1 THEN 'Weekly' 
      ELSE 'Monthly' 
     end, 
    e.FixedSalary 

东西看一看定义在这里

GROUP BY (Transact-SQL)