mysql - 如何集成 MySQL 子查询

Question

我有一个 MySQL 子查询，需要将其合并到我的主查询中，但我不知道如何合并。我的主要查询如下所示。我想运行它，并作为同一查询的一部分运行子查询并从中获取结果(恰好是一个总和)，然后比较主 MySQL 语句中子查询的结果。

主查询

select * from employees_timesheet where 
        (sign_off_1 = 'Director' or sign_off_2 = 'Director' or sign_off_3 = 'Director') 
        and
            (case when sign_off_1 <> 'Director' and sign_off_1_status = 'Compete' 
                then 1
                else 0
            end 
            + case when sign_off_2 <> 'Director' and sign_off_2_status = 'Compete' 
                then 1
                else 0
            end 
            + case when sign_off_3 <> 'Director' and sign_off_3_status = 'Compete' 
                then 1
                else 0
            end) = {{{$sign_offs_required}}}

子查询

select 
        SUM(
            case when employees_timesheet.sign_off_1 <> 'Director' 
                then 1
                else 0 
            end
            + case when employees_timesheet.sign_off_2 <> 'Director' 
                then 1
                else 0 
            end
            + case when employees_timesheet.sign_off_3 <> 'Director' 
                then 1
                else 0 
            end
        ) as sign_offs_required 
        from employees_timesheet

非常感谢任何帮助。

Answer 1

我设法使用以下查询让它工作:

select et.* from employees_timesheet et where 
        (et.sign_off_1 = 'Director' or et.sign_off_2 = 'Director' or et.sign_off_3 = 'Director') 
        and
            (case when et.sign_off_1 <> 'Director' and et.sign_off_1_status = 'Approved' 
                then 1
                else 0
            end 
            + case when et.sign_off_2 <> 'Director' and et.sign_off_2_status = 'Approved' 
                then 1
                else 0
            end 
            + case when et.sign_off_3 <> 'Director' and et.sign_off_3_status = 'Approved' 
                then 1
                else 0
            end) = 
                (case when et.sign_off_1 <> 'Director' 
                        then 1
                        else 0 
                    end
                    + case when et.sign_off_2 <> 'Director' 
                        then 1
                        else 0 
                    end
                    + case when et.sign_off_3 <> 'Director' 
                        then 1
                        else 0 
                    end
                )
        group by et.id