我的网站允许用户猜测体育比赛的结果.在比赛结束时,应将猜测与实际结果进行比较.获胜者是具有最接近正确猜测的成员
我正在寻找一种方法来返回所有猜测正确结果和得分差异的成员如果没有(零)成员猜对正确返回猜测最接近正确结果的成员
我修改了脚本以更改固定值,如下所示
if(isset($_POST['resultBtn'])){
foreach($_POST['winner'] as $id =>$winner){
$winscore = $_POST['score'][$id];
:
:
$sql="SELECT p.*
FROM Multiple_Picks p
WHERE p.event_id='$matchId' AND
p.pick='$winner' AND
abs(p.score-'$winscore') = (SELECT min(abs(p2.score-1))
FROM Multiple_Picks p2
Where p2.pick=p.pick AND
p2.event_id = p.event_id)";
我的问题是如果我在下表中运行此脚本：
即使我将结果完全正确,NOTHING也会显示：
我的变量值在sql语句中是正确的,所以这不是问题
任何帮助将受到欢迎……
重要的是,选择最严格的结果的用户,对于所有游戏,在圆圈期间是赢家
在这个答案中,我称之为“最佳”选择任何为特定比赛选择了正确赢家的选秀权,并且与实际比赛得分的得分最接近.
这些脚本也尊重竞争中的不同“轮次”,因为这是一个重要的复杂因素.
这个答案分为两部分：首先是一个类似于问题中的查询,该查询返回特定匹配的所有“最佳”选择.为了更容易在sql Fiddle中运行,我使用了MysqL变量而不是PHP变量.
带有测试数据的模式：
create table Multiple_Picks ( pick_id int,member_nr int,event_id int,pick varchar(100),score int ); insert into Multiple_Picks values (11,100,1,'Crusaders',15),(12,2,'Waratahs',10),(13,3,'Chiefs',4),(21,200,(22,(23,'Lions',(31,300,(32,12),(33,6),(41,4,20),(42,5,(43,6,20) ;
set @matchId = 2; set @winner = 'Waratahs'; set @winscore = 8; -- Show all picks for a particular match select * from Multiple_Picks where event_id = @matchId; -- Show best picks for a particular match select p.* from Multiple_Picks p where p.event_id = @matchId and p.pick = @winner and abs(p.score - @winscore) = (select min(abs(other.score - @winscore)) from Multiple_Picks other where other.event_id = @matchId and other.pick = @winner ) ;
SQL Fiddle to show picks for particular match
-- Show all picks for a particular match +---------+-----------+----------+----------+-------+ | pick_id | member_nr | event_id | pick | score | +---------+-----------+----------+----------+-------+ | 12 | 100 | 2 | Waratahs | 10 | | 22 | 200 | 2 | Waratahs | 10 | | 32 | 300 | 2 | Waratahs | 12 | +---------+-----------+----------+----------+-------+ -- Show best picks for a particular match +---------+-----------+----------+----------+-------+ | pick_id | member_nr | event_id | pick | score | +---------+-----------+----------+----------+-------+ | 12 | 100 | 2 | Waratahs | 10 | | 22 | 200 | 2 | Waratahs | 10 | +---------+-----------+----------+----------+-------+
现在我们需要努力寻找每一轮比赛的胜利者.
首先,我们有额外的测试数据,其中包含第1轮和第2轮中匹配的实际分数.
create table Matches ( event_id int,winner varchar(100),score int,round int ); insert into Matches values (1,10,1),(2,11,(3,(4,20,2),(5,(6,2) ;
现在选择所有匹配的最佳选择.子选择(别名为m)计算每次匹配的best_diff,作为实际得分与每个猜测得分之间的最小差异.然后将此子选择连接到每个选择,以便仅返回“最佳”选择.
-- Show all best picks for all Matches select p.*,m.round from Multiple_Picks p join ( select m2.event_id,m2.winner,m2.score,m2.round,min(abs(m2.score-p2.score)) as best_diff from Matches m2 join Multiple_Picks p2 on p2.event_id = m2.event_id and p2.pick = m2.winner group by m2.event_id,m2.round ) as m on p.event_id = m.event_id and p.pick = m.winner and abs(m.score - p.score) = m.best_diff order by m.round,p.event_id ;
通过使用member_nr和round对上一个查询进行分组,可以轻松获得每轮每个玩家的最佳选择数：
-- Show a count of best picks for each player for each round select p.member_nr,m.round,count(*) as best_count from Multiple_Picks p join ( select m2.event_id,m2.round ) as m on p.event_id = m.event_id and p.pick = m.winner and abs(m.score - p.score) = m.best_diff group by p.member_nr,m.round order by m.round,count(*) desc ;
SQL Fiddle for all best picks and counts for all matches
-- Show all best picks for all Matches +---------+-----------+----------+-----------+-------+-------+ | pick_id | member_nr | event_id | pick | score | round | +---------+-----------+----------+-----------+-------+-------+ | 31 | 300 | 1 | Crusaders | 15 | 1 | | 21 | 200 | 1 | Crusaders | 15 | 1 | | 11 | 100 | 1 | Crusaders | 15 | 1 | | 12 | 100 | 2 | Waratahs | 10 | 1 | | 32 | 300 | 2 | Waratahs | 12 | 1 | | 22 | 200 | 2 | Waratahs | 10 | 1 | | 23 | 200 | 3 | Lions | 4 | 1 | | 41 | 100 | 4 | Crusaders | 20 | 2 | | 42 | 100 | 5 | Waratahs | 20 | 2 | +---------+-----------+----------+-----------+-------+-------+ -- Show a count of best picks for each player for each round +-----------+-------+------------+ | member_nr | round | best_count | +-----------+-------+------------+ | 200 | 1 | 3 | | 300 | 1 | 2 | | 100 | 1 | 2 | | 100 | 2 | 2 | +-----------+-------+------------+
最后阶段是仅选择那些拥有最多最佳选秀权的每轮选手.我尝试修改上面的查询,但嵌套变得令人困惑,所以我的解决方案是创建一些逻辑视图,以便更容易理解最终查询.视图基本上封装了我上面解释的查询的逻辑：
create view MatchesWithBestDiff as select m.event_id,m.winner,m.score,min(abs(m.score-p.score)) as best_diff from Matches m join Multiple_Picks p on p.event_id = m.event_id and p.pick = m.winner group by m.event_id,m.round ; create view BestPicks as select p.*,m.round from Multiple_Picks p join MatchesWithBestDiff m on p.event_id = m.event_id and p.pick = m.winner and abs(m.score - p.score) = m.best_diff ; create view BestPickCount as select member_nr,round,count(*) as best_count from BestPicks group by member_nr,round ;
-- Show the players with the highest number of Best Picks for each round
select *
from BestPickCount p
where best_count =
(
select max(other.best_count)
from BestPickCount other
where other.round = p.round
)
order by round
;
SQL Fiddle for players with most Best picks for each round
-- Show the players with the highest number of Best Picks for each round +-----------+-------+------------+ | member_nr | round | best_count | +-----------+-------+------------+ | 200 | 1 | 3 | | 100 | 2 | 2 | +-----------+-------+------------+
整个调查让我想起让sql做多次操作是多么棘手,需要根据最大值和总和来选择记录.使用窗口函数(OVER和PARTITION BY子句)可以更容易地进行某些类型的查询,但它们在MysqL中不可用.
在设计上述查询时,我发现了一些有趣的限制：MysqL不允许在视图定义中加入子查询.ANSI sql不允许子查询中的聚合引用内部查询中的列和外部查询中的列. MysqL似乎有时允许这样做,但我无法找到关于何时允许的明确指导,因此我选择编写上述查询来避免这种“功能”.
