SELECT t1.depar_date, t1.return_date, t1.orig_air, t1.dest_air, t1.num_pass, count(t1.id) as count_of, 'TRUE' as Hit_Cache
FROM MY_TEST t1
WHERE t1.created_at BETWEEN t1.created_at AND dateadd(minute, 30, t1.created_at) --t1.created_at > dateadd(DAY, 1, t1.created_at)
GROUP BY t1.depar_date, t1.return_date, t1.orig_air, t1.dest_air, t1.num_pass
HAVING count(t1.id) > 1;
无法识别DATEADD
并抛出错误。
大
写 MINUTE 或以小写形式使用 mm:
SELECT t1.depar_date, t1.return_date, t1.orig_air, t1.dest_air, t1.num_pass, count(t1.id) as count_of, 'TRUE' as Hit_Cache
FROM MY_TEST t1
WHERE t1.created_at BETWEEN t1.created_at AND dateadd(MINUTE, 30, t1.created_at) --t1.created_at > dateadd(DAY, 1, t1.created_at)
GROUP BY t1.depar_date, t1.return_date, t1.orig_air, t1.dest_air, t1.num_pass
HAVING count(t1.id) > 1;
因为你的查询被堵住了DATEADD()
我猜你正在使用 MySQL。
在我看来,您想知道表中有多少其他记录位于每条记录的半小时内。
由于要将记录与记录进行比较,因此需要自联接操作。 具体来说,你想要这样的东西
FROM MY_TEST t1
JOIN MY_TEST t2 ON something.
此构造使您看起来像是在查询两个不同的表t1
和t2
,即使它们是相同的数据。
我想你的比较会像这样
FROM MY_TEST t1
JOIN MY_TEST t2
ON t2.created_at BETWEEN t1.created_at AND t1.created_at + INTERVAL 30 MINUTE
此连接操作的结果由表中与ON
条件匹配的行对的所有可能组合组成。 然后,您继续从这组行对中进行选择和汇总。
查询的其余部分看起来与您在问题中的内容非常相似。
SELECT t1.depar_date, t1.return_date, t1.orig_air,
t1.dest_air, t1.num_pass, count(t1.id) as count_of,
'TRUE' as Hit_Cache
FROM MY_TEST t1
JOIN MY_TEST t2
ON t2.created_at BETWEEN t1.created_at AND t1.created_at + INTERVAL 30 MINUTE
GROUP BY t1.depar_date, t1.return_date, t1.orig_air, t1.dest_air, t1.num_pass
HAVING count(t1.id) > 1;
也许我猜错了MySQL。在 SQL Server 中,您的 ON 子句将如下所示。
ON t2.created_at BETWEEN t1.created_at AND DATEADD(MINUTE, 30, t1.created_at)