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;

我见过的另一种方法是使用带有 TOP 1 或 WHERE 子句的 SELECT 语句将结果集限制为单行.

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屋！