選我最佳解答
SELECT A,B,
CASE WHEN B=1 THEN A
ELSE A-B
END AS C
FROM tblMain
CREATE TABLE tblMain
(`A` int, `B` int)
;
INSERT INTO tblMain
(`A`, `B`)
VALUES
(12, 1),
(14, 11)
;
不要選我最佳解答喔.不要害我喔...
create table itx191211 (
id smallint generated always as identity
, a smallint not null
, b smallint not null
);
insert into itx191211 (a, b) values
(12, 1),
(14, 11);
select a
, b
, case
when b = 1 and a > 0 then a
else a - b
end as c
from itx191211;
+----+----+----+
| a | b | c |
+----+----+----+
| 12 | 1 | 12 |
| 14 | 11 | 3 |
+----+----+----+
(2 rows)
-- 不用case 的方法
select a
, b
, a as c
from itx191211
where b = 1 and a > 0
union all
select a
, b
, a - b as c
from itx191211
where not (b = 1 and a > 0);
+----+----+----+
| a | b | c |
+----+----+----+
| 12 | 1 | 12 |
| 14 | 11 | 3 |
+----+----+----+
(2 rows)