postgresql - Filter 根据NULL值选择

Question

根据 postgresql 中的函数输出删除行:

在下面的查询中，我找到了小于零的结算值的总和

   select order_id , order_item_id , 
          case when sum(settlement_value) < 0  then
                     sum(settlement_value) 
            end
  from 
       "Recon".fk_courier_return 
 group by 
        order_item_id, order_id

输出是:

 Order id               Order_item_id  case

"OD101176788313080800";"115303430";
"OD40814018539";       "91216839";   -76.31
"OD40627030417";        "80207748";
"OD40913016810";       "98742811";
 "OD40701060277";       "80945680";
"OD100995986740582701";"108843075";  -42.07
"OD102293318278386300";"160498343";
 "OD40825127281";      "94066239";   -78.59
"OD200943992437302100";"106376239";
"OD40811280064";       "90512463";
"OD102056004796760300";"150562685";
 "OD40705090946";      "81791269";
"OD100996265730486401";"108855460";
"OD301050807164753201";"111119783";
"OD000825719620275400";" 100799584";
"OD001027651714425302"; "110064133";
"OD102181807536975500"; "156169384";
"OD000790021256135600";"99916743";
"OD101801498273347001";"139754487";
"OD40825034475";       "93908112";     -78.59

现在我想删除所有在案例列中没有值(value)的行

我应该在查询中做哪些更改

Answer 1

要回答您的问题(不显示基于函数输出的行)，您可以这样重写您的查询:

SELECT
  order_id,
  order_item_id,
  sum_settlement_value
FROM (
  SELECT order_id, order_item_id, 
         CASE WHEN sum(settlement_value) < 0  then
                   sum(settlement_value) 
         END AS sum_settlement_value
  FROM 
    "Recon".fk_courier_return 
   GROUP BY 
     order_item_id, order_id
) s
WHERE
  sum_settlement_value IS NOT NULL

(如果您的函数太复杂，这可能很有用)但是如果您只是不想显示没有负值的行，最好使用 HAVING 子句:

SELECT
  order_id,
  order_item_id,
  SUM(settlement_value)
FROM 
   "Recon".fk_courier_return 
GROUP BY 
  order_item_id,
  order_id
HAVING
  SUM(settlement_value) < 0