Mysql 创建带有计数子句的 View

Question

在我的数据库中，我有一个具有这种结构的表 (t1)

CREATE TABLE IF NOT EXISTS 't1' (
'id_ric' int(11) NOT NULL AUTO_INCREMENT,
'id_tipoins' decimal(1,0) NOT NULL,
'datains' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY ('id_ric')
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

我创建了一个 View 来计算以这种方式按 id_user 分组的所有记录

CREATE VIEW view_users_app
AS
SELECT  id_user, MAX(datains) last_datains, COUNT(*) totalCount 
FROM    t1
GROUP   BY id_user

现在我还想计算 id_tipoins = 1 和 id_tipoins = 2(按 id_user 分组)的位置。有可能吗？我怎么能这样做？谢谢

Answer 1

CREATE VIEW view_users_app
AS
SELECT  id_user, 
        MAX(datains) last_datains, 
        COUNT(*) totalCount,
        sum(id_tipoins = 1) as p1,
        sum(id_tipoins = 2) as p2
FROM    t1
GROUP   BY id_user