mysql - 对多个 COUNT 求和的 UNION 语法

Question

这些查询单独工作。如何编写 UNION 以对所有结果求和？

SELECT SUM(players) FROM `tournament_players` FROM (
SELECT COUNT(*) as `players` FROM `tournament_players` WHERE `foursome_1_p1_name` IS NOT NULL AND `tournament` BETWEEN 13 AND 17 AND flight = '8 AM'
UNION
SELECT COUNT(*) as `players` FROM `tournament_players` WHERE `foursome_1_p2_name` IS NOT NULL AND `tournament` BETWEEN 13 AND 17 AND flight = '8 AM'
UNION
SELECT COUNT(*) as `players` FROM `tournament_players` WHERE `foursome_1_p3_name` IS NOT NULL AND `tournament` BETWEEN 13 AND 17 AND flight = '8 AM'
UNION
SELECT COUNT(*) as `players` FROM `tournament_players` WHERE `foursome_1_p4_name` IS NOT NULL AND `tournament` BETWEEN 13 AND 17 AND flight = '8 AM'
)

Answer 1

您走在正确的轨道上，但有一个额外的 FROM 关键字。试试这个:

SELECT SUM(players) FROM (
SELECT COUNT(*) as `players` FROM `tournament_players` WHERE `foursome_1_p1_name` IS NOT NULL AND `tournament` BETWEEN 13 AND 17 AND flight = '8 AM'
UNION
SELECT COUNT(*) as `players` FROM `tournament_players` WHERE `foursome_1_p2_name` IS NOT NULL AND `tournament` BETWEEN 13 AND 17 AND flight = '8 AM'
UNION
SELECT COUNT(*) as `players` FROM `tournament_players` WHERE `foursome_1_p3_name` IS NOT NULL AND `tournament` BETWEEN 13 AND 17 AND flight = '8 AM'
UNION
SELECT COUNT(*) as `players` FROM `tournament_players` WHERE `foursome_1_p4_name` IS NOT NULL AND `tournament` BETWEEN 13 AND 17 AND flight = '8 AM'
) `tournament players`