oracle 希望通过单个查询在不同列中显示正值和负值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6210773/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
want to display positive and negative values in different columns thru a single query
提问by Sara
I have one column table with negative and positive values and want to display positive and negative values in different columns thru a single query.
我有一个包含负值和正值的列表，并希望通过单个查询在不同列中显示正值和负值。
Column
-10000
-17000
16000
25000
output should be like
输出应该像
A B
-----------------
-10000 16000
-17000 25000
I am using oracle and pl sql developer.
我正在使用 oracle 和 pl sql 开发人员。
回答by Dinup Kandel
select decode(sign(col),1,col) positive_value
, decode(sign(col),-1,col) negative_value
, decode(sign(col),0,col) zero_value
from tbl_name
i dont know whether this will work or not but once try it..
我不知道这是否行得通，但一旦尝试..
回答by pratik garg
HI sara ,
嗨，萨拉，
just try this one
试试这个
SELECT T1.POSITIVE_VALUE, T2.negetive_value
FROM (select COL positive_value, ROWNUM C1 FROM TEST_1 WHERE COL >= 0) T1
FULL OUTER JOIN (SELECT COL negetive_value, ROWNUM C2 FROM TEST_1 WHERE COL < 0) T2
ON (T1.C1 = T2.C2);
回答by mihsathe
This might be a solution but only if number of positive and negative numbers is same.
这可能是一个解决方案，但前提是正数和负数的数量相同。
select a.column as negative, b.column as non_negative from table a, table b where a.column<0 and b.column>=0;
Where of course, columnis your column name and tableis your table name.
哪里当然column是你的列名，table是你的表名。
