mysql inner group by_mysql - MySQL，多个COUNT INNER JOIN，GROUP BY DATE（） - 堆栈内存溢出...

我有一张安装日志表，我想从该表中选择

状态为= 1的每日安装次数

状态为0的每天安装次数

状态为IN(0，1)的每日安装次数

字段created_utc是我保留安装unix时间戳的位置。

这是我的表结构

CREATE TABLE `installs` (

`created_utc` int(11) DEFAULT NULL,

`status` tinyint(1) DEFAULT '1' COMMENT '1 - installed, 0 - deleted'

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

这是我的查询，无法正常工作(我选择u.id作为执行内部联接的拐杖……我完全迷失了)

SELECT

u.id,

count(pa.created_utc) AS installs_this_day, UNIX_TIMESTAMP(FROM_UNIXTIME(pa.created_utc, '%Y-%m-%d'))*1000 AS time_utc,

count(pa2.created_utc) AS installs_active_this_day

FROM

users u

INNER JOIN installs AS pa ON pa.status = 0

INNER JOIN installs AS pa2 ON pa2.status = 1

WHERE u.id = 1

GROUP BY DATE(from_unixtime(pa.created_utc)), DATE(from_unixtime(pa2.created_utc))

这将导致重复和不正确的计数。

1 45 1430280000000 45

1 36 1430280000000 36

1 117 1430280000000 117

1 72 1430280000000 72

1 36 1430280000000 36

1 531 1430280000000 531

1 144 1430280000000 144

1 36 1430280000000 36

1 18 1430280000000 18

1 27 1430280000000 27

1 9 1430280000000 9

安装表数据样本

INSERT INTO `installs` (`created_utc`, `status`)

VALUES

(1430320706, 0),

(1430319316, 0),

(1430311682, 0),

(1430311506, 0),

(1430311498, 0),

(1430311498, 0),

(1430311386, 0),

(1430311370, 0),

(1430311356, 0),

(1430311272, 1),

(1430278530, 1),

(1430229788, 1);