php – mysql – 获取最接近正确值的值,根据结果返回0到很多

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)";

在这个答案中,我称之为“最佳”选择任何为特定比赛选择了正确赢家的选秀权,并且与实际比赛得分的得分最接近.

这些脚本也尊重竞争中的不同“轮次”,因为这是一个重要的复杂因素.

这个答案分为两部分：首先是一个类似于问题中的查询,该查询返回特定匹配的所有“最佳”选择.为了更容易在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似乎有时允许这样做,但我无法找到关于何时允许的明确指导,因此我选择编写上述查询来避免这种“功能”.