mysql - 如何统一SQL COUNT

Question

我有这段代码，我想最小化它，但对我来说没有任何作用:

SELECT(SELECT COUNT(nes) FROM general_list WHERE nes<>0 AND saga LIKE '%/FFI/%')+
(SELECT COUNT(snes) FROM general_list WHERE snes<>0 AND saga LIKE '%/FFI/%')+
(SELECT COUNT(msx2) FROM general_list WHERE msx2<>0 AND saga LIKE '%/FFI/%')+
(SELECT COUNT(gb) FROM general_list WHERE gb<>0 AND saga LIKE '%/FFI/%')+
(SELECT COUNT(gc) FROM general_list WHERE gc<>0 AND saga LIKE '%/FFI/%')+
(SELECT COUNT(gba) FROM general_list WHERE gba<>0 AND saga LIKE '%/FFI/%')+
(SELECT COUNT(ds) FROM general_list WHERE ds<>0 AND saga LIKE '%/FFI/%')+
(SELECT COUNT(ds3) FROM general_list WHERE ds3<>0 AND saga LIKE '%/FFI/%')+
(SELECT COUNT(wii) FROM general_list WHERE wii<>0 AND saga LIKE '%/FFI/%')+
(SELECT COUNT(pc) FROM general_list WHERE pc<>0 AND saga LIKE '%/FFI/%')+
(SELECT COUNT(ps1) FROM general_list WHERE ps1<>0 AND saga LIKE '%/FFI/%')+
(SELECT COUNT(ps2) FROM general_list WHERE ps2<>0 AND saga LIKE '%/FFI/%')+
(SELECT COUNT(ps3) FROM general_list WHERE ps3<>0 AND saga LIKE '%/FFI/%')+
(SELECT COUNT(ps4) FROM general_list WHERE ps4<>0 AND saga LIKE '%/FFI/%')+
(SELECT COUNT(psp) FROM general_list WHERE psp<>0 AND saga LIKE '%/FFI/%')+
(SELECT COUNT(wsc) FROM general_list WHERE wsc<>0 AND saga LIKE '%/FFI/%')+
(SELECT COUNT(x360) FROM general_list WHERE x360<>0 AND saga LIKE '%/FFI/%')+
(SELECT COUNT(xone) FROM general_list WHERE xone<>0 AND saga LIKE '%/FFI/%') AS SumCount;

我尝试使用 SELECT nes,snes,msx2.......,COUNT(*) 并将列放入 COUNT 中 (SELECT COUNT(nes,snes.....)...) ，但两者都不起作用:(

非常感谢!

Answer 1

您需要条件聚合。这是一个开始:

SELECT (COUNT(CASE WHEN nes <> 0 AND saga LIKE '%/FFI/%' THEN nes END) +
        COUNT(CASE WHEN snes<>0 AND saga LIKE '%/FFI/%' THEN snes END) +
        COUNT(CASE WHEN msx2<>0 AND saga LIKE '%/FFI/%' THEN msx2 END) +
        . . .
       ) as SumCount
FROM general_list;

鉴于 saga 部分完全相同，您可以将其简化为:

SELECT (COUNT(CASE WHEN nes <> 0 THEN nes END) +
        COUNT(CASE WHEN snes <> 0 THEN snes END) +
        COUNT(CASE WHEN msx2 <> 0 THEN msx2 END) +
        . . .
       ) as SumCount
FROM general_list
WHERE saga LIKE '%/FFI/%' ;

而且，这可能可以使用 sum() 来简化:

SELECT (SUM(nes <> 0) +
        SUM(snes <> 0) +
        SUM(msx2 <> 0) +
        . . .
       ) as SumCount
FROM general_list
WHERE saga LIKE '%/FFI/%' ;

MySQL 将 bool 值视为整数，1 为 true。因此，使用 SUM() 计算匹配值的数量。