MySQL Union 未顯示第二個查詢結果集

我正在執行 2 個查詢的聯合併嘗試在兩個不同的列上獲取數據，但是聯合沒有從第二個結果集中獲取值。你能指導我嗎？

select  *
   From  
     ( SELECT  c1.ia_device_home_carrier, avg(rf1.ia_signal_strengh_rssi) as ss
           from  rf_voice_data_verizon_sec2 rf1, device_market_config d1,
               ia_device_carrier c1
           where  rf1.ia_device_id = d1.ia_device_id
             and  rf1.ia_device_id = c1.ia_device_id
             and  rf1.ia_signal_strengh_rssi BETWEEN -150.0 AND -40.0
             and  d1.ia_project_name = 'AA'
           group by  ia_device_home_carrier
        union 
        select  c2.ia_device_home_carrier, avg(rf2.httpul) as http
           from  rf_voice_data_verizon_sec2 rf2, device_market_config d2,
               ia_device_carrier c2
           where  rf2.ia_device_id = d2.ia_device_id
             and  rf2.ia_device_id = c2.ia_device_id
             and  rf2.httpul > 0
             and  d2.ia_project_name = 'AA'
           group by  ia_device_home_carrier 
     ) x
   group by  ia_device_home_carrier

                           +------------------------+----------+
                           | ia_device_home_carrier | ss       |
                           +------------------------+----------+
                           | xyz                    | -77.8559 |
                           | abcggg                 | -69.8158 |
                           | sfg                    | -95.8884 |
                           | VZW                    | -94.3992 |
                           +------------------------+----------+

但我需要這樣的結果集

                           +------------------------+----------+------------+
                           | ia_device_home_carrier | ss       |   httpul   |
                           +------------------------+----------+------------+
                           | xyz                    | -77.8559 |  7848.5175 |
                           | abcggg                 | -69.8158 |  5623.5231 |
                           | sfg                    | -95.8884 |  7228.5677 |
                           | VZW                    | -94.3992 |  7118.3175 |
                           +------------------------+----------+------------+

由於UNION排列列，而不是列名，因此您不能直接從您擁有的內容中獲取。但是，這裡有一種技術：

代替

SELECT * FROM (
   SELECT x, ss     FROM...
   UNION
   SELECT x, httpul FROM...
)

做

SELECT  x,
       MAX(ss) AS ss,  -- MAX() will ignore the NULL from the 'other' table
       MAX(httpul) AS httpul
FROM (
   SELECT x,         ss, NULL AS httpul  FROM ...
   UNION
   SELECT x, NULL AS ss,         httpul  FROM ...
)

嘗試這個：

SELECT  SS.IA_DEVICE_HOME_CARRIER ,SS.SS ,HTTP.HTTP
   FROM  
     ( SELECT  C1.IA_DEVICE_HOME_CARRIER, AVG(RF1.IA_SIGNAL_STRENGH_RSSI) AS SS
           FROM  RF_VOICE_DATA_VERIZON_SEC2 RF1, DEVICE_MARKET_CONFIG D1,
               IA_DEVICE_CARRIER C1
           WHERE  RF1.IA_DEVICE_ID = D1.IA_DEVICE_ID
             AND  RF1.IA_DEVICE_ID = C1.IA_DEVICE_ID
             AND  RF1.IA_SIGNAL_STRENGH_RSSI BETWEEN -150.0 AND -40.0
             AND  D1.IA_PROJECT_NAME = 'AA'
           GROUP BY  IA_DEVICE_HOME_CARRIER
     ) SS
   INNER JOIN  
     ( SELECT  C2.IA_DEVICE_HOME_CARRIER, AVG(RF2.HTTPUL) AS HTTP
           FROM  RF_VOICE_DATA_VERIZON_SEC2 RF2, DEVICE_MARKET_CONFIG D2,
               IA_DEVICE_CARRIER C2
           WHERE  RF2.IA_DEVICE_ID = D2.IA_DEVICE_ID
             AND  RF2.IA_DEVICE_ID = C2.IA_DEVICE_ID
             AND  RF2.HTTPUL > 0
             AND  D2.IA_PROJECT_NAME = 'AA'
           GROUP BY  IA_DEVICE_HOME_CARRIER
     ) HTTP ON SS.IA_DEVICE_HOME_CARRIER=HTTP.IA_DEVICE_HOME_CARRIER

引用自：https://dba.stackexchange.com/questions/131011