我有这样的SQL:
SELECT
Mid(Note,
InStr(Note, "device.")-
(
InStr(Note, "device.")-
InStr(Note, "pressure and")
)
+13,
(InStr(Note, "device.") - InStr(Note, "pressure and")) - 14
)
AS [Device],
Count([Device]),
Date_Field & " " & Time_Field AS [DateTime],
EnteredBy
FROM MyLog
WHERE Note LIKE "*removed and*"
GROUP BY [Device]
ORDER BY Date_Field DESC
;
我想GROUP BY
该自定义字段[Device]
并计算每个设备的数量。但是上面的代码给出了错误"...不包括指定的表达式...作为聚合函数的一部分",用于"...作为 [设备]"部分。
我怎样才能做到这一点?
现在的数据如下所示:
Record1 12/05/12 03:02:12 User2
Record1 12/02/12 01:02:12 User1
Record1 12/01/12 02:02:12 User2
Record2 12/06/12 03:02:12 User2
Record2 12/07/12 03:02:12 User3
但我希望它看起来像:
Record1 3
Record2 2
这是有效的旧 SQL(不聚合):
SELECT Mid(Note,
InStr(Note, "device.")-
(
InStr(Note, "device.")-
InStr(Note, "pressure and")
)
+13,
(InStr(Note, "device.") - InStr(Note, "pressure and")) - 14
) AS Device, Date_Field & " " & Time_Field AS [DateTime], EnteredBy
FROM MyLog
WHERE Note LIKE "*removed and*"
ORDER BY Date_Field DESC;
不能在 MS Access 中的 GROUP BY 中引用别名设备,但可以创建子查询并从中引用别名。
这为我运行:
SELECT EnteredBy, Device, Count(Device) As CountDev FROM (
SELECT
Mid(Note,InStr(Note,"device.")-
(InStr(Note,"device.")-
InStr(Note,"pressure and"))+13,
(InStr(Note,"device.")-InStr(Note,"pressure and"))-14) AS Device,
MyLog.EnteredBy
FROM MyLog
WHERE MyLog.[Note] Like "*removed and*")
GROUP BY EnteredBy, Device
您收到该错误,因为设备不是列。 也许您可以使用子查询,尽管我承认我已经有一段时间没有使用 Access 了。
尝试这样的事情:
SELECT Count([Device]), Device, [DateTime], EnteredBy
FROM (
SELECT
Mid(Note,
InStr(Note, "device.")-
(
InStr(Note, "device.")-
InStr(Note, "pressure and")
)
+13,
(InStr(Note, "device.") - InStr(Note, "pressure and")) - 14
)
AS [Device],
Date_Field & " " & Time_Field AS [DateTime],
EnteredBy
FROM MyLog
WHERE Note LIKE "*removed and*" ) t
GROUP BY Device, [DateTime], EnteredBy
ORDER BY Date_Field DESC
;
祝你好运。
与此类似的东西 - 基本上将复杂部分嵌套到子查询中。
select dev, count(dev) from
(
SELECT
Mid(Note,
InStr(Note, "device.")-
(
InStr(Note, "device.")-
InStr(Note, "pressure and")
)
+13,
(InStr(Note, "device.") - InStr(Note, "pressure and")) - 14
)
AS dev,
Date_Field & " " & Time_Field AS DateTime,
EnteredBy
FROM MyLog
WHERE Note LIKE "*removed and*"
)
GROUP BY dev
ORDER BY DateTime DESC