时间戳

首页 > 解决方案 > 为什么我的查询没有给出正确的计数和最大值？

sql - 为什么我的查询没有给出正确的计数和最大值？

问题描述

发送日期、打开总数、上次打开日期（如果有打开）、总点击次数、上次点击日期（如果有打开）

create table TBLA(Email text, SendID text, SendDate date, OpenDate date, ClickDate date);

insert into TBLA values("aaa@gmail.com", "ABCD1234", "8/1/2019",'1/8/2019',"8/1/2019");
insert into TBLA values("aaa@gmail.com", "ABCD1234", "8/1/2019",'2/8/2019',"");
insert into TBLA values("aaa@gmail.com", "ABCD1234", "8/1/2019",'3/8/2019',"8/3/2019");
insert into TBLA values("bbb@gmail.com", "ABCD1234", "8/1/2019",'1/8/2019',"");
insert into TBLA values("bbb@gmail.com", "ABCD1234", "8/1/2019",'2019/8/15',"8/15/2019");
insert into TBLA values("ccc@gmail.com", "ABCD1234", "8/1/2019","","");
insert into TBLA values("ddd@gmail.com", "ABCD1234", "8/1/2019","","");
insert into TBLA values("aaa@gmail.com", "xyz123", "8/2/2019","8/2/2019","8/2/2019");
insert into TBLA values("aaa@gmail.com", "xyz123", "8/2/2019","8/15/2019","");
insert into TBLA values("bbb@gmail.com", "xyz123", "8/2/2019","","");
insert into TBLA values("ccc@gmail.com", "xyz123", "8/2/2019","8/5/2019","8/5/2019");


select SendID," ", SendDate," ",count(OpenDate), " ", max(OpenDate), " ", count(ClickDate), " ", max(ClickDate)
from TBLA 
where SendID = "ABCD1234" AND (OpenDate<>"" AND ClickDate<>"");

count(OpenDate) 必须为 5，max(OpenDate) 必须为 8/15/2019。计数（点击日期）必须为 3。

标签： sqldatecountmax

解决方案

正如@nagarwal 指出的那样，存在数据类型问题。日期字段被声明为日期，但接收字符串作为输入。由于您是手动输入数据，我认为只需使用此“YYYY-MM-DD”语法作为日期即可。

推荐阅读