sql总结

发布于：2024-08-20 ⋅ 阅读:(31) ⋅ 点赞:(0)

微信交流群：https://732903.hlcode.cn/?id=NK1fWUR

1.sql

1.多数据筛选排名

row_number 从1到10顺序排名， rank(）11335并列的算一名 DENSE_RANK是1122333排，rank是11335

SELECT 
    '销售经理' AS f_a,
    bureau_name AS f_b,
    deal_staff_name AS f_c,
    COUNT(CASE 
            WHEN main_price_name = '全家福229元套餐' THEN 2
            WHEN main_price_name IN ('全家福199元套餐', '全家福159元套餐') THEN 1
            ELSE 0
         END) AS f_d,
    ROW_NUMBER() OVER (ORDER BY COUNT(CASE 
                                        WHEN main_price_name = '全家福229元套餐' THEN 2
                                        WHEN main_price_name IN ('全家福199元套餐', '全家福159元套餐') THEN 1
                                        ELSE 0
                                     END) DESC) AS rank
FROM 
    stat.hour_prod_list 
WHERE 
    prod_type = 'cdma_rhzk'
    AND price_id IN (907995879, 907994888, 907995884, 907995888, 907994916)
    AND the_month = TO_CHAR(CURRENT_TIMESTAMP, 'YYYYmm')::text
    AND dev_staff_job = '分支局营销经理'
GROUP BY 
    bureau_name, 
    deal_staff_name
ORDER BY 
    f_d DESC
LIMIT 10;

下级写法，嵌套查询，可以代码简化