php - MySQL Case 语句双重结果

Question

美好的一天!我正在处理我的项目，该项目需要指示时间是否存在、休假、假期。如果员工在场，我会计算从进到出的时间。如果休假和假期是有薪的，则总计薪时间就是他们的工作时间表。

Select `leave`.emp_id,
                    leave_request.date,
                    CASE  WHEN  leave.status_id = 4 AND leave_request.with_pay = 1  THEN 
                    ( 
                      SELECT TIMESTAMPDIFF(HOUR, w_s.time_in, w_s.time_out) 
                      FROM working_schedule w_s
                      JOIN employee_working_schedule ews ON w_s.id = ews.working_schedule_id
                      WHERE ews.employee_id = '01-0001' 
                    ) 
                    WHEN leave.status_id = 4 AND leave_request.with_pay = 0  THEN '0'
                    END AS status               
                    FROM `leave`
                    JOIN leave_request On `leave`.id = leave_request.`leave_id`
                    JOIN employee_logs AS e_l ON leave.emp_id = e_l.employee_id
                    WHERE `leave`.emp_id = '01-0001'    
                    UNION
                    SELECT e.employee_code AS id, 
                    CAST(e_l.time_in As date) day,
                    CASE    WHEN e_l.time_in IS NOT NULL THEN TIMESTAMPDIFF(HOUR, e_l.time_in, e_l.time_out)

                    FROM employee e
                    LEFT JOIN employee_logs e_l ON e.employee_code = e_l.employee_id
                    LEFT JOIN employee_company e_c ON e.employee_code = e_c.employee_id
                    LEFT JOIN company c ON e_c.company_id = c.id
                    JOIN employee_working_schedule ews On e.employee_code = ews.employee_id
                    JOIN working_schedule w_s On ews.working_schedule_id = w_s.id
                    WHERE e.employee_code = '01-0001' 
                    AND e_l.time_in BETWEEN '2017-09-01' AND '2017-09-28'
                    UNION
                    SELECT '01-0001',
                    CASE WHEN holiday.date BETWEEN '2017-09-01' AND '2017-09-28' THEN
                    (SELECT holiday.date) END AS date,
                    CASE WHEN holiday.date BETWEEN '2017-09-01' AND '2017-09-28' THEN 
                    ( 
                      SELECT TIMESTAMPDIFF(HOUR, w_s.time_in, w_s.time_out) 
                      FROM working_schedule w_s
                      JOIN employee_working_schedule ews ON w_s.id = ews.working_schedule_id
                      WHERE ews.employee_id = '01-0001' 
                    ) 
                    END AS status
                    FROM holiday    

So is it possible here to add another result. For example (CASE WHEN e_l.time_in IS NOT NULL THEN TIMESTAMPDIFF(HOUR, e_l.time_in, e_l.time_out) END AS hours_count) THEN Present END AS status

Answer 1

如果我没理解错的话，您想为状态选择语句添加另一列。也许你需要做的是这样的。

SELECT `leave`.emp_id,
       leave_request.date,
       CASE 
            WHEN leave.status_id = 4 AND leave_request.with_pay = 1 THEN (
                     SELECT TIMESTAMPDIFF(HOUR, w_s.time_in, w_s.time_out)
                     FROM   working_schedule w_s
                            JOIN employee_working_schedule ews
                                 ON  w_s.id = ews.working_schedule_id
                     WHERE  ews.employee_id = '01-0001'
                 )
            WHEN leave.status_id = 4 AND leave_request.with_pay = 0 THEN '0'
       END AS hours_count,
       CASE 
            WHEN leave.status_id = 4 AND leave_request.with_pay = 1 THEN 'Paid'
            WHEN leave.status_id = 4 AND leave_request.with_pay = 0 THEN 'Unpaid'
       END + ' Leave' AS Status
FROM   `leave`
       JOIN leave_request
            ON  `leave`.id = leave_request.`leave_id`
       JOIN employee_logs  AS e_l
            ON  leave.emp_id = e_l.employee_id
WHERE  `leave`.emp_id = '01-0001'
UNION

SELECT e.employee_code            AS id,
       CAST(e_l.time_in AS DATE) AS Date,
       CASE 
            WHEN e_l.time_in IS NOT NULL THEN TIMESTAMPDIFF(HOUR, e_l.time_in, e_l.time_out)
       END AS hours_count,
       'Present' AS Status
FROM   employee e
       LEFT JOIN employee_logs e_l
            ON  e.employee_code = e_l.employee_id
       LEFT JOIN employee_company e_c
            ON  e.employee_code = e_c.employee_id
       LEFT JOIN company c
            ON  e_c.company_id = c.id
       JOIN employee_working_schedule ews
            ON  e.employee_code = ews.employee_id
       JOIN working_schedule w_s
            ON  ews.working_schedule_id = w_s.id
WHERE  e.employee_code = '01-0001'
       AND e_l.time_in BETWEEN       '2017-09-01' AND '2017-09-28'
UNION

SELECT '01-0001',
       CASE 
            WHEN holiday.date BETWEEN '2017-09-01' AND '2017-09-28' THEN (
                     SELECT holiday.date
                 )
       END  AS DATE,
       CASE 
            WHEN holiday.date BETWEEN '2017-09-01' AND '2017-09-28' THEN (
                     SELECT TIMESTAMPDIFF(HOUR, w_s.time_in, w_s.time_out)
                     FROM   working_schedule w_s
                            JOIN employee_working_schedule ews
                                 ON  w_s.id = ews.working_schedule_id
                     WHERE  ews.employee_id = '01-0001'
                 )
       END  AS hours_count,
       'Holiday' AS Status
FROM   holiday    

只需在 hours_count 列旁边添加另一列。希望对您有所帮助。