選擇多個列并從結果中洗掉NULL值

我正在嘗試在 MySQL 中創建一個查詢，但我遇到了困難。我已經可以選擇一列，但想選擇多個。這是我正在使用的代碼：

    SELECT
      e.IDCARTEIRA,
      (SELECT `VALORAGUA` FROM `INDICADORES.VW_CADASTRO_HIDROMETROPOLITANO` WHERE `IDCARTEIRA` = e.IDCARTEIRA AND `REFERENCIA_MES_ANO` = '202109') AS 'Ref 09 2021',
      (SELECT `VALORAGUA` FROM `INDICADORES.VW_CADASTRO_HIDROMETROPOLITANO` WHERE `IDCARTEIRA` = e.IDCARTEIRA AND `REFERENCIA_MES_ANO` = '202110') AS 'Ref 10 2021'
      FROM `INDICADORES.VW_CADASTRO_HIDROMETROPOLITANO` e
      GROUP BY e.IDCARTEIRA;

這是查詢結果：

IDCARTEIRA | Ref 09 2021 | Ref 10 2021
       1   |    39,76    |    (null)
       2   |   (null)    |    174,97
       3   |   (null)    |    (null)
       4   |   388,33    |    (null)
       5   |   (null)    |    (null)

但我想選擇更多列，例如：

    SELECT
      e.IDCARTEIRA, e.otherColumn, e.AnotherColumn [...]

此外，我還想消除空結果。

這將是預期結果（僅顯示具有非空 Ref 09 2021 和非空 Ref 10 2021 列的行）

IDCARTEIRA | OtherColumn |Ref 09 2021 | Ref 10 2021
       1   |     Value   |    39,76   |    N/A  
       2   |     Value   |     N/A    |   174,97
       4   |     Value   |    388,33  |    N/A           


 

有誰知道我該怎么做這兩件事？

我試過了：

    SELECT
       e.IDCARTEIRA,
       e.MATRICULA,
       e.LIG_AGUA_SIT,
      COALESCE((SELECT `VALORAGUA` FROM `INDICADORES.VW_CADASTRO_HIDROMETROPOLITANO` WHERE `IDCARTEIRA` = e.IDCARTEIRA AND `REFERENCIA_MES_ANO` = '202109' AND `CONSUMO_FATURADO_MES` - `CONS_MEDIO_AG_6_MESES` > 0),'') AS 'Referencia 09 2021',
      COALESCE((SELECT `VALORAGUA` FROM `INDICADORES.VW_CADASTRO_HIDROMETROPOLITANO` WHERE `IDCARTEIRA` = e.IDCARTEIRA AND `REFERENCIA_MES_ANO` = '202110' AND `CONSUMO_FATURADO_MES` - `CONS_MEDIO_AG_6_MESES` > 0),'') AS 'Referencia 10 2021'
      
      FROM `INDICADORES.VW_CADASTRO_HIDROMETROPOLITANO` e
      GROUP BY e.VALORAGUA ORDER BY e.IDCARTEIRA;

但第 4 行沒有顯示：

4 | 121112 | CORTADO  |  388,33 

@FanoFN 建議：

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=e6e691dc4c8b4edc17e2446049497123

對于鏈接中顯示的示例，我只需要顯示第 1、2 和 4 行。換句話說，我只需要顯示“Ref 09 2021”和“Ref 10 2021”列的非空行。

uj5u.com熱心網友回復：

如果您只是在尋找當前嘗試的快速解決方案，那么我建議您執行以下操作：

 SELECT
      e.IDCARTEIRA,
      COALESCE((SELECT `VALORAGUA` FROM VW_CADASTRO_HIDROMETROPOLITANO WHERE `IDCARTEIRA` = e.IDCARTEIRA AND `REFERENCIA_MES_ANO` = '202109'),'') AS 'Ref 09 2021',
      COALESCE((SELECT `VALORAGUA` FROM VW_CADASTRO_HIDROMETROPOLITANO WHERE `IDCARTEIRA` = e.IDCARTEIRA AND `REFERENCIA_MES_ANO` = '202110'),'') AS 'Ref 10 2021'
      FROM VW_CADASTRO_HIDROMETROPOLITANO e
      GROUP BY e.IDCARTEIRA
HAVING CASE WHEN `Ref 09 2021`='' AND `Ref 10 2021`='' THEN 0 ELSE 1 END=1;

在自定義生成的列上添加HAVINGwith陳述句。CASE這是一個略短的替代查詢：

SELECT IDCARTEIRA,
       MAX(CASE WHEN `REFERENCIA_MES_ANO` = '202109' THEN `VALORAGUA` ELSE '' END) AS 'Ref 09 2021',
       MAX(CASE WHEN `REFERENCIA_MES_ANO` = '202110' THEN `VALORAGUA` ELSE '' END) AS 'Ref 10 2021' 
FROM VW_CADASTRO_HIDROMETROPOLITANO
GROUP BY IDCARTEIRA
HAVING CASE WHEN `Ref 09 2021`='' AND `Ref 10 2021`='' THEN 0 ELSE 1 END=1;

演示小提琴

標籤：mysql

上一篇：為什么Kafka服務器限制為10萬條訊息？

下一篇：計算最后一行中每一列的平均值