This topic has been archived. It cannot be replied.
-
工作学习 / IT技术讨论 / Can anyone write a SQL select sentence for follow question?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 ...
quite difficult,isn't it ?,I spent 10 minutes to find out,and tested it on MYSQL.
-easyway(漂流);
2004-3-23
{480}
(#1655698@0)
-
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 thisselect 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
-handd(大熊猫®);
2004-3-23
{353}
(#1655831@0)
-
REVISEDselect 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
-handd(大熊猫®);
2004-3-23
{476}
(#1655841@0)
-
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 testedselect 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
-handd(大熊猫®);
2004-3-23
{468}
(#1655927@0)
-
Good,for Oracle I would do it like thisselect 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.
-easyway(漂流);
2004-3-23
{224}
(#1656400@0)
-
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)
-
Refinedselect 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
-handd(大熊猫®);
2004-3-23
{297}
(#1656476@0)
-
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):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))
-camryv6(CamryV6);
2004-3-23
{190}
(#1656504@0)
-
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? :-)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 ))
-camryv6(CamryV6);
2004-3-24
{360}
(#1656751@0)
-
not all system support "select top 1"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.
-dibert(呆伯);
2004-3-24
{462}
(#1656791@0)
-
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)
-
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.This kind of 'uniform' coding might be not the best pratice
-handd(大熊猫®);
2004-3-24
{61}
(#1656910@0)
-
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.in terms of Standard SQL,there were a lot disputes on it in other IT forum.I don't want to continue here.
-easyway(漂流);
2004-3-24
{106}
(#1657022@0)
-
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:"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.
-camryv6(CamryV6);
2004-3-24
{383}
(#1657286@0)
-
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,I believe none of us have any interests in your personality.bytheway take care of your mouth,it's your trouble-maker.
-easyway(中);
2004-3-25
{119}
(#1658316@0)
-
you know what,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.
-camryv6(CamryV6);
2004-3-24
{498}
(#1656915@0)
-
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)
-
话都说不清楚，还在这卖弄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？！
-uniquemember(TjHong);
2004-3-26
{495}
(#1660831@0)