SQL排除负数

Question

请帮忙，有一个请求，其中有时 KOLVO_RZ 列中存在负值。是否可以在请求中检查这一点，如果该值为负数，则返回 0？

SELECT KOLVO_T - KOLVO_KKM - KOLVO_RZ AS KOLVO_T
FROM PRICE

Answer 1

这应该可以解决问题：

SELECT KOLVO_T - KOLVO_KKM - (CASE WHEN KOLVO_RZ < 0 THEN 0 ELSE KOLVO_RZ END) AS KOLVO_T
FROM PRICE

Answer 2

这是适用于我的 InterBase 2020 的示例。您使用的是哪个 InterBase 版本？

create table hikers (
       hiker_id integer not null, 
       name varchar (256) not null,
       constraint pk_hid primary key (hiker_id));
create table hiker_data (
       hiker_id integer not null, 
       steps_gained integer,
       constraint fk_hid foreign key (hiker_id) references hikers (hiker_id));
commit;
insert into hikers values (1, 'Aarti');
insert into hikers values (2, 'Bala');
insert into hikers values (3, 'Chaaya');
insert into hikers values (4, 'Deepak');
insert into hiker_data values (1, 20);
insert into hiker_data values (1, 10);
insert into hiker_data values (1, -5);
insert into hiker_data values (1, 15);
insert into hiker_data values (1, -20);
insert into hiker_data values (2, 12);
insert into hiker_data values (2, 6);
insert into hiker_data values (2, 20);
insert into hiker_data values (2, -5);
insert into hiker_data values (2, 10);
insert into hiker_data values (2, -18);
insert into hiker_data values (3, 20);
insert into hiker_data values (3, 10);
insert into hiker_data values (3, -5);
insert into hiker_data values (3, 15);
insert into hiker_data values (3, -20);
insert into hiker_data values (3, 12);
insert into hiker_data values (4, 6);
insert into hiker_data values (4, 20);
insert into hiker_data values (4, -5);
insert into hiker_data values (4, 10);
insert into hiker_data values (4, -18);
commit;
/* test foreign key enforcement */
insert into hiker_data values (-100, -18);

然后，我成功执行以下查询。

select hiker_id, 
       SUM(0 + (CASE WHEN steps_gained < 0 THEN 0 
                     ELSE steps_gained 
                     END)) AS total_steps_climbed,
       SUM(0 + (CASE WHEN steps_gained > 0 THEN 0 
                     ELSE steps_gained 
                     END)) AS total_steps_descended,
       SUM(steps_gained) AS final_tally
  from hiker_data
group by hiker_id;

select hiker_id, 
       (CASE WHEN steps_gained < 0 THEN 0 
                     ELSE steps_gained 
                     END) AS steps_climbed
  from hiker_data
 where hiker_id=2;