mySQL - 放入列的正确方法(UNION 或其他？)

Question

我试图找出为什么它给我 2 行而不是 1 行，正如您在“0130”行中看到的那样。基本上我想做的是获取敌人名称并根据服务器和状态中的联盟数量进行检查(取决于用户选择的内容，服务器将始终是整个数字，而状态会有所不同)。

第一个 UNION 显示整个服务器(始终相同的数字)第二个 UNION 显示取决于用户选择的状态(基于分组依据的随机数)

除非我在查询中的想法完全错误，否则我很想听听正确的方法是什么!

SELECT
    ASEnemy,ASFlagStatus,COUNT(SCAlliance) Server,0 State
    FROM AllianceStatus
    INNER JOIN SC75 ON (SCAlliance = ASEnemy AND SCSnap = (SELECT MAX(DSSnap) FROM DataSnap WHERE DSServer = 75))
    WHERE ASServer = 75
    AND ASOurs = '1057'
    AND (((SCFieldID % 800) >= 0 AND (SCFieldID % 800) <= 799) AND (FLOOR(SCFieldID / 800) >= 0 AND FLOOR(SCFieldID / 800) <= 799))
    GROUP BY BINARY SCAlliance
UNION ALL
SELECT
    ASEnemy,ASFlagStatus,0 Server,COUNT(SCAlliance) State
    FROM AllianceStatus
    INNER JOIN SC75 ON (SCAlliance = ASEnemy AND SCSnap = (SELECT MAX(DSSnap) FROM DataSnap WHERE DSServer = 75))
    WHERE ASServer = 75
    AND ASOurs = '1057'
    AND (((SCFieldID % 800) >= 400 AND (SCFieldID % 800) <= 599) AND (FLOOR(SCFieldID / 800) >= 600 AND FLOOR(SCFieldID / 800) <= 799))
    GROUP BY BINARY SCAlliance
    ORDER BY ASFlagStatus,BINARY ASEnemy
LIMIT 100

上面使用 mySQL 的当前输出示例:

ASEnemy ASFlag  Server  State
000001  1       398         0
0130    1       699         0
0130    1       0           263
04      1       240         0
04      1       0           22
08      1       456         0
08      1       0           3
1001    1       870         0
1001    1       0           438

我想看到的输出是:

ASEnemy ASFlag  Server  State
000001  1       398         0
0130    1       699         263
04      1       240         22
08      1       456         3
1001    1       870         438

提前致谢!

Answer 1

要获得您想要的输出，您可以将原始查询包装在另一个查询中以获取每组的最大值:

SELECT ASEnemy, ASFlagStatus, MAX(Server) Server, MAX(State) State
FROM (
-- your original query here
) src
GROUP BY ASEnemy, ASFlagStatus

或者您可以重写它并通过移动 count 函数中 case 表达式内不同的条件部分来使用条件聚合。

它可能看起来像这样(但我显然已经测试过它，因为没有可供测试的示例数据):

SELECT
 ASEnemy,
 ASFlagStatus,
 COUNT(CASE WHEN (((SCFieldID % 800) >= 0 AND (SCFieldID % 800) <= 799) AND (FLOOR(SCFieldID / 800) >= 0 AND FLOOR(SCFieldID / 800) <= 799)) THEN SCAlliance ELSE NULL END) Server,
 COUNT(CASE WHEN (((SCFieldID % 800) >= 400 AND (SCFieldID % 800) <= 599) AND (FLOOR(SCFieldID / 800) >= 600 AND FLOOR(SCFieldID / 800) <= 799)) THEN SCAlliance ELSE NULL END) State
FROM AllianceStatus
INNER JOIN SC75 ON (SCAlliance = ASEnemy AND SCSnap = (SELECT MAX(DSSnap) FROM DataSnap WHERE DSServer = 75))
WHERE ASServer = 75
  AND ASOurs = '1057'
GROUP BY BINARY SCAlliance
ORDER BY ASFlagStatus,BINARY ASEnemy
LIMIT 100