根据假期类型（如Exam，Unpaid，Annual）从mysql数据库中提取数据 [英] fetch data from mysql database on the basis of leave types such as Exam, Unpaid, Annual

问题描述

这是我的数据库表

：

这是我要显示数据的我的前端：

我需要的是离职记录类型和半天全天。从左到右员工姓名，然后每年计数在半天= 1在db和离开类型=年度等，最后显示一些列中的总列和half_day年度，考试和未付的乘以0.5。 / p>

我尝试了：

  SELECT users.name，leave_type，count （leaves.id）
 FROM leaves 
 INNER JOIN用户ON users.id = leaves.employee 
 GROUP BY leaves.leave_type，users.name 
  

---

  SELECT leave。*，count（users.id）
 FROM leaves 
 LEFT JOIN users ON users.id = leaves.employee 
 GROUP BY users.name 
  

提前感谢，请帮助。

解决方案

好的，让我们试试看，有用。

这是我有的一个模式。它非常简单，但支持您的用例。

  CREATE TABLE employees（
 id int unsigned auto_increment，
 name varchar（255），
 PRIMARY KEY（id）
）; 
 
 CREATE TABLE leave_type（
 id int unsigned auto_increment，
 name varchar（255），
 PRIMARY KEY（id）
） 
 
 CREATE TABLE leave_log（
 id int unsigned auto_increment，
 leave_type_id int unsigned，
 employee_id int unsigned，
 is_full_day int unsigned，
 is_half_day int unsigned，
 PRIMARY KEY（id）
）; 
  

一些测试数据...

  INSERT INTO employees VALUES（14，'Lisa'），（15，'Homer'），（13，'Bart'） 
 INSERT INTO leave_type VALUES（1，'Annual'），（2，'Unpaid'），（3，'Exam'）; 
 INSERT INTO leave_log VALUES（NULL，3,14,1,0），（NULL，1，14，1，0），（NULL，1，14，0，1）， ，0,1）; 
 INSERT INTO leave_log VALUES（NULL，2，15，0，1）; 
 INSERT INTO leave_log VALUES（NULL，3，13，1，0），（NULL，1，13，1，0）; 
  

不要过于关注列名和定义，我没有抛光模式，对你的应用程序有足够的了解。

一旦建立了模式并且数据在那里，这个简单的查询就可以做你想要的。

  SELECT e.name，SUM（annual.is_half_day），SUM（unpaid.is_half_day），SUM（exam.is_half_day），
 SUM（annual.is_full_day），SUM（unpaid.is_full_day），SUM（exam.is_full_day）
 FROM employees e 
 LEFT JOIN leave_log annual ON annual.leave_type_id = 1 AND annual.employee_id = e.id 
 LEFT JOIN leave_log unpaid ON unpaid.leave_type_id = 2 AND unpaid.employee_id = e.id 
 LEFT JOIN leave_log exam on exam.leave_type_id = 3 AND exam.employee_id = e.id 
 GROUP BY e .id 
  

看看你的想法。这可能是一个高性能或重负载应用程序？

EDIT

此查询涉及更多，但可能更准确。

  SELECT e.name，e.id，
 IFNULL（annual_half.total，0 ）annual_half，
 IFNULL（unpaid_half.total，0）unpaid_half，
 IFNULL（exam_half.total，0）exam_half，
 IFNULL（annual_full.total，0）annual_full，
 IFNULL （unpaid_full.total，0）unpaid_full，
 IFNULL（exam_full.total，0）exam_full 
 FROM employees e 
 LEFT JOIN（SELECT SUM（is_full_day）as total，employee_id，leave_type_id FROM leave_log WHERE is_full_day = 1 AND leave_type_id = 1 GROUP BY 3，2）annual_full ON annual_full.employee_id = e.id 
 LEFT JOIN（SELECT SUM（is_full_day）as total，employee_id，leave_type_id FROM leave_log WHERE is_full_day = 1 AND leave_type_id = 2 GROUP BY 3，2）unpaid_full ON unpaid_full.employee_id = e.id 
 LEFT JOIN（SELECT SUM（is_full_day）as total，employee_id，leave_type_id FROM leave_log WHERE is_full_day = 1 AND leave_type_id = 3 GROUP BY 3，2）exam_full ON exam_full.employee_id = e.id 
 LEFT JOIN（SELECT SUM（is_half_day）as total，employee_id，leave_type_id FROM leave_log WHERE is_half_day = 1 AND leave_type_id = 1 GROUP BY 3，2）annual_half ON annual_half.employee_id = e。 id 
 LEFT JOIN（SELECT SUM（is_half_day）as total，employee_id，leave_type_id FROM leave_log WHERE is_half_day = 1 AND leave_type_id = 2 GROUP BY 3，2）unpaid_half ON unpaid_half.employee_id = e.id 
 LEFT JOIN （SELECT SUM（is_half_day）as total，employee_id，leave_type_id FROM leave_log WHERE is_half_day = 1 AND leave_type_id = 3 GROUP BY 3，2）exam_half ON exam_half.employee_id = e.id 
 GROUP BY 1; 
  

