需要有关我的SQL查询的帮助 [英] Need help for my SQL query

问题描述

好吧，我可能也已在较早之前发布了此内容，但到目前为止仍找不到答案，因此请帮助我解决这个问题.

Well I may have posted this earlier also, but unable to find the answer so far, so please help me on this one.

我的数据库结构:

ATT (Activity表)

• Act_ID(PK)
• Assigned_To_ID(FK，请参阅Emp_ID)
• Project_ID(FK，请参阅Project_ID)
• Product_ID(FK，请参阅Product_ID)
• 状态(可以为New, OnHold, Cancel, Closed)

• Act_ID(PK)
• Assigned_To_ID (FK, refer to Emp_ID)
• Project_ID (FK, refer to Project_ID)
• Product_ID (FK, refer to Product_ID)
• Status (can be New, OnHold, Cancel, Closed)

产品表

• 产品ID(PK)
• 产品名称

Project_Table

• Project_ID(PK)
• Project_Name

Employee_Table

• Emp_ID(PK)
• F_Name.

约束

• 在1个项目中->多个员工可以工作
• 1名员工->可以分配多个活动(Act_ID)
• 在任何给定时间点Status可以是任何给定值

• In 1 Project --> Multiple employees can work
• 1 Employee -- > Can be assigned multiple activities (Act_ID)
• At any given point of time Status can be any of those given values

现在在我的SQL查询中，我要执行的是负载检查::

Now in my SQL query what I want to do is a load check ::

对于项目(ATT_Table中的Project_ID)和特定状态，分配给员工的活动(由Act_ID表示)由Assigned_To_ID表示.我需要从SQL查询中输出5个值:

An activity (represented by Act_ID) is assigned to an employee is represented by Assigned_To_ID, for Project (Project_ID in ATT_Table) and a particular status. I need to output 5 values from my SQL query:

1. 员工姓名
2. 项目名称
3. 已分配员工(在A中)的活动总数.
4. 已分配状态为新建"或处理中"的员工(在A中)的活动数.
5. 加载.就是= C/D * 100(很明显，C比D大).

现在可能存在这样的情况，即某个员工假设E1正在从事项目P1，P2.所以我的表输出将如下所示:

Now There may be a condition That an employee let's say E1 is working on project P1,P2. So my table output will be look like this::

A    B     C    D     E
E1   P1    
E1   P2 

因此，这里的C，D，E将具有与该项目(P1或P2)的活动相对应的值

So here C, D, E will be having values corresponding to activities for that Project(P1 or P2)

到目前为止，我已经尝试过::

I have tried this so far ::

SELECT 
    F_Name AS A, 
    Project_Name AS B, 
    Count(Act_ID) AS C, 
    Count(Act_ID) AS D
FROM 
    Employee_Table, ATT_Table, Project_Table
WHERE 
    ATT_table.[Assigned_To_ID] = Employee_Table.[Emp_ID] 
    AND Project_Table.[Project_ID] = ATT_Table.[Project_ID] 
    AND Count(Act_ID) IN (SELECT Count(Act_ID)
                          FROM ATT_Table
                          WHERE ATT_Table.[Status] <> 'Closed' 
                            AND ATT_Table.[Status] <> 'OnHold')
GROUP BY 
     F_Name, Project_Name;

我得到A，B，C.但是，当我尝试查找状态检查活动时，由于无法显示此消息而无法在WHERE子句中写入计数，因此无法执行此查询.请帮助我这一件事. 谢谢.

I am getting A, B, C. But when I try to find the activities for status check I cannot execute this query as it gives me a message cannot write count in WHERE clause. please help me on this one. Thanks..

推荐答案

您提到了Product_Table，但我看不出该表如何涉及您的问题.其余的，我将从子查询中提取列A，B，C和D，然后在外部查询中计算E.

You mentioned Product_Table, but I don't see how that table is involved in your question. For the rest, I would pull columns A, B, C, and D from a subquery, and compute E in the outer query.

SELECT
    sub.F_Name AS A,
    sub.Project_Name AS B,
    sub.C,
    sub.D,
    ((sub.C / sub.D) * 100) AS E
FROM
    (
        SELECT
            Emp.F_Name,
            Proj.Project_Name,
            Count(att.Act_ID) AS C,
            Sum(IIf(att.Status IN ('New', 'InProcess'), 1, 0)) AS D
        FROM
            (ATT_Table AS att
            INNER JOIN Employee_Table AS Emp
            ON Emp.Emp_ID = att.Assigned_To_ID)
            INNER JOIN Project_Table AS Proj
            ON Proj.Project_ID = att.Project_ID 
        GROUP BY
            Emp.F_Name,
            Proj.Project_Name
    ) AS sub;

这篇关于需要有关我的SQL查询的帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！