>source

mysql 쿼리에서 현재 상태의 총 데이터를 계산하는 데 문제가 있습니다.

간단히, 나는 서로 관련된 3 개의 테이블이 있습니다.

m_shift_schedule     site_shift_schedule      m_ticket
================     ===================     ============
* shift_id   ======>  * shift_id             * ticket_id
* start_time          * shift_date           * ticket_status
* end_time            * user_id   ========>  * ticket_served_by

내 질문 :

SELECT user_id,
case when count(m_ticket.ticket_served_by)<2 then 'idle' 
     when count(m_ticket.ticket_served_by)=2 then 'Busy' 
     when count(m_ticket.ticket_served_by)>2 then 'Overload' end as status
FROM site_shift_schedule
LEFT JOIN m_shift_schedule ON site_shift_schedule.shift_id = m_shift_schedule.shift_id
LEFT JOIN m_ticket ON site_shift_schedule.user_id=m_ticket.ticket_served_by
WHERE site_shift_schedule.shift_date =  '2019-02-11' and m_ticket.ticket_status in (4,5,6)
AND CURRENT_TIME > start_time and CURRENT_TIME < end_time
group by user_id

위의 쿼리 결과 :

user_id                           Status
=============================   ============
ismail.rahman.saanin@random.co     Idle
lutfi.aldi.nugroho@random.co       Busy

질문 : 출력을 다음과 같이 표시하려면 어떻게해야합니까?

Status              Total
==================================
 Idle                 1
 Busy                 1


  • 답변 # 1

    아래에서 시도 할 수 있습니다-하위 쿼리 사용

    select status,count(user_id) as total
    from
    (
    SELECT user_id,
    case when count(m_ticket.ticket_served_by)<2 then 'idle' 
         when count(m_ticket.ticket_served_by)=2 then 'Busy' 
         when count(m_ticket.ticket_served_by)>2 then 'Overload' end as status
    FROM site_shift_schedule
    LEFT JOIN m_shift_schedule ON site_shift_schedule.shift_id = m_shift_schedule.shift_id
    LEFT JOIN m_ticket ON site_shift_schedule.user_id=m_ticket.ticket_served_by
    WHERE site_shift_schedule.shift_date =  '2019-02-11' and m_ticket.ticket_status in (4,5,6)
    AND CURRENT_TIME > start_time and CURRENT_TIME < end_time
    group by user_id
    )A group by status
    
    

  • 답변 # 2

    group by 에서도 사용 사례

    SELECT 
    case when count(m_ticket.ticket_served_by)<2 then 'idle' 
         when count(m_ticket.ticket_served_by)=2 then 'Busy' 
         when count(m_ticket.ticket_served_by)>2 then 'Overload' end as status,
         count(*) as total
    FROM site_shift_schedule
    LEFT JOIN m_shift_schedule ON site_shift_schedule.shift_id = m_shift_schedule.shift_id
    LEFT JOIN m_ticket ON site_shift_schedule.user_id=m_ticket.ticket_served_by
    WHERE site_shift_schedule.shift_date =  '2019-02-11' and m_ticket.ticket_status in (4,5,6)
    AND CURRENT_TIME > start_time and CURRENT_TIME < end_time
    group by  case when count(m_ticket.ticket_served_by)<2 then 'idle' 
         when count(m_ticket.ticket_served_by)=2 then 'Busy' 
         when count(m_ticket.ticket_served_by)>2 then 'Overload' end
    
    

관련 자료

  • 이전 django - 시리얼 라이저 필드의 이름이 잘못되어`GenericRelatedObjectManager` 인스턴스의 속성 또는 키와 일치하지 않을 수 있습니다
  • 다음 .net - 런타임에 Docker 이미지의 dotnet dll에 명령 줄 인수를 전달하는 방법은 무엇입니까?