表结构:会员表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头脑里面还有投影笛卡尔积的模型,忘干净了,还得抽空看看逻辑查询语言