[SQL] 여러 쿼리 같은 테이블하지만 서로 다른 열 MySQL의에서

여러 쿼리 같은 테이블하지만 서로 다른 열 MySQL의에서

나는 더 많은 열이 개 다른 테이블의 결과를 요약 얻으려고

SET @start_res = 20150301;
SET @finish_res= 20150501;
SET @finish_check= 20150801;
SET @start_check= 20150301;
SET @daily_hos= 3;

SELECT* from
   ( SELECT COUNT(DAY_IN) AS arr FROM t_hospital WHERE DAY_IN between  @start_check and @finish_check and RES_DATE between @start_res and @finish_res  and ID_daily_hos =@daily_hos group by DAY_IN  )e,
     (SELECT COUNT(PAT_STATUS) AS ONG1 FROM t_hospital WHERE PAT_STATUS  like '%ong%' and  DAY_IN between @start_check and @finish_check and RES_DATE between @start_res and @finish_res  and ID_daily_hos =@daily_hos group by DAY_IN ) a,
        (SELECT COUNT(PAT_STATUS) AS RTED FROM t_hospital WHERE PAT_STATUS  like '%rtde%'and  DAY_IN between  @start_check and @finish_check and RES_DATE between @start_res and @finish_res  and ID_daily_hos =@daily_hos group by DAY_IN  )b,
           (SELECT COUNT(PAT_STATUS) AS POLI FROM t_hospital WHERE PAT_STATUS  like '%pol%'and DAY_IN between @start_check and @finish_check and RES_DATE between @start_res and @finish_res  and ID_daily_hos =@daily_hos group by DAY_IN   )c,
              (SELECT COUNT(PAT_STATUS) AS para FROM t_hospital WHERE PAT_STATUS  like '%para%' and  DAY_IN between @start_check and @finish_check and RES_DATE between @start_res and @finish_res  and ID_daily_hos =@daily_hos group by DAY_IN  )d

다른 사람이 잘못된 출력을 표시하면서 물론 그것은하지 작업, 그냥 먼저 표시 열 (편곡)를 작동 않습니다.

어디에서 잘못입니까?

해결법

1. ==============================

   1.이것은 매우 일반적인 패턴이다 :

   이것은 매우 일반적인 패턴이다 :

   SELECT DAY_IN, COUNT(*) AS arr,
           SUM(IF(PAT_STATUS  like '%ong%', 1, 0)) AS ONG1,
           SUM(IF(PAT_STATUS  like '%rtde%', 1, 0)) AS RTED,
           SUM(IF(PAT_STATUS  like '%pol%', 1, 0)) AS POL1,
           SUM(IF(PAT_STATUS  like '%para%', 1, 0)) AS para
   FROM t_hospital 
   WHERE DAY_IN between @start_check and @finish_check 
       and RES_DATE between @start_res and @finish_res  
       and ID_daily_hos =@daily_hos
   GROUP BY DAY_IN
   

2. ==============================

   2.SQL 서버 2005 +, 당신은 윈도우 기능을 사용할 수 있습니다. 그러나 MySQL은 지원되지 않습니다.

   SQL 서버 2005 +, 당신은 윈도우 기능을 사용할 수 있습니다. 그러나 MySQL은 지원되지 않습니다.

   하위 쿼리에서 SELECT 문은 운영자에 가입되어 있어야합니다. 이 같이 :

   SELECT * FROM (
       (SELECT COUNT(..) ...) AS C1
          INNER JOIN (SELECT COUNT(...) ...) AS C2
               ON <condition>
          INNER JOIN ... )
   

3. ==============================

   3.이 시도:-

   이 시도:-

   SET @start_res = 20150301;
   SET @finish_res= 20150501;
   SET @finish_check= 20150801;
   SET @start_check= 20150301;
   SET @daily_hos= 3;
   
   SELECT 
   
   (SELECT COUNT(DAY_IN) AS arr FROM t_hospital WHERE 
   DAY_IN between  @start_check and @finish_check and 
   RES_DATE between @start_res and @finish_res and 
   ID_daily_hos =@daily_hos) e,
   
   (SELECT COUNT(PAT_STATUS) AS ONG1 FROM t_hospital WHERE 
   PAT_STATUS  like '%ong%' and  
   DAY_IN between @start_check and @finish_check and 
   RES_DATE between @start_res and @finish_res  and 
   ID_daily_hos =@daily_hos) a,
   
   (SELECT COUNT(PAT_STATUS) AS RTED FROM t_hospital WHERE 
   PAT_STATUS  like '%rtde%' and  
   DAY_IN between  @start_check and @finish_check and 
   RES_DATE between @start_res and @finish_res  and 
   ID_daily_hos =@daily_hos)b,
   
   (SELECT COUNT(PAT_STATUS) AS POLI FROM t_hospital WHERE 
   PAT_STATUS  like '%pol%' and 
   DAY_IN between @start_check and @finish_check and 
   RES_DATE between @start_res and @finish_res  and 
   ID_daily_hos =@daily_hos) c,
   
   (SELECT COUNT(PAT_STATUS) AS para FROM t_hospital WHERE 
   PAT_STATUS  like '%para%' and  
   DAY_IN between @start_check and @finish_check and 
   RES_DATE between @start_res and @finish_res  and 
   ID_daily_hos =@daily_hos) d
   

   귀하의 질문은 두 테이블을 조인에 대해 언급하지만 문제에만 t_hospital이있을 나타납니다. 당신은 다른 테이블에 대한 정보와 질문을 업데이트 할 수 있습니다.

4. ==============================

   4.내가 제대로 이해하면, 여기 당신이 원하는입니다.

   내가 제대로 이해하면, 여기 당신이 원하는입니다.

   SET @start_res = 20150301;
   SET @finish_res= 20150501;
   SET @finish_check= 20150801;
   SET @start_check= 20150301;
   SET @daily_hos= 3;
   
   SELECT 
   
   (SELECT COUNT(DAY_IN) FROM t_hospital WHERE 
   DAY_IN between  @start_check and @finish_check and 
   RES_DATE between @start_res and @finish_res and 
   ID_daily_hos =@daily_hos) AS 'arr' ,
   
   (SELECT COUNT(PAT_STATUS) FROM t_hospital WHERE 
   PAT_STATUS  like '%ong%' and  
   DAY_IN between @start_check and @finish_check and 
   RES_DATE between @start_res and @finish_res  and 
   ID_daily_hos =@daily_hos) AS 'ONG1' ,
   
   (SELECT COUNT(PAT_STATUS) FROM t_hospital WHERE 
   PAT_STATUS  like '%rtde%' and  
   DAY_IN between  @start_check and @finish_check and 
   RES_DATE between @start_res and @finish_res  and 
   ID_daily_hos =@daily_hos) AS 'RTED' ,
   
   (SELECT COUNT(PAT_STATUS) FROM t_hospital WHERE 
   PAT_STATUS  like '%pol%' and 
   DAY_IN between @start_check and @finish_check and 
   RES_DATE between @start_res and @finish_res  and 
   ID_daily_hos =@daily_hos) AS 'POLI' ,
   
   (SELECT COUNT(PAT_STATUS) FROM t_hospital WHERE 
   PAT_STATUS  like '%para%' and  
   DAY_IN between @start_check and @finish_check and 
   RES_DATE between @start_res and @finish_res  and 
   ID_daily_hos =@daily_hos) AS 'para'
   

from https://stackoverflow.com/questions/33139000/multiple-query-same-table-but-in-different-columns-mysql by cc-by-sa and MIT license