获取SQL查询值正在重复

I want to get the percentage of acceptable, excellent, notacceptable, using the below query but the answer is repeating

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

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

In this SQL query the value is repeating please help me to solve it

在这个SQL查询中重复的值请帮我解决一下

1 个解决方案

#1

Well, from what I see You have SQL problem not C#.

好吧，从我看到你有SQL问题而不是C＃。

In cases like this, I tend to have wrong JOINs in my query, and the best way of knowing what is wrong is to write new select to see where rows are duplicated:

在这种情况下，我的查询中往往会出现错误的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

Please check what is pk for each joined table. You should have one em and emp PK per one ap PK. You clearly do not have this situation, so I see two options. Either You can try to write correct joins to ensure that rows will not double itself, or if this is not possible (for example one employee can have many personal details), You can decide which one You want to distpal in subquery and join to it. Something like this:

请检查每个连接表的pk是什么。每个PK PK你应该有一个em和emp 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 
[..]

Hope that will help.

希望会有所帮助。

#1