multiple query same table but in different columns

2018-12-31 22:54发布

问题:

I\'m trying to get more columns summarizing the result from 2 different tables

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

and of course it does not work, just the first displayed column (arr) works while the other ones show a wrong output.

Where am I wrong?

回答1:

This is a pretty common pattern:

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:

In SQL Server 2005+, you can use Window functions. But it isn\'t supported in MySQL.

In Subquery, select statements must have join operator. Like this:

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


回答3:

Try this:-

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

Your question mentions about joining 2 tables but there appears to be only t_hospital in question. You might want to update your question with information about the other table.



回答4:

If i understand you correctly, here\'s what you want.

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\'


回答5:

I am curious about your error. it will help others to give you precise solution. Your query is fine it should return many rows , as you doing cross join so if first from portion[( 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, ] returns 4 and lets say others return 3,5,6 then you will get 4*5*6*3 =360 rows and I guess you do not want that. to get a precise answer run every portion of select query in phpmyAdmin/sqlyog print screen and paste it in SO with what you want.



标签: mysql sql