sqlite - 对单个 group by 子句中不同值集的同一列进行计数

Question

我有一个像这样的表(SQLite DB)，

CREATE TABLE解析器(ip文本、用户文本、代码文本)；

现在我需要计算有多少 code 的值为 1、2 或 3，以及有多少不是，按 ip< 分组 字段。

但据我所知，我无法完全做到这一点，只能使用两个 SQL 短语。

例如

select count(*) as cnt, ip 
from parser 
where code in (1, 2, 3) 
group by ip 
order by cnt DESC 
limit 10

还有一个not in查询。

那么，我可以将两个查询合并为一个查询吗？

Answer 1

这将为每个 ip 提供两个计数，一个用于 code 值为 1、2 或 3 的行，另一个计数用于所有其余的行(除了1、2、3，包括 NULL。)

SELECT ip,
       COUNT(CASE WHEN code IN (1, 2, 3) THEN 1 ELSE NULL END) AS cnt_in, 
       COUNT(CASE WHEN code IN (1, 2, 3) THEN NULL ELSE 1 END) AS cnt_rest
FROM parser  
GROUP BY ip 
ORDER BY cnt_in DESC ;

这将为您提供 3 个计数，一个用于 1、2、3，另一个用于其余整数值，第三个用于 code 中具有 NULL 的行:

SELECT ip,
       COUNT(CASE WHEN code IN (1, 2, 3) THEN 1 END) AS cnt_in, 
       COUNT(CASE WHEN code NOT IN (1, 2, 3) THEN 1 END) AS cnt_not_in,
       COUNT(CASE WHEN code IS NULL THEN 1 END) AS cnt_null
FROM parser  
GROUP BY ip 
ORDER BY cnt_in DESC ;

<小时/>

如果您想将第一个结果(作为您的代码)限制为前 10 行，将第二个结果限制为其他前 10 行，您可以使用两个子查询和一个 UNION:

( SELECT ip,
         COUNT(*) AS cnt, 
         'in' AS type
  FROM parser
  WHERE code IN (1, 2, 3)
  GROUP BY ip 
  ORDER BY cnt DESC 
  LIMIT 10 
)
UNION ALL
( SELECT ip,
         COUNT(*) AS cnt, 
         'not in' AS type
  FROM parser
  WHERE code NOT IN (1, 2, 3)
  GROUP BY ip 
  ORDER BY cnt DESC 
  LIMIT 10 
) ;

测试于SQL-Fiddle