【SQL大考】请用一句SQL写出想要的结果。。。。。。

jinaqu

moseslin 发表于 2014-12-25 15:35
哎呀，不要谢了，我会害羞的。

Skye_to_Skye

moseslin 发表于 2014-12-25 14:54
WITH t AS (SELECT 2000 YEAR,100 in_come FROM dual
UNION ALL SELECT 2001, 400 FROM dual
UNION ALL S ...

我写的时候加了 partition by 就没有数据显示出来。原因是什么？难道取上个值的时候有一个为空不能分组？

li0924

来一个model

1. SELECT yearly,a,p FROM financial
   
2. MODEL
   
3. DIMENSION BY (yearly)
   
4. MEASURES (income AS p,income AS a)
   
5. RULES (p[ANY]=a[cv()]-nvl(a[cv()-1],0))

复制代码

lud999

SELECT a.yearly,a.income income,a.income-b.income profit
FROM financial a,financial b
WHERE a.yearly-1=b.yearly
ORDER BY   a.income-b.income DESC
(没看前面，已经有了，hoho）

qq19607781

http://user.qzone.qq.com/19607781/main
http://user.qzone.qq.com/2388215849
http://t.qq.com/navyli523518
http://weibo.com/qq19607781

http://www.bianews.com/news/91/n-441391.html
http://zx.zynews.com/ls/190092.html
http://zixun.zhongsou.com/faster/24/2feab46a68bc4fadce107bdb517845e4.html
http://www.qndb.net/gn/194225.html  
http://www.zgg35.com/news/show-17235.html
http://www.zgg35.com/news/show-17236.html
http://www.9982.cc/a/1278.html
http://www.9982.cc/a/1279.html
http://2555827.ohqly.com
http://2555827.ohqly.com/index.html
http://2555827.ohqly.com/74989l.html
http://2555827.ohqly.com/74990l.html
http://2555827.ohqly.com/74991l.html
http://2555827.ohqly.com/74992l.html
http://2555827.ohqly.com/74993l.html

silloye

select f2.yearly, f2.income, (f2.income - (select f1.income from financial f1 where f1.yearly=(f2.yearly-1))) as ic
from financial f2
where f2.yearly>2001
order by ic desc

jinaqu

li0924 发表于 2014-12-25 17:23
来一个model

用model的方法很不错，我一直想用，就是用不对，待会儿测试下看看，很谢谢你了

jinaqu

lud999 发表于 2014-12-25 20:00
SELECT a.yearly,a.income income,a.income-b.income profit
FROM financial a,financial b
WHERE a.year ...

都是高手啊，小伙伴们

王华荣

请问我用自连接可以吗，条件是a.year-1=b.year

jinaqu

王华荣 发表于 2014-12-28 19:43
请问我用自连接可以吗，条件是a.year-1=b.year

可以的。你看下例子，我的那个就是