MS Access(Jet/ACE)中的无表UNION查询 [英] Table-less UNION query in MS Access (Jet/ACE)

问题描述

这可以按预期工作:

SELECT "Mike" AS FName

此操作失败，并显示错误消息查询输入必须至少包含一个表或查询":

This fails with the error "Query input must contain at least one table or query":

SELECT "Mike" AS FName
UNION ALL
SELECT "John" AS FName

这仅仅是Jet/ACE数据库引擎的一个怪癖/局限性吗?还是我遗漏了一些东西?

Is this just a quirk/limitation of the Jet/ACE database engine or am I missing something?

推荐答案

您没有忽略任何内容. Access的数据库引擎将允许没有FROM数据源的单行SELECT.但是，如果要UNION或UNION ALL多行，即使没有引用该数据源中的任何字段，也必须包含FROM....

You didn't overlook anything. Access' database engine will allow a single row SELECT without a FROM data source. But if you want to UNION or UNION ALL multiple rows, you must include a FROM ... even if you're not referencing any field from that data source.

我创建了一个只有一行的表，并添加了一个检查约束以确保它始终只有一行.

I created a table with one row and added a check constraint to guarantee it will always have one and only one row.

Public Sub CreateDualTable()
    Dim strSql As String
    strSql = "CREATE TABLE Dual (id COUNTER CONSTRAINT pkey PRIMARY KEY);"
    Debug.Print strSql
    CurrentProject.Connection.Execute strSql
    strSql = "INSERT INTO Dual (id) VALUES (1);"
    Debug.Print strSql
    CurrentProject.Connection.Execute strSql

    strSql = "ALTER TABLE Dual" & vbNewLine & _
        vbTab & "ADD CONSTRAINT there_can_be_only_one" & vbNewLine & _
        vbTab & "CHECK (" & vbNewLine & _
        vbTab & vbTab & "(SELECT Count(*) FROM Dual) = 1" & vbNewLine & _
        vbTab & vbTab & ");"
    Debug.Print strSql
    CurrentProject.Connection.Execute strSql
End Sub

该Dual表对于诸如此类的查询很有用:

That Dual table is useful for queries such as this:

SELECT "foo" AS my_text
FROM Dual
UNION ALL
SELECT "bar"
FROM Dual;

我看到的另一种方法是将SELECT语句与TOP 1或WHERE子句一起使用，以将结果集限制为单行.

Another approach I've seen is to use a SELECT statement with TOP 1 or a WHERE clause which restricts the result set to a single row.

注意检查约束是在Jet 4中添加的，仅适用于从ADO执行的语句. CurrentProject.Connection.Execute strSql之所以有效，是因为CurrentProject.Connection是ADO对象.如果尝试使用DAO执行同一条语句(即CurrentDb.Execute或从Access查询设计器执​​行)，则将出现语法错误，因为DAO无法创建检查约束.

Note check constraints were added with Jet 4 and are only available for statements executed from ADO. CurrentProject.Connection.Execute strSql works because CurrentProject.Connection is an ADO object. If you try to execute the same statement with DAO (ie CurrentDb.Execute or from the Access query designer), you will get a syntax error because DAO can't create check constraints.

这篇关于MS Access(Jet/ACE)中的无表UNION查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！