动态获取数字列的最大和最小允许值？

发布于 2024-10-19 01:51:33 字数 395 浏览 4 评论 0原文

假设有一个名为 money 的列，该列为 NOT NULL 并且其类型为 NUMBER(x,y) 。对此列没有任何约束。

我想根据这个顺序对 money 列进行排序，+ve > -ve> 0 ，所以我的计划是将 0 值解码为 order by 子句中 Money 列可以容纳的最小允许值，例如 select * from tableXXX order by Dece(money, 0 , allowedMnimumValueForMoneyColumn , Money) desc< /代码> .我只是想知道是否可以动态获取货币列的最小允许值。

如何获得列的最大和最小允许值？ oracle有隐式变量来做这件事吗？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

又怨 2024-10-26 01:51:33

您似乎希望 money = 0 的记录出现在最后。

如果是这种情况，您将采用这样的顺序条款：

order by 
case when money = 0 then 0
                    else 1 
end desc,
money desc

通过一个工作示例，这将

create table tq84_order_by (
  txt   varchar2(10),
  money number not null
);

insert into tq84_order_by values ('aaa', 0);
insert into tq84_order_by values ('bbb', 2);
insert into tq84_order_by values ('ccc',-3);
insert into tq84_order_by values ('ddd', 4);
insert into tq84_order_by values ('eee', 1);

select * from tq84_order_by
order by 
case when money = 0 then 0
                    else 1 
                    end desc,
                    money desc;

导致

TXT             MONEY
---------- ----------
ddd                 4
bbb                 2
eee                 1
ccc                -3
aaa                 0

It seems that you want the records whose value for money = 0 appear last.

If this is the case you would go by such an order clause:

order by 
case when money = 0 then 0
                    else 1 
end desc,
money desc

With a working example, that would be

create table tq84_order_by (
  txt   varchar2(10),
  money number not null
);

insert into tq84_order_by values ('aaa', 0);
insert into tq84_order_by values ('bbb', 2);
insert into tq84_order_by values ('ccc',-3);
insert into tq84_order_by values ('ddd', 4);
insert into tq84_order_by values ('eee', 1);

select * from tq84_order_by
order by 
case when money = 0 then 0
                    else 1 
                    end desc,
                    money desc;

resulting in

TXT             MONEY
---------- ----------
ddd                 4
bbb                 2
eee                 1
ccc                -3
aaa                 0

回复收藏 0 原文

清旖 2024-10-26 01:51:33

您不必知道最小值。您可以将 NULL 视为最小值：

... ORDER BY decode(money, 0, NULL, money) NULLS LAST

You don't have to know the minimum value. You can treat NULL as the minimum value:

... ORDER BY decode(money, 0, NULL, money) NULLS LAST

回复收藏 0 原文

一杆小烟枪 2024-10-26 01:51:33

您需要在列上创建检查约束：

CREATE TABLE TEST (MONEY NUMBER(14,2) NOT NULL)
/

ALTER TABLE TEST ADD
  CONSTRAINT MONEY_VALID CHECK (( MONEY > 100 AND MONEY < 5000))
/

-- This fails
INSERT INTO TEST VALUES (20);

-- This works
INSERT INTO TEST VALUES (110);

You need to create a check constraint on the column:

CREATE TABLE TEST (MONEY NUMBER(14,2) NOT NULL)
/

ALTER TABLE TEST ADD
  CONSTRAINT MONEY_VALID CHECK (( MONEY > 100 AND MONEY < 5000))
/

-- This fails
INSERT INTO TEST VALUES (20);

-- This works
INSERT INTO TEST VALUES (110);

回复收藏 0 原文

~没有更多了~