إضافة عمودين إضافيين لتعيين النتيجة عن طريق الانضمام إلى جداول أخرى

1

لدي 4 طاولات:

جدول المهام

 (task_id , department_id , task_title , task_description , task_start_date , task_due_date , task_rating , task_is_completed)

جدول الموظفين

 (employee_id , department_id , employee_name , employee_salary ,  employee_hire_date)

جدول الأقسام

 (department_id , department_name)

انضمام الموظفين في الجدول

 (employee_id , task_id)

كل جدول هو كيان في قاعدة بيانات الغرفة.

أريد إرجاع عمودين إضافيين مع ( select * from employees ) واحد لحساب تصنيف الموظف (عن طريق الحصول على متوسط task_rating العمود في جدول المهام يجب إكمال المهام) والعمود الآخر هو إظهار عدد المهام قيد التشغيل لهذا الموظف (من خلال الحصول على عدد الصفوف في المهام مع task_is_completed = 0 )

أنا لا أعرف الجدول الذي أضيف معه الجدول. تمكنا من عمل عبارتي SQL منفصلتين تعيدان هذين العمودين باستخدام صلات الاتحاد واليسار لكنهما قبيحتان جدًا وعندما لا يعملان عند دمجهما.

ما حاولنا

select employees.employee_name , employees.employee_id ,avg(tasks.task_rating)  as Ratings from employees , tasks inner join employees_tasks on(employees.employee_id = employees_tasks.employee_id )AND tasks.task_id = employees_tasks.task_id where tasks.task_is_completed = 1 group by (employees.employee_name ) 
union select employees.employee_name, employees.employee_id, avg(0) as Ratings from employees where employees.employee_id  not in (select employees.employee_id from employees , tasks inner join employees_tasks on(employees.employee_id = employees_tasks.employee_id ) AND tasks.task_id = employees_tasks.task_id where tasks.task_is_completed = 1 group by (employees.employee_name ) ) group by employees.employee_id order by employees.employee_id ;

select employees.employee_name , employees.employee_id ,count(tasks.task_title)  as tasks_Running from employees , tasks inner join employees_tasks on(employees.employee_id = employees_tasks.employee_id )AND tasks.task_id = employees_tasks.task_id where tasks.task_is_completed = 0  group by (employees.employee_name ) 
union select employees.employee_name , employees.employee_id ,0   as tasks_Running  from employees  where (employees.employee_id  not in (select employees.employee_id from employees , tasks inner join employees_tasks on(employees.employee_id = employees_tasks.employee_id )AND tasks.task_id = employees_tasks.task_id where tasks.task_is_completed = 0  group by (employees.employee_name )))group by (employees.employee_name) order by employees.employee_id ; 

نريد أن يكون الناتج على هذا النحو

 (employee_id , department_id , employee_name , employee_salary ,  employee_hire_date , ratings , numTasksRunning)

1 إجابة

0

أعتقد أن ما يلي قد يناسب: -

WITH 
  -- Common Table Expression 1 - Average of Completed Tasks per employee
    employee_completedtask_info AS (
        SELECT employees.employee_id,avg(tasks.task_rating) AS atr
            FROM employees_tasks
                JOIN tasks ON employees_tasks.task_id = tasks.task_id
                JOIN employees ON employees_tasks.employee_id = employees.employee_id
            WHERE tasks.task_is_completed > 0
            GROUP BY employees.employee_id
    ),
    -- Common Table Expression 2 - Incompleted Taks per employee
    employee_notcompleted_info AS (
        SELECT employees.employee_id,count() AS itc
            FROM employees_tasks
                JOIN tasks ON employees_tasks.task_id = tasks.task_id
                JOIN employees ON employees_tasks.employee_id = employees.employee_id
            WHERE tasks.task_is_completed = 0
            GROUP BY employees.employee_id
    ),
    -- Common Table Expression 3 - Total Tasks per Employee
    employee_total_tasks AS (
        SELECT employees.employee_id,count() AS ttc
            FROM employees_tasks
                JOIN tasks ON employees_tasks.task_id = tasks.task_id
                JOIN employees ON employees_tasks.employee_id = employees.employee_id
            GROUP BY employees.employee_id
    )
    SELECT employees.employee_name, 
        CASE WHEN atr IS NOT NULL THEN atr ELSE 0 END AS average_completed_task_rating,
        CASE WHEN itc IS NOT NULL THEN itc ELSE 0 END AS incomplete_task_count,
        CASE WHEN ttc IS NOT NULL THEN ttc ELSE 0 END AS total_task_count
        FROM employees 
            LEFT JOIN employee_completedtask_info ON employees.employee_id = employee_completedtask_info.employee_id
            LEFT JOIN employee_notcompleted_info ON employees.employee_id = employee_notcompleted_info.employee_id
            LEFT JOIN employee_total_tasks ON employees.employee_id = employee_total_tasks.employee_id
    ;

