php - 如何计算 BOOLEAN 列 MySQL

Question

我有一个列名为“IS_QUICK”的表，类型为 TINYINT (0|1)；

我需要统计一段时间内他为真(1)和假(0)的次数:

在 MySQL 中:

    SELECT DIA, MES, HORA, ANO, QUICK, NOT_QUICK FROM (
    SELECT * ,(
        SELECT COUNT(rq1.IS_QUICK) FROM qp1_relatorio_quickview rq1 where rq1.IS_QUICK = 1 AND rq1.created_at BETWEEN '2018-07-03 00:00:00' AND '2018-07-06 23:59:59'
    ) as QUICK,
    (
        SELECT COUNT(rq1.IS_QUICK) FROM qp1_relatorio_quickview rq1 where rq1.IS_QUICK = 0 AND rq1.created_at BETWEEN '2018-07-03 00:00:00' AND '2018-07-06 23:59:59'
    ) as NOT_QUICK
   , YEAR(rq.created_at) as ANO
   , MONTH(rq.created_at) as MES
   , DAY(rq.created_at) as DIA
   , HOUR(rq.created_at) as HORA
 FROM qp1_relatorio_quickview rq WHERE rq.created_at BETWEEN '2018-07-03 00:00:00' AND '2018-07-06 23:59:59'

) as relatorio
GROUP BY DIA

但他返回第 3 天和第 6 天之间所有 IS_QUICK 的计数，而不是仅针对第 3 天(例如)

编辑:表:

    CREATE TABLE `qp1_relatorio_quickview` (
    `ID` INT(11) NOT NULL AUTO_INCREMENT,
    `IS_QUICK` TINYINT(1) NOT NULL DEFAULT '0',
    `PRODUTO_ID` BIGINT(20) NOT NULL DEFAULT '0',
    `PRODUTO_VARIACAO_ID` BIGINT(20) NOT NULL DEFAULT '0',
    `QUANTIDADE` INT(11) NOT NULL DEFAULT '0',
    `created_at` DATETIME NOT NULL,
    `updated_at` DATETIME NOT NULL,
    PRIMARY KEY (`ID`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=19
;

Answer 1

要对 bool 型TINYINT 字段进行计数，您可以简单地使用SUM(field) 来计算1，或者使用SUM(NOT field) 来计算计数 0。

SELECT
    SUM(IS_QUICK) AS QUICK,
    SUM(NOT IS_QUICK) AS NOT_QUICK
FROM qp1_relatorio_quickview rq
WHERE rq.created_at BETWEEN '2018-07-03 00:00:00' AND '2018-07-06 23:59:59';

上述查询将选择该期间的总计。例如，如果您希望每天对其进行汇总，请确保您选择并按所有相关字段分组:

SELECT
    YEAR(rq.created_at) as ANO,
    MONTH(rq.created_at) as MES,
    DAY(rq.created_at) as DIA,
    SUM(IS_QUICK) AS QUICK,
    SUM(NOT IS_QUICK) AS NOT_QUICK
FROM qp1_relatorio_quickview rq
WHERE rq.created_at BETWEEN '2018-07-03 00:00:00' AND '2018-07-06 23:59:59'
GROUP BY ANO, MES, DIA;