基于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

现在我想删除所有在case列中没有值的行
我应该在查询中做哪些更改

最佳答案

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

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

09-10 07:50
查看更多