بناءً على البيانات التي تم إنشاؤها وفقًا لما يلي: -

DROP TABLE IF EXISTS employees;
CREATE TABLE IF NOT EXISTS employees (employee_id INTEGER PRIMARY KEY, department_id INTEGER, employee_name TEXT, employee_salary REAL,  employee_hire_date TEXT);
DROP TABLE IF EXISTS departments;
CREATE TABLE IF NOT EXISTS departments (department_id INTEGER PRIMARY KEY, department_name TEXT);
DROP TABLE IF EXISTS employees_tasks;
CREATE TABLE IF NOT EXISTS employees_tasks (employee_id INTEGER, task_id INTEGER, PRIMARY KEY(employee_id, task_id));

INSERT INTO departments VALUES
    (null,'Maths'),(null,'English'),(null,'Craft')
;
INSERT INTO employees VALUES
    (null,1,'Fred',55000,'2000-01-02'),
    (null,2,'Mary',62000,'1996-03-20'),
    (null,3,'Tom',52000,'2004-10-11'),
    (null,3,'Susan',72000,'1999-06-14'),
    (null,2,'Bert',66000,'2000-10-15'),
    (null,1,'Jane',70000,'1992-04-02')
;
INSERT INTO tasks VALUES
    (null,3,'Task 001 - Craft','Do the Craft thinggy','2018-01-01','2018-08-19',10,0),
    (null,1,'Task 002 - Maths','Do the Maths thinggy','2018-03-14','2019-03-13',20,0),
    (null,2,'Task 003 - English','Do the English thinggy','2018-02-14','2018-09-14',8,0),
    (null,3,'Task 004 - Craft','Do the Craft job','2018-01-01','2018-08-19',10,1),
    (null,1,'Task 005 - Maths','Do the Maths job','2018-03-14','2019-03-13',20,1),
    (null,2,'Task 006 - English','Do the English job','2018-02-14','2018-09-14',8,1),
    (null,3,'Task 007 - Craft','Craft thinggy','2018-03-03','2018-11-21',10,0),
    (null,1,'Task 008 - Maths','Maths thinggy','2018-03-14','2019-03-13',20,0),
    (null,2,'Task 009 - English','English thinggy','2018-02-14','2018-09-14',8,0)
;
INSERT INTO employees_tasks VALUES
    (1,2),(1,5),(1,8),(1,6),
    (2,2),
  (3,1),(3,4),(3,7)
;

وينتج عنه :-

Imagen 689470

  • ملاحظة يحول هذا الإدخالات الفارغة إلى 0 (أي في ما سبق لا توجد مهام لسوزان وبيرت وجين بحيث تكون لاغية بالنسبة لعدد المهام / المتوسطات ، مما يعقد الأمور قليلاً ومن ثم الحالة عند ... ثم ... .. END AS شروط).
  • ملاحظة لقد قمت بتضمين إجمالي عدد المهام حيث قد يكون هذا مفيدًا / مطلوبًا (يستخلص CTE الثالث هذه المعلومات)
:مؤلف
فوق
قائمة طعام