需求
这是前天看见的一个面试题。
在这里有两个表,一个用户表和一个用户积分记录表。
需求分析
目的
找到总积分前10的用户和用户信息
现有条件
用户表和积分记录表,唯一的联系就是会员uid。
需求实现
新建两个表
用户表
积分记录表
积分表用户积分求和
select uid, SUM(score) as total from scoreTb group by uid;
求和后排序
需求是求前10的,这里数据量没那么大就求前5把,既然是前5,我们就降序排列。这里就要使用MySQL排序了。
使用 ORDER BY 子句将查询数据排序后再返回;
ASC:升序(默认)
DESC :降序。
根据总积分排序,那么我需要使用求和后的总积分了。
select uid, SUM(score) as total from scoreTb group by uid order by total desc;
找到前5个
这里总积分有了,排序也有了。剩下的是找到总积分排序中的前5个。
需要使用MySQL的LIMIT了。
LIMIT子句接受一个或两个参数。两个参数的值必须为零或正整数。
LIMIT offset , count;
offset:参数指定要返回的第一行的偏移量。第一行的偏移量为0,而不是1。
count:指定要返回的最大行数。
但是LIMIT 0,count
和LIMIT count
是一样的结果,取表中的前count行。
select uid, SUM(score) as total from scoreTb group by uid order by total desc limit 5;
这里我们就取到了积分表中总积分前5的了,但是还不知道用户信息。
查找用户信息
这里我们希望是通过前面找到的总分前5的uid来找用户信息,然后把结果拼接在一起输出。既然是两个表的结果拼接就需要LEFT JOIN
。那么sql语句就是这样的了:
select * from userTb t left join (select uid, SUM(score) as total from scoreTb group by uid order by total desc limit 5) tab on t.uid=tab.uid;
这里其实我们已经找到了总分前5的人的信息了。但是结果似乎有点问题。
问题分析
这里我们先看看上面的sql语句的逻辑:
1.根据用户uid求用户总积分;
2.根据总积分按照降序排列;
3.取上面获得的数据中的前5个;
4.根据上面的结果去用户表查找,结果做链接在一起输出。
这个过程似乎没任何问题,但是实际的查询结果却输出了下面那些NULL
我们不需要的数据。但是这里为什么?我也不知道原因。
解决问题
解决问题就只能换个思路了。
先对用户积分表排序排序,然后找根据这个结果去用户表找,最后的结果在输出前5条。
select * from userTb t left join (select sum(score) as my_score,uid from scoreTb group by uid order by my_score desc) tab on tab.uid = t.uid limit 5;
这样就好了。
其他
// 修改字段类型
alter table userTb modify column uid(字段名) text(字段类型);
// 修改字段名
alter table scoreTb change name(原字段名) score(目标字段名) text(字段类型);
http://www.runoob.com/mysql/mysql-alter.html
http://www.yiibai.com/mysql/alter-table.html