正在重复获取SQL查询值

本文关键字：SQL 查询 获取 | 更新日期: 2023-09-27 18:28:51

我想使用下面的查询获得可接受、优秀、不可接受的百分比，但答案是重复

begin
   set nocount on
   declare @acceptable as varchar(10)
   declare @Excellent as varchar(10)
   declare @NotAcceptable as varchar(10)
   declare @total as varchar(10)
   declare @percent1 as varchar(10) = null
   declare @percent2 as varchar(10) = null
   declare @percent3 as varchar(10) = null
   select @acceptable = count(*) 
   from [dbo].[tbl_Apprisal] 
   where ApprisalStatus = 'Acceptable' 
   select @Excellent = count(*) 
   from [dbo].[tbl_Apprisal] 
   where ApprisalStatus = 'Excellent' 
   select @NotAcceptable = count(*) 
   from [dbo].[tbl_Apprisal] 
   where ApprisalStatus = 'Not Acceptable' 
   SET @total = convert(decimal, @acceptable) + 
                convert(decimal, @Excellent) +
                convert(decimal, @NotAcceptable)
   SET @percent1 = convert(int, @acceptable) * convert(int, 100) / convert(int, @total)
   SET @percent2 = convert(int, @Excellent) * convert(int, 100) / convert(int, @total)
   SET @percent3 = convert(int, @NotAcceptable) * convert(int, 100) / convert(int, @total)
   select 
       'Accplable:' + @percent1 + '%' + ',' + 'Excellent:' + @percent2 + '%' + ',' + 'Not Acceptable:' + @percent3 + '%' as persnt,
       Emp.personFname as doneby1, 
       Em.personFname + Em.[personMname] + Em.[personLname] as personFname1, 
       ap.ProcessId, ap.empNumber, 
       ap.fromDate ApprisalStatus, ap.comment, ap.DoneBy, 
       convert(date, ap.DoneByDate, 105) as DoneByDate   
   from 
       [dbo].[tbl_Apprisal] ap
   inner join 
       [dbo].[tbl_EmployeePersonalDetails] Em on Em.empNumber = ap.empNumber  
   inner join 
       [dbo].[tbl_EmployeePersonalDetails] Emp on Emp.empNumber = ap.DoneBy 
   order by 
       convert(date, ap.fromDate, 105) DESC

在这个SQL查询中，值重复，请帮助我解决它

正在重复获取SQL查询值

好吧，据我所见，您有SQL问题，而不是C#问题。

在这种情况下，我的查询中往往有错误的JOIN，了解错误的最好方法是编写新的select来查看行的重复位置：

select 
    -- AP primary key
    ap.?
    -- Em primary key
    em.?
    -- Emp primary key
    emp.?
from 
    [dbo].[tbl_Apprisal] ap
inner join [dbo].[tbl_EmployeePersonalDetails] Em on Em.empNumber = ap.empNumber  
inner join [dbo].[tbl_EmployeePersonalDetails] Emp on Emp.empNumber = ap.DoneBy

请检查每个联接表的pk是多少。你应该有一个em和emp PK每一个ap PK。你显然没有这种情况，所以我看到两个选项。您可以尝试编写正确的联接，以确保行不会自行翻倍，或者如果这不可能（例如，一名员工可能有许多个人详细信息），您可以决定要在子查询中提取哪一个并联接到它

select 
[..]
from 
    [dbo].[tbl_Apprisal] ap
inner join (
    SELECT 
        empNumber,
        MAX(personFname) as personFname 
    FROM 
        [dbo].[tbl_EmployeePersonalDetails] 
    GROUP BY 
        empNumber
) Em on Em.empNumber = ap.empNumber 
[..]

希望这会有所帮助。

相关文章：