能增加计算公式吗

fine_aloys

请教各位：能自己增加一个计算公式吗，比已经有的计算总数，平均值要复杂的公式？

pshen

加在哪里？
报表？

pshen

现在的比较困难，可以考虑从View里，直接把数据计算好，但是，格式或者显示就受点限制。

或者可能有别的好办法，等待楼下解答

fine_aloys

用视图的话那只能用一个公式了，有好几种计算方法怎么办呢？

aoslee

用视图的话那只能用一个公式了，有好几种计算方法怎么办呢？

用视图可以有多种计算公式.
case ... when结构

fine_aloys

感谢赐教，能举个简单的例子吗，还没有在视图查询中用过case语句

aoslee

In a simple CASE expression, Oracle searches for the first WHEN ... THEN pair for which expr is equal to comparison_expr and returns return_expr. If none of the WHEN ... THEN pairs meet this condition, and an ELSE clause exists, then Oracle returns else_expr. Otherwise, Oracle returns null. You cannot specify the literal NULL for every return_expr and the else_expr.

In a searched CASE expression, Oracle searches from left to right until it finds an occurrence of condition that is true, and then returns return_expr. If no condition is found to be true, and an ELSE clause exists, Oracle returns else_expr. Otherwise, Oracle returns null.

For a simple CASE expression, the expr and all comparison_exprs must either have the same datatype (CHAR, VARCHAR2, NCHAR, or NVARCHAR2, NUMBER, BINARY_FLOAT, or BINARY_DOUBLE) or must all have a numeric datatype. If all expressions have a numeric datatype, then Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype.

For both simple and searched CASE expressions, all of the return_exprs must either have the same datatype (CHAR, VARCHAR2, NCHAR, or NVARCHAR2, NUMBER, BINARY_FLOAT, or BINARY_DOUBLE) or must all have a numeric datatype. If all return expressions have a numeric datatype, then Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype.

The maximum number of arguments in a CASE expression is 255. All expressions count toward this limit, including the initial expression of a simple CASE expression and the optional ELSE expression. Each WHEN ... THEN pair counts as two arguments. To avoid exceeding this limit, you can nest CASE expressions so that the return_expr itself is a CASE expression.


Simple CASE Example

For each customer in the sample oe.customers table, the following statement lists the credit limit as "Low" if it equals $100, "High" if it equals $5000, and "Medium" if it equals anything else.

SELECT cust_last_name,
   CASE credit_limit WHEN 100 THEN 'Low'
   WHEN 5000 THEN 'High'
   ELSE 'Medium' END
   FROM customers;

CUST_LAST_NAME       CASECR
-------------------- ------
...
Bogart               Medium
Nolte                Medium
Loren                Medium
Gueney               Medium

Searched CASE Example

The following statement finds the average salary of the employees in the sample table oe.employees, using $2000 as the lowest salary possible:

SELECT AVG(CASE WHEN e.salary > 2000 THEN e.salary
   ELSE 2000 END) "Average Salary" from employees e;

Average Salary
--------------
    6461.68224

fine_aloys

非常感谢，这么详细的介绍，好好学习下先

sofar1218

导出数据，在excel里设置公式，或者把数据导出，再导入到adaptiveplanning，再设公式