表:用户表:id,名称,***;(保存所有用户信息);
步数表:id,用户id,步数,时间,***;(保存所有用户步数信息,假如当天有步数一天一条,如果没有当天无记录);
好友表:id,好友1 id,好友2 id,状态,***;(保存好友关系,状态1好友,-1解除好友);
好友pk表:id,好友1 id,好友2 id,状态,***;(保存好友关系,状态输或赢);
需求:获取指定用户的所有好友列表(显示:名称,步数,PK状态),并按步数从大到小排序。
1.获取所有好友id(设指定好友id = 100003)
SELECT IF(aUserId=100003,bUserId,aUserId) AS friendUserId FROM `t_friends`
WHERE (aUserId = 100003 || bUserId = 100003) AND STATUS = 1;
2.从用户表中查找好友信息
SELECT u.`nickName`,u.avatarUrl,u.id,
FROM `t_user` u WHERE u.id IN
(SELECT IF(aUserId=100003,bUserId,aUserId) AS friendUserId FROM `t_friends`
WHERE (aUserId = 100003 || bUserId = 100003) AND STATUS = 1) ;
3.获取步数
SELECT s.step FROM `t_step` s WHERE TIMESTAMP = UNIX_TIMESTAMP(CURDATE()) AND s.`userId` = 用户id
4,获取比赛状态
SELECT mr.status FROM `t_match_running` mr WHERE TIME = UNIX_TIMESTAMP(CURDATE()) AND ( mr.`fromUserId`=用户id|| mr.`toUserId` = 用户id )
5.ifnull(b,a):表示假如b是null,就取值为a,如果b不是null,则值取b值。
组装:
SELECT u.`nickName`,u.avatarUrl,u.id,
IFNULL((SELECT s.step FROM `t_step` s WHERE TIMESTAMP = UNIX_TIMESTAMP(CURDATE()) AND u.id = s.`userId`),0) AS step ,
IFNULL((SELECT mr.status FROM `t_match_running` mr WHERE TIME = UNIX_TIMESTAMP(CURDATE()) AND ( mr.`fromUserId`=u.`id`|| mr.`toUserId` = u.id )),0) AS STATUS
FROM `t_user` u WHERE u.id IN
(SELECT IF(aUserId=100003,bUserId,aUserId) AS friendUserId FROM `t_friends`
WHERE (aUserId = 100003 || bUserId = 100003) AND STATUS = 1) ORDER BY step DESC;