不一致的MySQL查询结果
P粉514458863
P粉514458863 2023-08-15 21:47:39
0
1
504
<p>我有一个名为"transaction"的表,保存了在某个支付系统上进行的交易。 我想要获得2023年7月的每日报告(例如),并运行了以下查询:</p> <pre class="brush:sql;toolbar:false;">SELECT DAY(CREATED), count(*) FROM `transaction` WHERE STATUS = 'approved' AND MONTH(CREATED) = 07 AND YEAR(CREATED) = 2023 GROUP BY DAY(CREATED) </pre> <p>结果跳过了一些天,例如对于一个月的第7天,我什么都没有得到,但是当我将查询改为:</p> <pre class="brush:sql;toolbar:false;">SELECT DAY(CREATED), count(*) FROM `transaction` WHERE STATUS = 'approved' AND MONTH(CREATED) = 07 AND YEAR(CREATED) = 2023 AND DAY(CREATED) = 7 GROUP BY DAY(CREATED); </pre> <p>(基本上在查询中添加了<code>DAY(CREATED) = 7</code>)</p> <p>它返回了第7天的计数。</p> <p>为什么会不一致? 在第一个查询中我该如何解决这个问题?</p> <p>提前谢谢 :)</p> <p>编辑: 表结构和数据示例:</p> <pre class="brush:sql;toolbar:false;">CREATE TABLE `transaction` ( `ID` int NOT NULL, `PLATFORM_ID` int NOT NULL, `ENTITY_ID` int NOT NULL, `RELATED_TRANSACTION_ID` int NOT NULL DEFAULT '0', `ORIGIN_ID` varchar(120) NOT NULL, `BANK` varchar(64) DEFAULT NULL, `AMOUNT` double NOT NULL, `CURRENCY` varchar(3) NOT NULL, `TYPE` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `CREATED` datetime NOT NULL, `CARD_NO` varchar(20) DEFAULT NULL, `CARD_BRAND` varchar(45) DEFAULT NULL, `CARD_EXPIRE_YEAR` int DEFAULT NULL, `CARD_EXPIRE_MONTH` varchar(45) DEFAULT NULL, `HOLDER_NAME` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, `EMAIL` varchar(256) DEFAULT NULL, `STATUS` enum('approved','declined','filtered','pending') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, `IS_3D` tinyint(1) DEFAULT NULL, `CREATED_AT` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `AMOUNTEUR` double DEFAULT NULL, `IS_FRAUD` tinyint(1) NOT NULL DEFAULT '0', `FRAUD_DATE` date DEFAULT NULL, `KYC_DATE` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; </pre> <p>示例插入:</p> <pre class="brush:sql;toolbar:false;">INSERT INTO `transaction` (`ID`, `PLATFORM_ID`, `ENTITY_ID`, `RELATED_TRANSACTION_ID`, `ORIGIN_ID`, `BANK`, `AMOUNT`, `CURRENCY`, `TYPE`, `CREATED`, `CARD_NO`, `CARD_BRAND`, `CARD_EXPIRE_YEAR`, `CARD_EXPIRE_MONTH`, `HOLDER_NAME`, `EMAIL`, `STATUS`, `IS_3D`, `CREATED_AT`, `AMOUNTEUR`, `IS_FRAUD`, `FRAUD_DATE`, `KYC_DATE`) VALUES (1, 1, 87, 0, '219114359', 'SOME BANK', 150, 'USD', 'DB', '2022-02-01 00:00:17', '000000XXXXXX0000', 'MasterCard', 2025, '06', 'NAME FAMILY', 'aaaaaaaaa@gmail.com', 'approved', 0, '2022-08-25 13:12:58', 150.7605, 0, NULL, NULL); </pre> <p>我省略了索引定义,因为我认为在这种情况下它不相关。</p>
P粉514458863
P粉514458863

全部回复(1)
P粉476883986

在MySQL控制台中运行后,我发现了问题,原因很简单,PhpMyAdmin系统只限制了查看25行...

这是我的错误!:O 非常感谢大家的时间和支持!

热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板