oracle窗口函数

Question

有没有人能帮我解决这个问题：

SELECT SUM(summa), name, 
       TO_CHAR(invoice_date, 'YYYY/mm') 
           OVER (PARTITON EXTRACT(MONTH FROM i.invoice_date, c.name) 
FROM invoice i, customer c 
WHERE i.customer_id = c.id
AND months_between(sysdate, invoice_date) = 3
AND rownum < 11 GROUP BY invoice_date, name
ORDER BY SUM(SUMMA) DESC;

假设获取过去三个月的前十行，按月份分组并按总和排序。

谢谢。

Answer 1

首先，使用适当的显式join语法。其次，您需要row_number()

SELECT t.*
FROM (SELECT SUM(summa) as sumsumma, name, 
             TO_CHAR(invoice_date, 'YYYY/mm') as yyyymm,
             ROW_NUMBER() OVER (PARTITION BY TO_CHAR(invoice_date, 'YYYY/mm')
                                ORDER BY SUM(summa) DESC
                               ) as seqnum
      FROM invoice i JOIN
           customer c 
           ON i.customer_id = c.id
      WHERE months_between(sysdate, invoice_date) = 3
      GROUP BY invoice_date, name
     ) t
WHERE seqnum <= 10
ORDER BY sumsumma DESC;