在mysql中使用别名来计算条件

Question

select 
    delegation_cycle.delegation_cycle_id as task_id,
    delegation_cycle.delegate_from_id as from_id,
    delegation_cycle.delegate_to_id as to_id,
    delegation_cycle.time_estimate as main_time_estimate,
    delegation_cycle.status as main_task_status,
    delegation_cycle.start_date as main_start_date,
    delegation_cycle.changes,
    DATE(delegation_cycle.delegated_time) as orginal_email_time,
    tasks.task_name,
    tasks.client_id,
    tasks.followup,
    tasks.task_owner,
    tasks.main_due_date,
    tasks.main_priority,
    1 as delegated,
    delegation_user_relation.showinlist,
    users.email as email_from,
    clients.client_name,
    (select 
            count(delegation_cycle_id)
        from
            delegation_cycle
        where
            delegation_cycle.task_id = tasks.task_id
                and delegation_cycle.task_parent = '2') as delegationcount,
    (select 
            sum(status)
        from
            delegation_cycle
        where
            delegation_cycle.task_id = tasks.task_id
                and delegation_cycle.task_parent = '2'
                and delegation_cycle.status = '4') as sumstatus,
    (select 
            count(notes_records_id)
        from
            notes_records
                join
            notes ON notes.notes_id = notes_records.notes_id
        where
            notes_to = '2'
                and notes.task_id = delegation_cycle.task_id) as notescount,
    (select 
            count(notes_records_id)
        from
            notes_records
                join
            notes ON notes.notes_id = notes_records.notes_id
        where
            notes_to = '2'
                and notes.task_id = delegation_cycle.task_id
                and notes_records.read = 0) as unreadnotescount
from
    delegation_cycle
        inner join
    tasks ON tasks.task_id = delegation_cycle.task_id
        inner join
    users ON users.user_id = delegation_cycle.delegate_from_id
        inner join
    clients ON clients.client_id = tasks.client_id
        join
    delegation_user_relation ON delegation_user_relation.delegate_task_id = tasks.task_id
where
    delegation_cycle.delegate_to_id = '2'
        and delegation_cycle.status != 4
        and case
        when
            delegation_cycle.status != 3
        then
            (delegation_user_relation.showinlist = 1
                || delegation_user_relation.showinlist = 2)
                and delegation_user_relation.user_id = '2'

        else delegation_user_relation.user_id = '2'
    end

嗨，这个查询正在运行，我想用case语句在where条件下检查use delegationcount和sumstatus

 when delegation_cycle.status != 3 && delegationcount*4!=sumstatus then 
(delegation_user_relation.showinlist = 1 || delegation_user_relation.showinlist = 2)  
 and delegation_user_relation.user_id = '2'  else delegation_user_relation.user_id = '2'    
 end

我想在where条件下使用alise名称，但是这个查询对我来说是不可能的，所以我想重新构建这个查询，以便在where条件下使用alise。

Answer 1

可以按照查询定义临时会话变量，并在where子句中使用相同的语句。

select 
    delegation_cycle.delegation_cycle_id as task_id,
    delegation_cycle.delegate_from_id as from_id,
    delegation_cycle.delegate_to_id as to_id,
    delegation_cycle.time_estimate as main_time_estimate,
    delegation_cycle.status as main_task_status,
    delegation_cycle.start_date as main_start_date,
    delegation_cycle.changes,
    DATE(delegation_cycle.delegated_time) as orginal_email_time,
    tasks.task_name,
    tasks.client_id,
    tasks.followup,
    tasks.task_owner,
    tasks.main_due_date,
    tasks.main_priority,
    1 as delegated,
    delegation_user_relation.showinlist,
    users.email as email_from,
    clients.client_name,

    @delegationcount:=(select 
            count(delegation_cycle_id)
        from
            delegation_cycle
        where
            delegation_cycle.task_id = tasks.task_id
                and delegation_cycle.task_parent = '2') as delegationcount,

    @sumstatus:=(select 
            sum(status)
        from
            delegation_cycle
        where
            delegation_cycle.task_id = tasks.task_id
                and delegation_cycle.task_parent = '2'
                and delegation_cycle.status = '4') as sumstatus,

    @notescount:=(select 
            count(notes_records_id)
        from
            notes_records
                join
            notes ON notes.notes_id = notes_records.notes_id
        where
            notes_to = '2'
                and notes.task_id = delegation_cycle.task_id) as notescount,

    @unreadnotescount:=(select 
            count(notes_records_id)
        from
            notes_records
                join
            notes ON notes.notes_id = notes_records.notes_id
        where
            notes_to = '2'
                and notes.task_id = delegation_cycle.task_id
                and notes_records.read = 0) as unreadnotescount
from
    delegation_cycle
        inner join
    tasks ON tasks.task_id = delegation_cycle.task_id
        inner join
    users ON users.user_id = delegation_cycle.delegate_from_id
        inner join
    clients ON clients.client_id = tasks.client_id
        join
    delegation_user_relation ON delegation_user_relation.delegate_task_id = tasks.task_id
where
    delegation_cycle.delegate_to_id = '2'
        and delegation_cycle.status != 4
        and case
        when
            delegation_cycle.status != 3
        then
            (delegation_user_relation.showinlist = 1
                || delegation_user_relation.showinlist = 2)
                and delegation_user_relation.user_id = '2'

        else delegation_user_relation.user_id = '2'
    end

并将新的when ...子句更改如下：

when 
  delegation_cycle.status != 3 && 
  @delegationcount*4!=@sumstatus then 
    (delegation_user_relation.showinlist = 1 || 
     delegation_user_relation.showinlist = 2
    ) and delegation_user_relation.user_id = '2' 
  else
    delegation_user_relation.user_id = '2'    
end