mysql - Union ALL 查询在 VBA 中失败但在 SQL 中正常

Question

我尝试运行以下查询，但它在“unionselect”附近显示错误语法不正确有人可以帮忙吗？

strsql = "select distinct a.LOCID,locnum,locname,streetname,city,county,statecode,state,peril,SITEDEDAMT,SITELIMAMT,COMBINEDDEDAMT,COMBINEDLIMAMT from eqdet a inner join loccvg b on a.LOCID=b.locid inner join loc c on b.LOCID=c.LOCID where PERIL=1" & _
   "union" & _
  "select distinct a.LOCID,locnum,locname,streetname,city,county,statecode,state,peril,SITEDEDAMT,SITELIMAMT,COMBINEDDEDAMT,COMBINEDLIMAMT from hudet a  inner join loccvg b on a.LOCID=b.locid inner join loc c on b.LOCID=c.LOCID where peril=2" & _
  "union" & _
    "select distinct a.LOCID,locnum,locname,streetname,city,county,statecode,state,peril,SITEDEDAMT,SITELIMAMT,COMBINEDDEDAMT,COMBINEDLIMAMT from todet a  inner join loccvg b on a.LOCID=b.locid inner join loc c on b.LOCID=c.LOCID where peril=3" & _
    "union" & _
   "select a.LOCID,locnum,locname,streetname,city,county,statecode,state,peril,SITEDEDAMT,SITELIMAMT,COMBINEDDEDAMT,COMBINEDLIMAMT from fldet a  inner join loccvg b on a.LOCID=b.locid inner join loc c on b.LOCID=c.LOCID where peril=4" & _
   "union" & _
 "select distinct a.LOCID,locnum,locname,streetname,city,county,statecode,state,peril,SITEDEDAMT,SITELIMAMT,COMBINEDDEDAMT,COMBINEDLIMAMT from frdet a  inner join loccvg b on a.LOCID=b.locid inner join loc c on b.LOCID=c.LOCID where peril=5" & _
 "union" & _
 "select distinct a.LOCID,locnum,locname,streetname,city,county,statecode,state,peril,SITEDEDAMT,SITELIMAMT,COMBINEDDEDAMT,COMBINEDLIMAMT from trdet a  inner join loccvg b on a.LOCID=b.locid inner join loc c on b.LOCID=c.LOCID where peril=6"

Answer 1

查看错误消息，我怀疑您正在连接一个字符串来进行查询。 unionselect 应该是两个单词，用空格分隔。 (union select) 我认为在您的代码空间中缺少分隔关键字。

参见:

"...where PERIL=1" & _ "union" & _ "select distinct..."

会产生

...其中 PERIL=1unionselect distinct...

输出。正确的代码(注意并集周围的空格):

"...where PERIL=1" & _ " union " & _ "select distinct ..."