mysql COUNT DISTINCT multiple columnes with WHERE

Question

我试图从一个表中生成数据，该表创建一个报告，显示位置a,b,c在最近一段时间(@dt)中发生了多少次。

我可以使单个SELECT查询工作，但不知道如何将它们分组在一起并生成完整的报告。错误是1241:操作数应该包含1列，我花了一个小时通过以前的答案工作，但我完全卡住了。

SELECT (
SELECT DISTINCT pid, SUM(location like '%a%') FROM db.t WHERE (date > @dt) GROUP BY pid)
 AS 'a', (
SELECT DISTINCT pid, SUM(location like '%b%') FROM db.t WHERE (date > @dt) GROUP BY pid)
 AS 'b', (
SELECT DISTINCT pid, SUM(location like '%c%') FROM db.t WHERE (date > @dt) GROUP BY pid)
 AS 'c';

Answer 1

您正在查找每个pid的计数吗?如果是，这是一个更简单的查询:

 SELECT pid, SUM(location like '%a%') as As,
        SUM(location like '%b%') as Bs,
        SUM(location like '%c%') as Cs
 FROM db.t
 WHERE (date > @dt)
 GROUP BY pid