mysql 一行两列_mysql – 计算一行中两列或多列的值超过某个值[篮球,双倍,三倍]...

我玩一个篮球游戏,允许将其统计数据输出为数据库文件,因此可以计算游戏中未实现的统计数据.到目前为止,我已经毫无疑问地计算了我想要的统计数据,但是现在我遇到了一个问题：从他的比赛统计数据中计算一名球员本赛季的双打和/或三双的数量.

双倍和双倍的定义如下：

两双：

A double-double is defined as a performance in which a player accumulates a double-digit number total in two of five statistical categories—points, rebounds, assists, steals, and blocked shots—in a game.

三双：

A triple-double is defined as a performance in which a player accumulates a double digit number total in three of five statistical categories—points, rebounds, assists, steals, and blocked shots—in a game.

四倍双倍(添加澄清)

A quadruple-double is defined as a performance in which a player accumulates a double digit number total in four of five statistical categories—points, rebounds, assists, steals, and blocked shots—in a game.

“PlayerGameStats”表存储玩家玩的每个游戏的统计数据,如下所示：

CREATE TABLE PlayerGameStats AS SELECT * FROM ( VALUES

( 1, 1, 1, 'Nuggets', 'Cavaliers', 6, 8, 2, 2, 0 ),

( 2, 1, 2, 'Nuggets', 'Clippers', 15, 7, 0, 1, 3 ),

( 3, 1, 6, 'Nuggets', 'Trailblazers', 11, 11, 1, 2, 1 ),

( 4, 1, 10, 'Nuggets', 'Mavericks', 8, 10, 2, 2, 12 ),

( 5, 1, 11, 'Nuggets', 'Knicks', 23, 12, 1, 0, 0 ),

( 6, 1, 12, 'Nuggets', 'Jazz', 8, 8, 11, 1, 0 ),

( 7, 1, 13, 'Nuggets', 'Suns', 7, 11, 2, 2, 1 ),

( 8, 1, 14, 'Nuggets', 'Kings', 10, 15, 0, 3, 1 ),

( 9, 1, 15, 'Nuggets', 'Kings', 9, 7, 5, 0, 4 ),

(10, 1, 17, 'Nuggets', 'Thunder', 13, 10, 10, 1, 0 )

) AS t(id,player_id,seasonday,team,opponent,points,rebounds,assists,steals,blocks);

我想要实现的输出如下所示：

| player_id | team | doubleDoubles | tripleDoubles |

|-----------|---------|---------------|---------------|

| 1 | Nuggets | 4 | 1 |

到目前为止我发现的唯一解决方案是如此糟糕让我呕吐……; o)…它看起来像这样：

SELECT

player_id,

team,

SUM(CASE WHEN(points >= 10 AND rebounds >= 10) OR

(points >= 10 AND assists >= 10) OR

(points >= 10 AND steals >= 10)

THEN 1

ELSE 0

END) AS doubleDoubles

FROM PlayerGameStats

GROUP BY player_id

……现在你读完这篇文章之后,你可能也在呕吐(或者笑得很开心).我甚至没有写出获得所有双重组合所需的所有内容,并省略了三重双重的case语句,因为它更加荒谬.

有一个更好的方法吗？使用我拥有的表结构或使用新的表结构(我可以编写一个脚本来转换表).

我可以使用MySQL 5.5或PostgreSQL 9.2.

这是一个带有示例数据的SqlFiddle链接以及我上面发布的糟糕解决方案：http://sqlfiddle.com/#!2/af6101/3

请注意,我并不真正对四倍双打(见上文)感兴趣,因为据我所知,它在我玩的游戏中没有出现,但如果查询很容易扩展而没有多少重写,那将是一个加分四倍双打.