时间戳

首页 > 解决方案 > 选择交付的最高订单

sql - 选择交付的最高订单

问题描述

以下是我的输出：

MONTH STAF STAFFNAME            TOTAL_ORDER_DELIVERED
===== ==== ==================== =====================
JAN   S009 Theresina Ertelt                         1
FEB   S015 Lonna Charker                            1
MAR   S003 Suzi Maccari                             2
MAR   S010 Zacharie Witty                           1
MAR   S020 Abbie Gosnoll                            1
MAR   S017 Renee Alston                             1
AUG   S006 Falito Ollerton                          1
AUG   S017 Renee Alston                             1
AUG   S003 Suzi Maccari                             1
OCT   S003 Suzi Maccari                             3
OCT   S020 Abbie Gosnoll                            2

我想要的是：

MONTH STAF STAFFNAME            TOTAL_ORDER_DELIVERED
===== ==== ==================== =====================
JAN   S009 Theresina Ertelt                         1
FEB   S015 Lonna Charker                            1
MAR   S003 Suzi Maccari                             2
AUG   S006 Falito Ollerton                          1
AUG   S017 Renee Alston                             1
AUG   S003 Suzi Maccari                             1
OCT   S003 Suzi Maccari                             3

我想根据月份选择最高的结果，但不知道该怎么做。这是我在 SQL 中的查询：

SELECT TO_CHAR(TO_DATE(EXTRACT(MONTH FROM receivedDate),'mm'),'MON') AS Month,
       d.staffID, staffname, count(deliveryID) AS Total_Order_Delivered
FROM delivery d, deliverystaff s
WHERE (d.staffid = s.staffid)
AND (EXTRACT(YEAR FROM receivedDate) = 2020)
GROUP BY EXTRACT(MONTH FROM d.receivedDate),d.staffid, staffname 
ORDER BY EXTRACT(MONTH FROM d.receivedDate),count(deliveryID) desc;

标签： sqloracle

解决方案

我建议RANK在这里使用：

WITH cte AS (
    SELECT TO_CHAR(TO_DATE(EXTRACT(MONTH FROM receivedDate), 'mm'), 'MON') AS Month,
           EXTRACT(MONTH FROM d.receivedDate) AS month_num,
           d.staffID, staffname, COUNT(deliveryID) AS Total_Order_Delivered,
           RANK() OVER (PARTITION BY EXTRACT(MONTH FROM d.receivedDate), d.staffid, staffname
                        ORDER BY COUNT(deliveryID) DESC) rnk
    FROM delivery d
    INNER JOIN deliverystaff s ON d.staffid = s.staffid
    WHERE EXTRACT(YEAR FROM receivedDate) = 2020
    GROUP BY EXTRACT(MONTH FROM d.receivedDate), d.staffid, staffname
)

SELECT Month, staffID, staffname, Total_Order_Delivered
FROM cte
WHERE rnk = 1
ORDER BY month_num;

推荐阅读