mysql - SQL计算另一个表的所有行上的行

Question

我正在计算棒球运动员的相似度得分。我想做的是根据玩家 ID 选择一行，然后对所有其他现有玩家进行计算。

这是一般概念:

我编写的代码适用于单个playerID 与另一个playerID 的比较:

SELECT p.player, pc.player, 
     1000 - (Abs((p.G-pc.G)/20) + 
     Abs((p.AB-pc.AB)/75) + 
     Abs((p.R-pc.R)/10) +
     Abs((p.H-pc.H)/15) +
     Abs((p.D-pc.D)/5) +
     Abs((p.T-pc.T)/4) +
     Abs((p.HR-pc.HR)/2) +
     Abs((p.RBI-pc.RBI)/10) +
     Abs((p.BB-pc.BB)/25) +
     Abs((p.SO-pc.SO)/150) +
     Abs((p.SB-pc.SB)/20) +
     Abs((p.AVG-pc.AVG)*1000) +
     Abs((p.SLG-pc.SLG)*2000)) AS SIMscore

FROM(SELECT CONCAT(m.nameFirst, ' ', m.nameLast) AS player, 
     Sum(b.G) AS G, 
     Sum(b.AB) AS AB, 
     Sum(b.R) AS R, 
     Sum(b.H) AS H,
     Sum(b.2b) AS D, 
     Sum(b.3b) AS T, 
     Sum(b.HR) AS HR, 
     Sum(b.RBI) AS RBI, 
     Sum(b.BB) AS BB,     
     Sum(b.SO) AS SO, b.SB AS SB, 
     Sum(b.H)/Sum(b.AB) AS AVG,
     (Sum(b.H)+Sum(b.2b)*2+Sum(b.3B)*3+Sum(b.HR)*4)/Sum(b.AB) AS SLG
  FROM Batting b
  JOIN Master m
  ON b.playerID = m.playerID
  WHERE b.playerID = 'griffke02') AS p,
  (SELECT CONCAT(m.nameFirst, ' ', m.nameLast) AS player, 
      Sum(b.G) AS G, 
      Sum(b.AB) AS AB, 
      Sum(b.R) AS R, 
      Sum(b.H) AS H,
      Sum(b.2b) AS D, 
      Sum(b.3b) AS T, 
      Sum(b.HR) AS HR, 
      Sum(b.RBI) AS RBI, 
      Sum(b.BB) AS BB, 
      Sum(b.SO) AS SO, 
      Sum(b.SB) AS SB, 
      Sum(b.H)/Sum(b.AB) AS AVG,
      (Sum(b.H)+Sum(b.2b)*2+Sum(b.3B)*3+Sum(b.HR)*4)/Sum(b.AB) AS SLG
  FROM Batting b
  JOIN Master m
  ON b.playerID = m.playerID
  WHERE b.playerID = 'troutmi01') AS pc

但是，我需要它来将单个玩家 ID 与所有其他玩家进行比较，而不仅仅是 WHERE b.playerID = 'troutmi01'

SELECT CONCAT(m.nameFirst, ' ', m.nameLast) AS player, 
      Sum(b.G) AS G, 
      Sum(b.AB) AS AB, 
      Sum(b.R) AS R, 
      Sum(b.H) AS H,
      Sum(b.2b) AS D, 
      Sum(b.3b) AS T, 
      Sum(b.HR) AS HR, 
      Sum(b.RBI) AS RBI, 
      Sum(b.BB) AS BB, 
      Sum(b.SO) AS SO, 
      Sum(b.SB) AS SB, 
      Sum(b.H)/Sum(b.AB) AS AVG,
      (Sum(b.H)+Sum(b.2b)*2+Sum(b.3B)*3+Sum(b.HR)*4)/Sum(b.AB) AS SLG
  FROM Batting b
  JOIN Master m
  ON b.playerID = m.playerID

有什么见解吗？

Answer 1

SELECT CONCAT(m.nameFirst, ' ', m.nameLast) AS player, 
      Sum(b.G) AS G, 
      Sum(b.AB) AS AB, 
      Sum(b.R) AS R, 
      Sum(b.H) AS H,
      Sum(b.2b) AS D, 
      Sum(b.3b) AS T, 
      Sum(b.HR) AS HR, 
      Sum(b.RBI) AS RBI, 
      Sum(b.BB) AS BB, 
      Sum(b.SO) AS SO, 
      Sum(b.SB) AS SB, 
      Sum(b.H)/Sum(b.AB) AS AVG,
      (Sum(b.H)+Sum(b.2b)*2+Sum(b.3B)*3+Sum(b.HR)*4)/Sum(b.AB) AS SLG
  FROM Batting b
  JOIN Master m
  ON b.playerID = m.playerID
GROUP BY CONCAT(m.nameFirst, ' ', m.nameLast)

添加分组依据...

至于为什么需要了解Mysql Group By Extensions

具体来说:“但是，这主要是当每个组中未在 GROUP BY 中命名的每个非聚合列中的所有值对于每个组都相同时很有用。服务器可以自由地从每个组中选择任何值，因此除非它们相同，否则选择的值是不确定。”

它们与您的情况不同。每个记录的播放器都会不同，因此需要将其添加到分组依据中。