如何计算布尔列

dnph8jn4 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(252)

我有一个列名为“is|u 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天之间的返回计数很快，而不仅仅是第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
;

mysql php

来源：https://stackoverflow.com/questions/51212924/how-to-count-boolean-columns-mysql

2条答案

按热度按时间

brgchamk1#

计算布尔值 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;

赞(0）回复(0）举报 2021-06-20

bwleehnv2#

嗨，你能试着用sum吗

SELECT
    sum(CASE WHEN rq1.IS_QUICK= 1 THEN 1 ELSE 0 END) as IS_QUICK,
    sum(CASE WHEN rq1.IS_QUICK= 0 THEN 1 ELSE 0 END) 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'

赞(0）回复(0）举报 2021-06-20

我来回答

如何计算布尔列

2条答案

相关问题

热门标签

最新问答