关于一个SQL语句的写法

Question

本帖最后由 tianshuaing 于 2010-09-29 10:39:10 编辑

Answer 1

sum(价格) over(partition by id order by 年月)

Answer 2

描述的不是很清楚,大概写了个,你看看跟你需要的有没有差别
SQL> select * from tbl_sum;
 
MONTH           ID        PRICE
------ ----------- ------------
201008        1060        24.50
201008        1059        47.00
201008        1059        51.50
201007      307848        67.00
201009      307848        68.00
201006      307848        73.50
201008        1059        75.00
201007      307848        78.00
201006      307848       107.00
201006      307848       123.50
201006      307848       136.00
201005      307848       139.00
201006      307848       140.00
201006      307848       140.00
201006      307848       150.00
201008        1059       160.00
 
16 rows selected
 
SQL> select month,id,price,sum(price)over(partition by month order by id asc),sum(price)over(partition by substr(month,1,4) order by id asc) from tbl_sum;
 
MONTH           ID        PRICE SUM(PRICE)OVER(PARTITIONBYMONT SUM(PRICE)OVER(PARTITIONBYSUBS
------ ----------- ------------ ------------------------------ ------------------------------
201005      307848       139.00                            139                           1580
201006      307848       123.50                            870                           1580
201006      307848       140.00                            870                           1580
201006      307848       136.00                            870                           1580
201006      307848        73.50                            870                           1580
201006      307848       107.00                            870                           1580
201006      307848       140.00                            870                           1580
201006      307848       150.00                            870                           1580
201007      307848        67.00                            145                           1580
201007      307848        78.00                            145                           1580
201008        1059        47.00                          333.5                          333.5
201008        1059        51.50                          333.5                          333.5
201008        1059       160.00                          333.5                          333.5
201008        1059        75.00                          333.5                          333.5
201008        1060        24.50                            358                            358
201009      307848        68.00                             68                           1580
 
16 rows selected
 
SQL> 

Answer 3

--试试，是不是你想要的：
WITH t AS(
SELECT '201008'month,'1060'id,24.5 price FROM dual UNION ALL 
SELECT '201008','1059',47 FROM dual UNION ALL 
SELECT '201008','1059',51.5 FROM dual UNION ALL 
SELECT '201007','307848',67 FROM dual UNION ALL 
SELECT '201009','307848',68 FROM dual UNION ALL
SELECT '201006','307848',73.5 FROM dual UNION ALL    
SELECT '201008','1059',75 FROM dual UNION ALL
SELECT '201007','307848',78 FROM dual UNION ALL
SELECT '201006','307848',107 FROM dual UNION ALL
SELECT '201006','307848',123.5 FROM dual UNION ALL
SELECT '201006','307848',136 FROM dual UNION ALL 
SELECT '201005','307848',139 FROM dual UNION ALL
SELECT '201006','307848',140 FROM dual UNION ALL
SELECT '201006','307848',140 FROM dual UNION ALL 
SELECT '201006','307848',150 FROM dual UNION ALL
SELECT '201008','1059',160 FROM dual
)
select month,id,price,
sum(price)over(partition by month order by id asc) month_sum,
sum(price)over(ORDER BY month) year_sum 
from (select month,id,price from t order by month)
;结果：
MONTH   ID     PRICE    MONTH_SUM   YEAR_SUM
------------------------------------------
201005 307848   139        139         139
201006 307848   73.5       870         1009
201006 307848   107        870         1009
201006 307848   123.5      870         1009
201006 307848   150        870         1009
201006 307848   140        870         1009
201006 307848   140        870         1009
201006 307848   136        870         1009
201007 307848   67         145         1154
201007 307848   78         145         1154
201008 1059     47         333.5       1512
201008 1059     75         333.5       1512
201008 1059     51.5       333.5       1512
201008 1059     160        333.5       1512
201008 1060     24.5       358         1512
201009 307848   68         68          1580

Answer 4

补充一下：
如果你数据量很大，最好不要加这句查询(select month,id,price from t order by month),多了次排序，效率低！直接用表T，即：
select month,id,price,
sum(price)over(partition by month order by id asc) month_sum,
sum(price)over(ORDER BY month) year_sum 
FROM t因为分析函数中就已经排序了。

Answer 5

莫非是要这样的?
SQL> 
SQL> select month,id,price,sum(price)over(partition by month order by id,price asc),
  2         sum(price)over(partition by substr(month,1,4) order by id,price asc)
  3   from tbl_sum
  4  order by id ,price;
 
MONTH           ID        PRICE SUM(PRICE)OVER(PARTITIONBYMONT SUM(PRICE)OVER(PARTITIONBYSUBS
------ ----------- ------------ ------------------------------ ------------------------------
201008        1059        47.00                             47                             47
201008        1059        51.50                           98.5                           98.5
201008        1059        75.00                          173.5                          173.5
201008        1059       160.00                          333.5                          333.5
201008        1060        24.50                            358                            358
201007      307848        67.00                             67                            425
201009      307848        68.00                             68                            493
201006      307848        73.50                           73.5                          566.5
201007      307848        78.00                            145                          644.5
201006      307848       107.00                          180.5                          751.5
201006      307848       123.50                            304                            875
201006      307848       136.00                            440                           1011
201005      307848       139.00                            139                           1150
201006      307848       140.00                            720                           1430
201006      307848       140.00                            720                           1430
201006      307848       150.00                            870                           1580
 
16 rows selected
 
SQL> 

Answer 6

SQL> select month,id,price,sum(price)over(partition by month order by id,price asc),
  2         sum(price)over(partition by substr(month,1,4) order by id,price asc)
  3   from tbl_sum
  4  order by month,id,price;
 
MONTH           ID        PRICE SUM(PRICE)OVER(PARTITIONBYMONT SUM(PRICE)OVER(PARTITIONBYSUBS
------ ----------- ------------ ------------------------------ ------------------------------
201005      307848       139.00                            139                           1150
201006      307848        73.50                           73.5                          566.5
201006      307848       107.00                          180.5                          751.5
201006      307848       123.50                            304                            875
201006      307848       136.00                            440                           1011
201006      307848       140.00                            720                           1430
201006      307848       140.00                            720                           1430
201006      307848       150.00                            870                           1580
201007      307848        67.00                             67                            425
201007      307848        78.00                            145                          644.5
201008        1059        47.00                             47                             47
201008        1059        51.50                           98.5                           98.5
201008        1059        75.00                          173.5                          173.5
201008        1059       160.00                          333.5                          333.5
201008        1060        24.50                            358                            358
201009      307848        68.00                             68                            493
 
16 rows selected
 
SQL>

Answer 7

SELECT 年月,
 ID,
 PRICE,
 SUM(PRICE) OVER(PARTITION BY 年月, ID ORDER BY 年月, 核算项目内码 ASC) MONTH_SUM,
 SUM(PRICE) OVER(PARTITION BY ID ORDER BY 年, 年月 ASC) YEAR_SUM
FROM   DUAL  

Answer 8

我只写了求year_sum 的语句，其他的应该你也会。
（相信方法是对的，但没安装oracle，没有试过）WITH t AS(
SELECT '201008'month,'1060'id,24.5 price FROM dual UNION ALL 
SELECT '201008','1059',47 FROM dual UNION ALL 
SELECT '201008','1059',51.5 FROM dual UNION ALL 
SELECT '201007','307848',67 FROM dual UNION ALL 
SELECT '201009','307848',68 FROM dual UNION ALL
SELECT '201006','307848',73.5 FROM dual UNION ALL            
SELECT '201008','1059',75 FROM dual UNION ALL
SELECT '201007','307848',78 FROM dual UNION ALL
SELECT '201006','307848',107 FROM dual UNION ALL
SELECT '201006','307848',123.5 FROM dual UNION ALL
SELECT '201006','307848',136 FROM dual UNION ALL 
SELECT '201005','307848',139 FROM dual UNION ALL
SELECT '201006','307848',140 FROM dual UNION ALL
SELECT '201006','307848',140 FROM dual UNION ALL 
SELECT '201006','307848',150 FROM dual UNION ALL
SELECT '201008','1059',160 FROM dual
)with tx as
( select month, id, sum(price)  price 
from t 
group by month, id
)
select t1.month, t1.id, 
sum( decode( sign( to_number(t1.month) - to_number(t2.month) ) , 
 1, 0, t2.price )  year_sum 
from tx  t1,  tx  t2
where t1.id = t2.id and substr(t1.month,1,4) = substr(t2.month,1,4)
;

Answer 9

 ) year_sum  前要加多的 ）