mysql计数null

发布于 2025-02-09 19:27:08 字数 710 浏览 3 评论 0原文

我有一个game1桌子，似乎是这样的：

player1	player2	result_1	result_2
team1 team1	team2	10	null
team1	team3	10	null
team2 team2	team3	6	null

result_1是player1的结果，result_2是player2的结果。

我想在下表：

播放器	count_null
team1	0
Team2	1
Team3	2

请帮助我

原文

I have a game1 table,
it seems like this:

player1	player2	result_1	result_2
team1	team2	10	NULL
team1	team3	10	NULL
team2	team3	6	NULL

result_1 is player1's result and result_2 is player2's result.

I want to make below table:

player	count_null
team1	0
team2	1
team3	2

Please help me

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

眼藏柔 2025-02-16 19:27:08

您可以通过将表列“分配”成更多的行来做到这一点，然后按播放器（团队）进行分组，然后总和1当结果为null时

SELECT player, sum(if(result is null, 1, 0))
FROM (
  SELECT player1 as player, result_1 as result
  FROM game1
  UNION ALL
  SELECT player2 as player, result_2 as result
  FROM game1) t
GROUP BY player

You can do that by "dividing" the table columns into more rows, then group by player (team) and then sum 1 when the result is null

SELECT player, sum(if(result is null, 1, 0))
FROM (
  SELECT player1 as player, result_1 as result
  FROM game1
  UNION ALL
  SELECT player2 as player, result_2 as result
  FROM game1) t
GROUP BY player

回复收藏 0 原文

~没有更多了~