sql查询分组排行第一的记录

表结构：会员表member(member_id, member_name) ， 订单表order(order_id, order_source， order_amount, order_time, member_id)， 订单菜品表order_dish(order_dish_id, dish_price,dish_name, order_id)。

题目1： 获取所有会员的第一笔消费金额, 如果不满500元或者未消费，显示为0元。

Select m.member_id, m.member_name,(Case When o.order_amount < 500 Then 0 Else o.order_amount) as amount 
From member m left join (
Select member_id, order_amount, Row_Number() over (partition by member_id order by order_time) as rowNum from order
) o on m.member_id = o.member_id Where rowNum = 1

上面的sql使用了case when then的搜索模式，用了sql server2005、oracle特有的函数 row_number over (partition by .. order by ..)， row_number能根据排序字段给出一个从1开始的唯一序列号。嘿嘿，如果是不支持row_number函数的mysql呢

SELECT m.member_id, m.member_name,(CASE WHEN o.order_amount < 500 THEN 0 ELSE o.order_amount) AS amount 
FROM member m LEFT JOIN order o ON m.member_id = o.member_id INNER JOIN (
SELECT member_id, MIN(order_time) FROM ORDER GROUP BY member_id
) f ON o.member_id = f.member_id AND o.order_time = f.order_time

题目2：根据第一笔消费金额的大小给会员的进行消费排名，金额相同排名相同

Select m.member_id, m.member_name,Rank() Over (order by o.order_amount desc)
From member m left join (
Select member_id, order_amount, Row_Number() over (partition by member_id order by order_time) as rowNum from order
) o on m.member_id = o.member_id Where rowNum = 1

使用了rank函数。在相同的排名后面会空出排名。如1，1，3.而dense_rank不会空出排名。

哈哈,玩玩这个挺有意思的,大学时候写sql头脑里面还有投影笛卡尔积的模型,忘干净了,还得抽空看看逻辑查询语言

转载于:https://www.cnblogs.com/zzq417/p/sql_test.html