在一条记录中以两种不同的方式聚合数据 [英] Aggregating data in two different ways within a single record

问题描述

可能重复:
需要有关我的SQL查询的帮助

Possible Duplicate:
Need help for my SQL query

好吧，我也可能早些时候发布了这篇文章，但是我的要求有所变化，所以请帮助我解决这个问题.

Well I may have posted this earlier also, but my requirements changed a bit, 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.

Emp_Product_Project_Mapping

(此表基本上映射了将哪个员工分配给哪个产品和哪个项目.在所有3列中允许重复输入，以映射可以将1个员工分配给多个项目或产品)

(This table basically maps which employee is assigned to which product and which project. Duplicates entry are allowed in all the 3 columns, to map that 1 employee can be assigned to multiple project or products)

• Emp_IDFK(FK指的是Emp_ID PK)
• Product_IDFK(FK表示Product_ID PK)
• Project_IDFK(FK指的是Project_ID PK)

约束

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

• 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.
• At any given point an activity can be assigned to 1 product and 1 project.

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

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

针对产品(ATT_table中的Product_ID)的项目(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) for product(Product_ID in ATT_table) and a particular status. I need to output 5 values from my SQL query:

1. 员工姓名
2. 产品名称
3. 为状态为新建"或处理中"的该产品(2个)分配的总活动数.(此处，我计算与该产品(2个)相关的所有活动.它与Employee(in 1)不相关.我要在我的第4个部分中进行分配.
4. 现在，我想要分配给Employee(在1中)的活动计数(在我从(3)中得到的总计数中).希望我能使自己清楚.第三是分配给产品的活动总数(第二).在第4次中，其活动超出了我第3次的结果，在第1次中分配给一名员工.
5. 加载.就是=(结果3)/(结果4)* 100((结果3)比(结果4)更大，这很明显.)

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

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

1    2     3    4     5
E1   P1    
E1   P2 

因此，这里的3、4、5将具有与该产品(P1或P2)的活动相对应的值

So here 3, 4, 5 will be having values corresponding to activities for that Product(P1 or P2)

请帮助我解决这一问题.使用MSaccess数据库. 谢谢.

Please help me on this one. Using MSaccess database. Thanks..

推荐答案

由于要以两种独立的方式对数据进行分组"，因此无法使用单个GROUP BY子句来完成此操作.您必须改为使用子查询计算每个产品的活动总数；在这里，我还使用子查询来计算每个员工在这些产品上的活动，因为我认为比将一个GROUP BY子查询与父项中的GROUP BY混合起来更容易理解:

Since you want to "group" your data in two independent ways, you can't do it with a single GROUP BY clause. You must instead count the total number of activities for each product with a subquery; here I'm also using a subquery to count each employee's activities on those products, since I think it's easier to understand than mixing one GROUP BY subquery with a GROUP BY in the parent:

SELECT
  Employee_Table.F_Name,
  Product_Table.Product_Name,
  AllActivities.Count,
  EmployeeActivities.Count,
  EmployeeActivities.Count / AllActivities.Count AS Load
FROM
  Employee_Table, Product_Table
  INNER JOIN (
    SELECT Product_ID, COUNT(*) AS Count
    FROM ATT
    GROUP BY Product_ID
  ) AS AllActivities ON
    AllActivities.Product_ID = Product_Table.Product_ID
  INNER JOIN (
    SELECT Assigned_To_ID, Product_ID, COUNT(*) AS Count
    FROM ATT
    GROUP BY Assigned_To_ID, Product_ID
  ) AS EmployeeActivities ON
        EmployeeActivities.Assigned_To_ID = Employee_Table.Emp_ID
    AND EmployeeActivities.Product_ID     = Product_Table.Product_ID

这篇关于在一条记录中以两种不同的方式聚合数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！