This is my Database table

:

This is my front end where i want display data:

What I need is employee leaves record on the basic of leave types and half day full day. from left to right employee name then annual count on the basis of half day = 1 in db and leave type = annual so on and in the last show some of all columns in total column and half_day annual, exam and unpaid multiply with 0.5.

What I tried:

SELECT users.name, leave_type, count( leaves.id )
  FROM leaves
       INNER JOIN users ON users.id = leaves.employee
 GROUP BY leaves.leave_type, users.name

---

SELECT leaves.*, count( users.id ) 
  FROM leaves 
       LEFT JOIN users ON users.id=leaves.employee 
 GROUP BY users.name

Thanks in advance, please help.

解决方案

Ok, so, lets try this and see if it works.

This is the schema I have some up with. Its very simple but supports your use case.

CREATE TABLE employees (
  id int unsigned auto_increment,
  name varchar(255),
  PRIMARY KEY(id)
);

CREATE TABLE leave_type (
  id int unsigned auto_increment,
  name varchar(255),
  PRIMARY KEY(id)
);

CREATE TABLE leave_log (
  id int unsigned auto_increment,
  leave_type_id int unsigned,
  employee_id int unsigned,
  is_full_day int unsigned,
  is_half_day int unsigned,
  PRIMARY KEY(id)
);

Some test data ...

INSERT INTO employees VALUES (14, 'Lisa'), (15, 'Homer'), (13, 'Bart');
INSERT INTO leave_type VALUES (1, 'Annual'), (2, 'Unpaid'), (3, 'Exam');
INSERT INTO leave_log VALUES (NULL, 3, 14, 1, 0), (NULL, 1, 14, 1, 0), (NULL, 1, 14, 0, 1), (NULL, 1, 14, 0, 1);
INSERT INTO leave_log VALUES (NULL, 2, 15, 0, 1);
INSERT INTO leave_log VALUES (NULL, 3, 13, 1, 0), (NULL, 1, 13, 1, 0);

Dont focus too much on column names and definitions, I did not polish the schema at all as I don't know enough about your app to do this.

Once the schema is made and data in there, this pretty simple query should do what you want.

SELECT e.name, SUM(annual.is_half_day), SUM(unpaid.is_half_day), SUM(exam.is_half_day), 
       SUM(annual.is_full_day), SUM(unpaid.is_full_day), SUM(exam.is_full_day)
  FROM employees e
       LEFT JOIN leave_log annual ON annual.leave_type_id = 1 AND annual.employee_id = e.id
       LEFT JOIN leave_log unpaid ON unpaid.leave_type_id = 2 AND unpaid.employee_id = e.id
       LEFT JOIN leave_log exam ON exam.leave_type_id = 3 AND exam.employee_id = e.id
 GROUP BY e.id

Have a look and see what you think. Is this likely to be a high performance or heavily loaded application?

EDIT

This query is more involved and would probably have some performance drawbacks but may be more accurate.

SELECT e.name, e.id,     
       IFNULL(annual_half.total, 0) annual_half,
       IFNULL(unpaid_half.total, 0) unpaid_half,
       IFNULL(exam_half.total, 0) exam_half,
       IFNULL(annual_full.total, 0) annual_full,
       IFNULL(unpaid_full.total, 0) unpaid_full,
       IFNULL(exam_full.total, 0) exam_full
  FROM employees e    
       LEFT JOIN ( SELECT SUM(is_full_day) as total, employee_id, leave_type_id FROM leave_log WHERE is_full_day = 1 AND leave_type_id = 1 GROUP BY 3, 2) annual_full ON annual_full.employee_id = e.id    
       LEFT JOIN ( SELECT SUM(is_full_day) as total, employee_id, leave_type_id FROM leave_log WHERE is_full_day = 1 AND leave_type_id = 2 GROUP BY 3, 2) unpaid_full ON unpaid_full.employee_id = e.id    
       LEFT JOIN ( SELECT SUM(is_full_day) as total, employee_id, leave_type_id FROM leave_log WHERE is_full_day = 1 AND leave_type_id = 3 GROUP BY 3, 2) exam_full ON exam_full.employee_id = e.id    
       LEFT JOIN ( SELECT SUM(is_half_day) as total, employee_id, leave_type_id FROM leave_log WHERE is_half_day = 1 AND leave_type_id = 1 GROUP BY 3, 2) annual_half ON annual_half.employee_id = e.id    
       LEFT JOIN ( SELECT SUM(is_half_day) as total, employee_id, leave_type_id FROM leave_log WHERE is_half_day = 1 AND leave_type_id = 2 GROUP BY 3, 2) unpaid_half ON unpaid_half.employee_id = e.id    
       LEFT JOIN ( SELECT SUM(is_half_day) as total, employee_id, leave_type_id FROM leave_log WHERE is_half_day = 1 AND leave_type_id = 3 GROUP BY 3, 2) exam_half ON exam_half.employee_id = e.id
 GROUP BY 1;

这篇关于根据假期类型（如Exam，Unpaid，Annual）从mysql数据库中提取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！