why I insist using pure concise SQL SELECT,not vendor lock-in feature,you even don't understand what shouldn't use and why not,here is why and I give my solution.

easyway(漂流)

本文发表在 rolia.net 枫下论坛In current technology fashion,multi-tier architecture applys in most developping a distributing system.so middleware play more and more vital role in whole system.and business logic keep changing with fast pace. J2EE serivce that pattern seemlessly,one entity-bean may serve even tens thousands request.by nature one entity-bean correspends one select query.I prefer use less entity-bean to solve problem than more entity-bean even a query take a little bit longer than other solution,consider network,container overhead ,I think overall performance should favorites less SELECT.if your solution lock in to vendor feature,think about this, not every one know every database feature,if one guy who design the business logic component leave,what you're going to do?except maybe you will be asked to port your project to other platform.

so here is my solution,I hate someone just gave code without explaination:

select a.vpn,a.date,a.price-b.price as inc from product a,product b,product c where a.vpn=b.vpn and a.vpn=c.vpn and a.update_date>c.update_date group by a.vpn,a,update_date,b.update_date having b.update_date=max(c.update_date).

here is explanation ,consider three logic table a,b,c .

a: serve as basic table ,group by a.vpn,a.update_date means for any combination of (vpn,update_date) there will be on result in query Result Set .
b: serve as a table in which a previous update_date will be found.

c:serve as a table in which all previous update_date will be found.更多精彩文章及讨论，请光临枫下论坛 rolia.net

