时间戳

首页 > 解决方案 > MySQL 订单问题

mysql - MySQL 订单问题

问题描述

有一张包含赛事结果的表格 - 赢家、输家、地点、日期、回合。想要输出玩家在赛事中的最佳成绩（第二次排序是按日期排序 - 所以如果他在 2012 年和 2014 年获胜，它将列出 2012 年的胜利）。如果玩家进入决赛并首先输掉比赛（2012 年进入决赛并输掉但随后在 2015 年获胜），则以下查询有效，但不会输出赛事 (W) 的胜利。

SELECT CASE WHEN win=(select id players where last like concat('smith','%') AND first like concat('b','%')) AND 
  round='f' AND tourn like concat('city','%')then 'W', los=(select id players where last like concat('smith','%') AND first like concat('b','%')) AND 
  round='f' AND tourn like concat('city','%')then 'F', los=(select id players where last like concat('smith','%') AND first like concat('b','%')) AND 
  round='sf' AND tourn like concat('city','%')then 'SF', los=(select id players where last like concat('smith','%') AND first like concat('b','%')) AND 
  round='qf' AND tourn like concat('city','%')then 'QF', los=(select id players where last like concat('smith','%') AND first like concat('b','%')) AND 
  round='2nd' AND tourn like concat('city','%')then '2nd', los=(select id players where last like concat('smith','%') AND first like concat('b','%')) AND 
  round='1st' AND tourn like concat('city','%')then '1st', end as round, date_format(thedate, '%Y') as 'theYear' from records order by field(round,'W','F','SF','QF','2nd','1st), thedate limit 1;

标签： mysql

解决方案

替换为按字段排序。用过的

order by CASE 
  WHEN round = 'W' THEN 0  
  WHEN round='F' THEN 1 
  WHEN round='SF' THEN 2
  etc
  END LIMIT 1

推荐阅读