改一下。如果你的输出是一个矩阵，列数肯定得事先知道的。要不然就用存储过程+动态SQL

newkid(newkid)

CREATE OR REPLACE VIEW vw_prices
AS
SELECT prod_id
,CASE WHEN prod_life>0 THEN price*(1+rate0) END AS price0
,CASE WHEN prod_life>1 THEN price*(1+rate0)*(1+rate1) END AS price1
,CASE WHEN prod_life>2 THEN price*(1+rate0)*(1+rate1)*(1+rate2) END AS price2
,CASE WHEN prod_life>3 THEN price*(1+rate0)*(1+rate1)*(1+rate2)*(1+rate3) END AS price3
,CASE WHEN prod_life>4 THEN price*(1+rate0)*(1+rate1)*(1+rate2)*(1+rate3)*(1+rate4) END AS price4
FROM products
,(SELECT MAX(CASE WHEN year=0 THEN rate END) AS rate0
,MAX(CASE WHEN year=1 THEN rate END) AS rate1
,MAX(CASE WHEN year=2 THEN rate END) AS rate2
,MAX(CASE WHEN year=3 THEN rate END) AS rate3
,MAX(CASE WHEN year=4 THEN rate END) AS rate4
FROM increase_rates
);

(#5024310@0)
Last Updated: 2009-2-4
This post has been archived. It cannot be replied.

想起我老板提起的一个关于EXCEL和Power Builder的故事. -coolmao(酷猫*Fremen); 2009-1-26 {338} (#5006804@0)

N年前, 他在美国一公司做一个类似项目. 一组打算用VB+Excel+Access, 另一组打算用Power Builder. 他在VB那组. 花了几天时间给客户用做了个Demo. 客户看完问他, 这和最后实际的application有什么区别?他说, 加些限制条件,error checking 等. 客户说那就花几天时间加上, 就用它了.

结果原本两月的项目, 两星期就完了. 客户回去后, fire了整个Power Builder team.

N应该是个不小的数了吧. 现在固然没有人用Power Builder, ACCESS用量也极其有限. 都是互连网闯的祸. EXCEL, WORD, 甚至NOTEPAD, 这些基本工具是不会被淘汰的. -javacup(风铃); 2009-1-26 (#5007288@0)

ACCESS这边用得多了，很多上市公司的业务都在上面跑。简单，不用看IT的眼色，很多业务主管亲自操刀写ACCESS报表。 -cerboros_redux(Cerboros Reborn); 2009-1-26 (#5007353@0)

IT不喜欢业务主管是业务把他们所有的问题都当成了IT的问题，demanding...然后IT费九牛二虎之力去解决业务的问题的时候，业务一个人去上面的老板那里领功，说是自己的功劳。赫赫，不是IT眼色不好。。 -sunday8(sunday8); 2009-2-4 (#5024028@0)

EXCEL不是数据库, 是电子报表软件. 报表开发从来都是一项很重要的技术, 但是跟数据库基本没重叠. 其实很有些公司用EXCEL替代CR做数据库报表． -javacup(风铃); 2009-1-26 (#5007269@0)

Excel can be used as a database. why not? -anthonylo2008(anthonylo); 2009-2-4 (#5024871@0)
谁说数据库就特指关系数据库? -akoei(停车**枫林晚); 2009-2-5 (#5027837@0)

做oracle的看不起用Access的，做Access的看不起用Excel的，做Excel的看不起手写账本的，NND，我一个月总共挣不了几个破钱，打瓶酱油记个胀还要用oracle？ -yangapple(yangapple); 2009-1-26 (#5007297@0)

我以前一印度同事，用oracle的，但只有Access的水平，而且典型的看不起用Excel的，他经常跟我说oracle好些东西做不了，所以一定要导出文本文件然后倒入Access在手工处理。他说的可不是orcal没有form界面，而是嫌oracle的Sql在服务器上运行太慢所以要放到pc上运行。 -yangapple(yangapple); 2009-1-26 (#5007302@0)

只能说明他不懂ORACLE -newkid(newkid); 2009-2-4 (#5024246@0)
hoho, thats kinda brave person -win(秋天的菠菜); 2009-2-4 (#5024250@0)
你的印度同事很牛. -clear(clear); 2009-2-4 (#5024263@0)

可惜楼上的回答都没有到点上呢。补充的是，原帖没有看不起某某用某某的意思, 也不提倡。 -sunday8(sunday8); 2009-2-4 {193} (#5023973@0)

主要的问题是： calculation on cross column and sequencial row or column at the same time
EXEL非常简单的解决这种问题，关系数据库好像是牛刀用不上场, 或者偶还找不到更好的解决方法.
thanks again..

Buddy, I guess you forget the use of VIEW in relational database ! -nicetomeetyou(_); 2009-2-4 (#5024007@0)

Even View may not be able to solve it.. one of the example would be... -sunday8(sunday8); 2009-2-4 {239} (#5024146@0)
prod, price,prod life
A ,P,5

Year, %increase
0,0%
1,1%
2,2%
3,3%
4,0%

to contruct a view like
Prod, Year 0 ,1 , 2 , 3 , 4
A price P0,P1,P2,P3,P4

e.g.
P0 = P*(1+0%)
...
P4 = P* (1+0%)(1+1%)(1+2%) (1+3%)(1+0%)

谁说的？不就一个简单的行转列吗？ -newkid(newkid); 2009-2-4 {893} (#5024242@0)
ORACLE:

CREATE TABLE products (prod_id VARCHAR2(20),price NUMBER, prod_life NUMBER);

CREATE TABLE increase_rates (year NUMBER, rate NUMBER);

CREATE OR REPLACE VIEW vw_prices
AS
SELECT prod_id
,CASE WHEN prod_life>0 THEN price*(1+rate0) END AS price0
,CASE WHEN prod_life>1 THEN price*(1+rate1) END AS price1
,CASE WHEN prod_life>2 THEN price*(1+rate2) END AS price2
,CASE WHEN prod_life>3 THEN price*(1+rate3) END AS price3
,CASE WHEN prod_life>4 THEN price*(1+rate4) END AS price4
FROM products
,(SELECT MAX(CASE WHEN year=0 THEN rate END) AS rate0
,MAX(CASE WHEN year=1 THEN rate END) AS rate1
,MAX(CASE WHEN year=2 THEN rate END) AS rate2
,MAX(CASE WHEN year=3 THEN rate END) AS rate3
,MAX(CASE WHEN year=4 THEN rate END) AS rate4
FROM increase_rates
);

hmmm.. not really.. i don't see the cumulative rate from ur code, and prod life is a variable..(but we can simplify the problem to be a max year for now) -sunday8(sunday8); 2009-2-4 {89} (#5024288@0)
p0 = p* ((1+rate0)
p1 = p* (1+rate0)(1+rate1)
p2 = p* (1+rate0)(1+rate1)(1+rate2)
....

改一下。如果你的输出是一个矩阵，列数肯定得事先知道的。要不然就用存储过程+动态SQL -newkid(newkid); 2009-2-4 {843} (#5024310@0)
CREATE OR REPLACE VIEW vw_prices
AS
SELECT prod_id
,CASE WHEN prod_life>0 THEN price*(1+rate0) END AS price0
,CASE WHEN prod_life>1 THEN price*(1+rate0)*(1+rate1) END AS price1
,CASE WHEN prod_life>2 THEN price*(1+rate0)*(1+rate1)*(1+rate2) END AS price2
,CASE WHEN prod_life>3 THEN price*(1+rate0)*(1+rate1)*(1+rate2)*(1+rate3) END AS price3
,CASE WHEN prod_life>4 THEN price*(1+rate0)*(1+rate1)*(1+rate2)*(1+rate3)*(1+rate4) END AS price4
FROM products
,(SELECT MAX(CASE WHEN year=0 THEN rate END) AS rate0
,MAX(CASE WHEN year=1 THEN rate END) AS rate1
,MAX(CASE WHEN year=2 THEN rate END) AS rate2
,MAX(CASE WHEN year=3 THEN rate END) AS rate3
,MAX(CASE WHEN year=4 THEN rate END) AS rate4
FROM increase_rates
);

感谢..你让我在大寒的冬天，看到佛罗里达的阳光。。:) -sunday8(sunday8); 2009-2-4 (#5024442@0)

你不是在挖苦吧:) 要不咱们再继续PK? -newkid(newkid); 2009-2-4 (#5024470@0)

Easy case: Use user defined function to do the calculation for customized "column". Actually in Excel, the formula you input in a column is something like a user defined function with certain cell's value as input param. -nicetomeetyou(_); 2009-2-4 (#5024317@0)

恩。user function可行，但觉得会累死server，特别是cummulative的那种计算。。 -sunday8(sunday8); 2009-2-4 (#5024453@0)

很多情况下SQL就可以搞定了，自定义函数有些额外开销 -newkid(newkid); 2009-2-4 (#5024487@0)

SQL has extended syntax like SELECT.. CASE WHEN...and in most cases, enough. But for some complex situation requesting more computing power, use store proc or func, then load 3rd party external module written in C is desired. -nicetomeetyou(_); 2009-2-4 (#5024537@0)

要不您也来出一题？反正闲着也是闲着。 -newkid(newkid); 2009-2-4 (#5024578@0)

我现在满脑子都是enterprise app architectural design, how and where to put process, in client? in middleware? in database? who takes responsibility to do what... and how, what is appealing color scheme on client, etc. -nicetomeetyou(_); 2009-2-4 {61} (#5024624@0)
等有了具体问题,再说, 不过一般都是how to maximize performance.

if you want to achieve the best performance then utilize the power of SQL! anything other than UI design should be implemented in DB, trust me -newkid(newkid); 2009-2-4 (#5024638@0)

Not really sir, put too much logic in database, you compromise your application's compatibility, scalability, and mobility. -nicetomeetyou(_); 2009-2-4 (#5024649@0)

How likely is it that your company runs an application on different DB platforms? It's very difficult, if not impossible, to build an application compatible with all DBs. -newkid(newkid); 2009-2-4 {214} (#5024691@0)
Every DB has its own features and power. These are part of your investment.
DB itself is scalable (by adding cluster nodes)
And I don't know what you mean by mobility ---- are you talking about working offline?

You are right from end users' standpoint, I make some sense from a software solution vendor's point of view.:-) -nicetomeetyou(_); 2009-2-4 (#5024772@0)

I'm actually a software solution vendor----I understand it as a fancy name for "developer" :) -newkid(newkid); 2009-2-4 {195} (#5024828@0)
this topic is like a religion war so I'd better shut up to save us some mouth water.
but if you find any example achieving better performance than DB solution, please feel free to challenge me.

Are you a Taliban fighter? I didn't say you are wrong. Having more multi-culture senses in mind then you can become a leader. As a senior in the field, I don't care which is the best, I care if the job can be done, and if people are happy with it. -nicetomeetyou(_); 2009-2-4 (#5024842@0)

I don't want to be a leader ---- not everybody owns that niubility :) -newkid(newkid); 2009-2-4 {430} (#5024901@0)
I said it's like a religion war because I've seen too many arguments between DB and non-DB guys.
You don't care which is the best ---- nothing is the best, we are just talking about performance, and you brought up the topic about maximizing the performance.
You care if the job can be done ---- yes if you hire the right person
and if people are happy with it ---- usually people are happy with a system of good performance.

山外有山，天外有天。任何工具都有其独到的针对某种需求的特殊优势。 -ukyo(阿飞); 2009-2-4 (#5024543@0)

don't 鄙视 anything. there is always bad solution, not bad tools. -anthonylo2008(anthonylo); 2009-2-4 (#5024877@0)

@Florida

改一下。如果你的输出是一个矩阵，列数肯定得事先知道的。要不然就用存储过程+动态SQL

Replies, comments and Discussions:

More Topics