홈>2020-09-21 21:44source

sql - 현재 상태 쿼리 MySQL에서 총 개수 가져 오기

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

mysql sql database

답변 # 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

Python2.net

sql - 현재 상태 쿼리 MySQL에서 총 개수 가져 오기

관련 자료