由于“不同”原因导致访问查询备忘录字段被截断
我在运行查询时遇到问题,既没有截断 NotesTbl 中的注释字段,也没有返回重复的条目。
对于 AccessTbl,UID 不是唯一的。 当我省略“不同”注释时,将多次返回,因为我在非不同条件下加入 AccessTbl。 当我使用 distict 时,注释字段会被截断,因为它是备注字段。
这是我的查询:
SELECT DISTINCT NotesTbl.pin, NotesTbl.noteid, NotesTbl.note, NotesTbl.date,
AccessTbl.affiliation, AccessTbl.name
FROM NotesTbl
LEFT JOIN AccessTbl
ON NotesTbl.UID = AccessTbl.UID
WHERE PIN = #pin#
AND UID = '#uid#'
ORDER BY NotesTbl.DATE DESC
I am having problems running a query without either truncating the note field in NotesTbl or returning repeated entries.
UID is not unique for AccessTbl. When I leave out "distinct" notes will return multiple times because I am joining with AccessTbl on a non-distinct condition. When I use distict, the note field is trunctated because it is a memo field.
Here is my query:
SELECT DISTINCT NotesTbl.pin, NotesTbl.noteid, NotesTbl.note, NotesTbl.date,
AccessTbl.affiliation, AccessTbl.name
FROM NotesTbl
LEFT JOIN AccessTbl
ON NotesTbl.UID = AccessTbl.UID
WHERE PIN = #pin#
AND UID = '#uid#'
ORDER BY NotesTbl.DATE DESC
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
Access 数据库引擎通常仅使用前 255 个字符来确定文本(“字符串”)数据的唯一性,这就是
DISTINCT
和GROUP BY
将被截断的原因。这个建议有点啰嗦,但它确实有效:将
MEMO
分成 255 个字符的块,在这些块上使用DISTINCT
,然后再次将它们连接在一起,例如( Access 数据库引擎 ANSI-92 查询模式语法(即子查询的括号):已发布评论:
嗯,根据我的测试,这根本不起作用。 快速重现:
在 SQL-92 查询模式下使用 Access2007 .accdb ACE 引擎查询对象的 SQL 视图进行测试,查询返回单行(不正确),其值已被截断为 255 个字符(不正确)。
早期的“分块”查询返回两行(正确),没有截断(正确)。
The Access database engine normally determines uniqueness of text ('String') data using only the first 255 characters and that is why
DISTINCT
andGROUP BY
will truncate.This suggestion is a bit long winded but it does work: split the
MEMO
into chunks of 255 characters, do useDISTINCT
on the chunks, then concatenate them back together again e.g. (Access database engine ANSI-92 Query Mode syntax i.e. parens for subqueries):A comment has been posted:
Well, in my testing this doesn't work at all. Quick repro:
Tested using the SQL view of a Access2007 .accdb ACE engine Query object in SQL-92 Query Mode, the query returns a single row (incorrect) whose value has been truncated at 255 characters (incorrect).
The earlier 'chunking' query returns two rows (correct) without truncation (correct).
我找到了一个似乎有效的解决方案。 我使用“分组依据”来强制 PIN 和 NoteID 的区别。 我尝试使用 First() 来从清晰度比较中排除注释以避免截断。
I found a solution that seems to work. I used a "group by" to force distinctness on the PIN and NoteID. I tried to exclude the note from distinctness comparissons by using First() to avoid truncation.
编辑:--删除了第一个建议--
...
另一种方法是将您的请求分解为两个查询:一个查询优化 AccessTbl,以便 UID 在查询中是唯一的,另一个查询将 NotesTbl 联接到您刚刚创建的 qryAccessTblUnique 查询。
Edit: --Removed first suggestion--
...
Another method would be to break your request into two queries: One that refines AccessTbl so that UID is unique within the query, and another that joins NotesTbl to the qryAccessTblUnique query you just created.
您还可以过滤 CF 端的查询对象以删除(或忽略)已显示的响应。 效率不高,但如果您使用 Acess,我猜流量大并不是一个大问题。
类似这样的:
对于大量的返回 n,这在 O(n^2) 下会很丑陋,但对于小 n,它应该给你你想要的。
You could also filter the query object on the CF end to remove (or ignore) responses which have already been displayed. Not efficient, but if you are using Acess, I'm guessing heavy traffic is not a huge issue.
Something like:
For large numbers of returns n, this is going to be ugly at O(n^2), but for small n, it should give you what you want.