(#1656818@0)
Last Updated: 2004-3-24
This post has been archived. It cannot be replied.

select DISTINCT vpn,date,price,increase from ... -uniquemember(TjHong); 2004-3-23 (#1655708@0)

look into product table ,there is no increase column,that means you calculate it for every record. -easyway(漂流); 2004-3-23 (#1655713@0)

up -easyway(漂流); 2004-3-23 (#1655823@0)

How about this -handd(大熊猫®); 2004-3-23 {353} (#1655831@0)

select b.vpn
, b.update_date
, b.price-a.price incr
from
(
select rownun s_n
, vpn
, update_date
, price
from product
order by update_date) a
,
(
select rownun s_n
, vpn
, update_date
, price
from product
order by update_date) b
where a.vpn=b.vpn
and a.s_n<b.s_n
and rownum = 1

REVISED -handd(大熊猫®); 2004-3-23 {476} (#1655841@0)
select PP.vpn,PP.update,AA.inc, from
(
select b.vpn
, b.update_date
, b.price-a.price inc
from
(
select rownun s_n
, vpn
, update_date
, price
from product
order by update_date) a
,
(
select rownun s_n
, vpn
, update_date
, price
from product
order by update_date) b
where a.vpn=b.vpn
and a.s_n<b.s_n
and rownum = 1
) AA
, product PP
where AA.vpn = PP.vpn
and AA.update_date = PP.update_date

I couldn't test your Select,what database is your select for? -easyway(漂流); 2004-3-23 (#1655874@0)

Oracle -handd(大熊猫®); 2004-3-23 (#1655887@0)
'rownum' is a pseudocolumn in Oracle -handd(大熊猫®); 2004-3-23 (#1655892@0)

Revised (version2) and tested -handd(大熊猫®); 2004-3-23 {468} (#1655927@0)
select b.vpn
, b.update_date
, b.price-a.price inc
from
(
select rownum s_n
, vpn
, update_date
, price
from product
order by update_date) a
,
(
select rownum s_n
, vpn
, update_date
, price
from product
order by update_date) b
where a.vpn=b.vpn
and a.s_n < b.s_n
and a.s_n = b.s_n-1

Good,for Oracle I would do it like this -easyway(漂流); 2004-3-23 {224} (#1656400@0)
select b.vpn, b.update_date, (b.price-a.price) as inc from product a,product b where a.vpn=b.vpn and a.rownum = b.rownum -1 order by a.vpn ,a.update_date,b.update_date;

problem is not every database provide rownum column.

but, basicallly, for every database system, embeded date function and join are supported. You need more practise on SQL. this problem is not difficult. -camryv6(CamryV6); 2004-3-23 (#1656519@0)

Refined -handd(大熊猫®); 2004-3-23 {297} (#1656476@0)
select b.vpn
, b.update_date
, b.price-a.price inc
from
(
select rownum s_n
, vpn
, update_date
, price
from product
order by vpn,update_date) a
,
(
select rownum s_n
, vpn
, update_date
, price
from product
order by vpn,update_date) b
where a.vpn=b.vpn
and a.s_n = b.s_n-1

yes,you did it correct,so the rownum contributes to your solution. -easyway(漂流); 2004-3-23 (#1656687@0)

see inside (I didn't test, it should be working): -camryv6(CamryV6); 2004-3-23 {190} (#1656504@0)

select a.vpn, a.update_date, a.price, (a.price - isnull(b.price, 0)) as increasement
from product a left join product b on (a.vpn = b.vpn
and a.update_date = dateadd(d, 1, b.update_date))

first of all you take wrong assumption:the update_date is not nessesorily be every day.you use wrong a.update_date = dateadd(d, 1, b.update_date) condition. -easyway(漂流); 2004-3-23 (#1656664@0)

well, it's a typical self-join problem. shouldn't be difficult. I just gave you an idea to work it out. it looks you are just challenging us, aren't you? :-) -camryv6(CamryV6); 2004-3-24 {360} (#1656751@0)
in case you still need help, again, i didn't test:

select a.vpn, a.update_date, a.price, (a.price - isnull(b.price, 0)) as increasement
from product a left join product b on (a.vpn = b.vpn
and a.update_date > b.update_date
and b.update_date =
(select top 1 c.update from product c
where c.update_date < a.update_date order by c.update_date desc ))

not all system support "select top 1" -dibert(呆伯); 2004-3-24 {462} (#1656791@0)
select a.vpn, a.update_date, a.price, (a.price - isnull(b.price, 0)) as increasement
from product a left join product b on (a.vpn = b.vpn
and a.update_date > b.update_date
and b.update_date =
(select top 1 c.update from product c
where c.update_date < a.update_date order by c.update_date desc ))

How about changing "(select top 1..." to:
(select max(c.update) from product c
where c.update_date < a.updte_date
and c.vpn = a.vpn)

Not tested.

yup, I agree with you that we should use standard SQL instead of "special" keyword. and I think I made a mistake that I should put a.update_date = c.update_date in the subquery. -camryv6(CamryV6); 2004-3-24 (#1656904@0)

mistake again, should be a.vpn = c.vpn .........just didn't pay too much attention to it............... hehe ........ -camryv6(CamryV6); 2004-3-24 (#1656963@0)

why I insist using pure concise SQL SELECT,not vendor lock-in feature,you even don't understand what shouldn't use and why not,here is why and I give my solution. -easyway(漂流); 2004-3-24 {1497} (#1656818@0)
本文发表在 rolia.net 枫下论坛In current technology fashion,multi-tier architecture applys in most developping a distributing system.so middleware play more and more vital role in whole system.and business logic keep changing with fast pace. J2EE serivce that pattern seemlessly,one entity-bean may serve even tens thousands request.by nature one entity-bean correspends one select query.I prefer use less entity-bean to solve problem than more entity-bean even a query take a little bit longer than other solution,consider network,container overhead ,I think overall performance should favorites less SELECT.if your solution lock in to vendor feature,think about this, not every one know every database feature,if one guy who design the business logic component leave,what you're going to do?except maybe you will be asked to port your project to other platform.

so here is my solution,I hate someone just gave code without explaination:

select a.vpn,a.date,a.price-b.price as inc from product a,product b,product c where a.vpn=b.vpn and a.vpn=c.vpn and a.update_date>c.update_date group by a.vpn,a,update_date,b.update_date having b.update_date=max(c.update_date).

here is explanation ,consider three logic table a,b,c .

a: serve as basic table ,group by a.vpn,a.update_date means for any combination of (vpn,update_date) there will be on result in query Result Set .
b: serve as a table in which a previous update_date will be found.

c:serve as a table in which all previous update_date will be found.更多精彩文章及讨论，请光临枫下论坛 rolia.net

I only partial agree...1, The standard slows down the performance. 2, Currently, most of s/f are built upon different DB with different version. -handd(大熊猫®); 2004-3-24 {61} (#1656910@0)
This kind of 'uniform' coding might be not the best pratice

I'd like discuss issues base on technique point of view,not personal attack. that help to form a good conclusion,the results should benifits all. -easyway(漂流); 2004-3-24 {106} (#1657022@0)
in terms of Standard SQL,there were a lot disputes on it in other IT forum.I don't want to continue here.

This is my quote of today: " MIND IS LIKE A PARACHUTE, IT HAS TO BE OPEN" -handd(大熊猫®); 2004-3-24 (#1657046@0)

I was OK even when I realized this stupid guy was just challenging us by this simple question. But the word from the stupid guy, which made me angry was: -camryv6(CamryV6); 2004-3-24 {383} (#1657286@0)
"so here is my solution,I hate someone just gave code without explaination:" -- bull shit!!! who is he?

who gave this stupid guy the right to "hate" people like us who are helping or discussing question with him? We were so unlucky to meet guy like that.

also, his "solution", which is just a sql statement, he called it solution, wasn't that super apparently.

have you seen I ever said I couldn't work it out asking for help?it's open discussion,you put it with your taste,then you always made your wrong assumption. -easyway(漂流); 2004-3-24 (#1657323@0)

Showing off this kind of knowledge is much more "STUPID" than asking for help or simply discussing. You are just IDIOT. -camryv6(CamryV6); 2004-3-24 (#1658176@0)

I'v met many chinese in other IT forum ,none of them behave like you,you exposed yourself so quickly and thoroughly -easyway(中); 2004-3-25 {119} (#1658316@0)
,I believe none of us have any interests in your personality.bytheway take care of your mouth,it's your trouble-maker.

you know what, -camryv6(CamryV6); 2004-3-24 {498} (#1656915@0)
you are kind of stupid.

what are you doing here? show off yourself? teaching us? you think this is a world class difficulty worthy to announce here? As I told you it's just a typical self-join SQL problem. I even didn't spend more than 3 minutes to write you something. It may be incorrect, but the idea inside is correct. I thought you were seeking help to work it out.

anyway, forget about the fucking idiot SQL. again, you are stupid of showing off here. I was stupid, too, to help you.

Don't be so angry. Every one has his onw personality. The discussion would not hurt anything. However, the finger point could keep the valuable ideas from you -handd(大熊猫®); 2004-3-24 (#1656930@0)

ok, the factor is: we are discussing a simple question, which is not worthy to spend too much time. I am quite sure 9 of 10 experienced SQL programmer know how to do it. -camryv6(CamryV6); 2004-3-24 (#1656968@0)

话都说不清楚，还在这卖弄 -uniquemember(TjHong); 2004-3-26 {495} (#1660831@0)

There are only one table product(vpn char(8),update_date char(8),price double) which record all products identified by vpn ,the price is updated on update_date .here is the question:
Using only one select to find out price differents which is (the price on update_date - the price on previous date) for every records in product table. like this :
select vpn,date,price,increase from ...

How can you find the originally price in a table identified by vpn when the price has been updated？！

@Calgary

why I insist using pure concise SQL SELECT,not vendor lock-in feature,you even don't understand what shouldn't use and why not,here is why and I give my solution.

Replies, comments and Discussions:

More Topics