从一个表中选择所有行，为另一个表中的每一行选择一个特定值

问题描述：

Good afternoon lads, I am trying to make a page where I can check which bosses I did today. I have two tables (table with bosses and table with boss times). Now I need to show all bosses but for each of them I only want to show the closest time when the boss is going to spawn.

The select so far looks like that:

$timePlus = strtotime($currentTime) + 60*60*2.2;
$timePlusForm = date("H:i:s", $timePlus);
$userNametoLower = strtolower($userName);
$userTableName = "".$userNametoLower."_bosses";
$currentTime = date("H:i:s", time());
"SELECT `bossTime`.`ID`, `bossTime`.`bossID`, `bossTime`.`time`, `$userTableName`.`ID`, `$userTableName`.`name`, 
                  `$userTableName`.`zone`, `$userTableName`.`map`, `$userTableName`.`waypointCode`, `$userTableName`.`bossDone`
                  FROM `bossTime` LEFT JOIN `$userTableName` ON `$userTableName`.`ID` = `bossTime`.`bossID`
                  WHERE `bossTime`.`time` BETWEEN '$currentTime' AND '$timePlusForm'
                  GROUP BY `bossTime`.`bossID`
                  ORDER BY `bossTime`.`time` ASC";

The problem is that this select does not pick the next closest value from time table. I also tried BETWEEN and it also didn't work (some bosses got correct closest time but other got the second closest). Any idea how to solve this is welcomed.

下午好的小伙子，我正在尝试制作一个页面，我可以查看今天我做过哪些老板。我有两张桌子（有老板的桌子和老板时间的桌子）。现在我需要展示所有老板，但对于他们每个人我只想显示老板要产卵的最近时间。 p>

到目前为止的选择看起来像这样： p>

  $ timePlus = strtotime（$ currentTime）+ 60 * 60 * 2.2; 
  $ timePlusForm = date（“H：i：s”，$ timePlus）; 
 $ userNametoLower = strtolower（$ userName）; 
 $ userTableName =“”。$ userNametoLower。“_ bosses”; 
 $ currentTime = date（  “H：i：s”，time（））; 
“SELECT`bossTime``ID`，`bossTime``bossID`，`bossTime` .time`，`$ userTableName` .ID`，  `$ userTableName` .name`，
`$ userTableName` .zone`，`$ userTableName` .map`，`$ userTableName``waypointCode`，`$ userTableName``bossDone` 
 FROM`  bossTime` LEFT JOIN` $ userTableName` ON` $ userTableName` .ID` =`bossTime``bossID` 
 WHERE`bossTime``time` BETWEEN'$ currentTime'AND'$ timePlusForm'
 GROUP BY`  bossTime``bossID` 
 ORDER BY`bossTime` .time` ASC“; 
  code>  pre> 
 
 问题是此选择不会选择下一个最接近的值 从时间表。 我也尝试了BETWEEN，它也没有用（一些老板得到了正确的最近时间，但其他人得到了第二个最接近的）。 任何想法如何解决这个问题都很受欢迎。 p> 
  div>

答

I removed GROUP BY and changed the condition to WHERE bossTime.time >= '$currentTime' AND bossTime.time <='$timePlusForm' and for some reason it works

从一个表中选择所有行，为另一个表中的每一行选择一个特定值

相关推荐