我有以下工作查询:http : //sqlfiddle.com/#!6/6e124/12
SELECT date, time, custom_id, Number
FROM traffic t
WHERE CONCAT(date, ' ', time) BETWEEN '01/06/2014 00:00' AND '01/10/2014 23:00'
AND Number =
(SELECT MAX(CAST(Number as smallint)) FROM traffic WHERE t.date = date and t.custom_id = custom_id)
ORDER BY date, CAST(Number as smallint) DESC, custom_id
但出于我的需要,我将其更改为:
SELECT [Trunk group],
Start_Date, Start_Time,
[Inc Seizure Traffic (TG)(Erl)]
FROM [tbl_TG_Nte-Pac_pHrs] t
WHERE CONCAT(Start_Date, ' ', Start_Time) BETWEEN '01/06/2014 00:00' AND '01/10/2014 23:00'
AND [Inc Seizure Traffic (TG)(Erl)] =
(
SELECT MAX(CAST([Inc Seizure Traffic (TG)(Erl)] as smallint))
FROM [tbl_TG_Nte-Pac_pHrs]
WHERE t.Start_Date = Start_Date
and t.[Trunk group] = [Trunk group])
ORDER BY Start_Date,
CAST([Inc Seizure Traffic (TG)(Erl)] as smallint) DESC,
[Trunk group]
Microsoft Access由于某些原因给我此行的syntaxis错误:
(SELECT MAX(CAST([Inc Seizure Traffic (TG)(Erl)] as smallint)) FROM [tbl_TG_Nte-Pac_pHrs] WHERE t.Start_Date = Start_Date and t.[Trunk group] = [Trunk group])
有什么事吗
您的WHERE
子句中的第一个条件有两个问题:
CONCAT(Start_Date, ' ', Start_Time) BETWEEN '01/06/2014 00:00' AND '01/10/2014 23:00'
第一个问题是Access SQL无法识别该CONCAT()
功能。您可以改用&
串联运算符。
Start_Date & ' ' & Start_Time
第二个问题是,BETWEEN
比较将日期评估为字符串。这个条件为真:
'01/07/1999' BETWEEN '01/06/2014 00:00' AND '01/10/2014 23:00'
如果要将这些字符串作为日期进行比较,可以执行以下操作:
CDate(Start_Date, ' ', Start_Time) BETWEEN #01/06/2014# AND #01/10/2014 23:00#
由于CAST()
Access SQL也不支持,CInt()
因此建议使用@mvaraujo代替。其他可能性是Val()
或CLng()
。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句