回答

收藏

Access 2007中的表达过于复杂

技术问答 技术问答 448 人阅读 | 0 人回复 | 2023-09-12

当我尝试通过ODBC接口进入Access在MySQL数据库中的Access中运行此查询时,出现“在查询表达式中表达式过于复杂”的错误。我想要做的基本事情是将语言的缩写名称翻译成与它们对应的完整的英语版本。我想知道是否可以通过某种方式“欺骗”访问以使子查询的表达式更小,或者是否有人对如何解决此问题有更好的了解。我曾考虑过制作一个临时表并对其进行连接,但是Access
* _) [8 h1 u" R# l, Q5 Q" eSQL不支持该表。
& R1 p9 n5 Z5 t1 N7 G* e就像FYI一样,查询工作正常,直到我添加了大而长的IFF链。我在三种语言的较小IFF链上测试了查询,这不是问题,因此问题肯定出自庞大的IFF链(深度为26)。另外,我也许可以删除一些选项(例如组合不同形式的中文或葡萄牙语)) b8 \4 W4 s2 {5 g) G! J
作为测试,在将SQL查询分解为14个IFF()语句后,我能够使它运行,但是与我要代表的26种语言相去甚远。
# k) W8 c$ o* V" _SELECT TOP 5 Count( * ) AS [Number of visits by language], IIf(login.lang="ar","Arabic",IIf(login.lang="bg","Bulgarian",IIf(login.lang="zh_CN","Chinese (Simplified Han)",IIf(login.lang="zh_TW","Chinese (Traditional Han)",IIf(login.lang="cs","Czech",IIf(login.lang="da","Danish",IIf(login.lang="de","German",IIf(login.lang="en_US","United States English",IIf(login.lang="en_GB","British English",IIf(login.lang="es","Spanish",IIf(login.lang="fr","French",IIf(login.lang="el","Greek",IIf(login.lang="it","Italian",IIf(login.lang="ko","Korean",IIf(login.lang="hu","Hungarian",IIf(login.lang="nl","Dutch",IIf(login.lang="pl",&quotolish",IIf(login.lang="pt_PT","European Portuguese",IIf(login.lang="pt_BR","Brazilian Portuguese",IIf(login.lang="ru","Russian",IIf(login.lang="sk","Slovak",IIf(login.lang="sl","Slovenian","IIf(login.lang="fi","Finnish",IIf(login.lang="sv","Swedish",IIf(login.lang="tr","Turkish","Unknown")))))))))))))))))))))))))) AS [Language]
8 k  J3 N1 C9 }) l3 w3 {& R. ?: TFROM login, reservations, reservation_users, schedules
" J* o, g7 x7 M4 f$ ?$ s. I/ iWHERE (reservations.start_date Between DATEDIFF('s','1970-01-01 00:00:00',[Starting Date in the Following Format YYYY/MM/DD]) And DATEDIFF('s','1970-01-01 00:00:00',[Ending Date in the Following Format YYYY/MM/DD])) And reservations.is_blackout=0 And reservation_users.memberid=login.memberid And reservation_users.resid=reservations.resid And reservation_users.invited=0 And reservations.scheduleid=schedules.scheduleid And scheduletitle=[Schedule Title]
& m$ L. o0 `8 ?+ c/ ]. T( ZGROUP BY login.lang
$ n" t( x. x0 `; p, c+ AORDER BY Count( * ) DESC;
$ ]5 I2 v: M% N@迈克尔·托德
1 b3 j1 i7 H0 e: ~我完全同意。语言列表应该是数据库中的一个表,而login.lang应该是该表中的FK。不幸的是,这不是数据库的编写方式,也不是真正的修改数据库。语言由数据库顶部运行的PHP放置在login.lang字段中。  |$ y" g7 d" e, R3 Q
               
: G" D% e3 h- B2 J6 j4 q解决方案:
分享到:
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则