gpt4 book ai didi

php - 如何计算 BOOLEAN 列 MySQL

转载 作者:行者123 更新时间:2023-11-29 05:54:14 25 4
gpt4 key购买 nike

我有一个列名为“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
;

最佳答案

要对 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;

关于php - 如何计算 BOOLEAN 列 MySQL,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/51212924/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com