选择查询，选择一条选择语句 [英] A select query selecting a select statement

问题描述

我什至不知道我是否以正确的方式进行此查询. Sandwiches表有大约7个字段，其中两个是组合框(Type和Bread).

I don't even know if I am doing this query the right way. There is a Sandwiches table that has some 7 fields and 2 of them are comboboxes (Type and Bread).

因此，我进行了一个查询，将所有组合框的值组合到一个查询中，如下所示:

So I made a query that combines all of the comboboxes values into one query, like this:

SELECT TypesAndBreads.TBName, TypesAndBreads.Type
FROM (SELECT [Sandwiches Types].[Sandwich Type] As TBName, "Sandwich Type" As Type
    FROM [Sandwiches Types]
UNION ALL
    SELECT Breads.Bread As TBName, "Bread" As Type 
    FROM Breads)  AS TypesAndBreads;

我现在得到表格的固定值，我想计算每个TypesAndBreads.TBName下的所有三明治.我有这个，只是为了确保它适用于所有三明治:

I get the flat values of the tables now I want to count all the sandwiches under each TypesAndBreads.TBName. I have this, just to make sure it works with all the Sandwiches:

SELECT TypesAndBread.Type, TypesAndBread.TBName,
       (SELECT Count(Sandwiches.[SandwichID]) As SandwichCount
        FROM Sandwiches) As SandwichCount
FROM TypesAndBread;

但是我想在子查询中引用当前的Type和TBName.像这样:

But I want to reference the current Type and TBName inside the subquery. Something like this:

SELECT TypesAndBread.Type, TypesAndBread.TBName,
       (SELECT Count(Sandwiches.[SandwichID]) As SandwichCount
        FROM Sandwiches
        WHERE Sandwiches.[TypesAndBread.Type] = Sandwiches.[TypesAndBread.TBName]) As SandwichCount
FROM TypesAndBread;

但是，这当然行不通.我没想到会，只是想尝试一下.当他们打开此查询将基于的报表时，我正在考虑使用VBA构造查询.

But of course this doesn't work. I didn't think it will, just thought of giving it a try. I was thinking of maybe constructing the query with VBA when they open the Report that this query is going to be based of.

所以我想我的问题是:是否可以引用子查询中的当前选定字段?还是有其他方法可以解决这个问题?

So I guess my question is: Is there a way to reference the current selected fields in a subquery? Or is there a different way to approach this?

感谢您的帮助

我的表结构是这样的:

My table structure is like this:

Sandwiches的字段

| SandwichID | Name | Date Added | Chef | Sandwich Type | Bread | Reviewed By |

其中Sandwich Type和Bread是这些表的查找字段:

where Sandwich Type and Bread are Lookup fields for these tables:

Sandwiches Types的字段

| Sandwich Type |

Breads的字段

| Bread |

TypesAndBreads查询结合了Sandwiches Types和Breads表，但是这样做的原因是，我可以获得具有该Type或面包的所有三明治的计数.这样的结果:

The TypesAndBreads query combined the Sandwiches Types and Breads tables, but the reason for that is so that I can get the count of all the sandwiches that have that Type or bread. A result like this:

+=============================================+
|      Type     |    TBName   | SandwichCount |
+=============================================+
| Sandwich Type | Turkey Club |            10 |
| Bread         | Italian     |             5 |
| Bread         | Garlic      |             8 |
+---------------------------------------------+

示例结果的第一行基本上说，记录中有10个三明治，三明治类型"字段等于土耳其俱乐部".

the example result's first row basicly says there are 10 sandwiches in record with the Sandwich Type field equal to Turkey Club.

我希望能更好地解释它.

I hope that explains it better.

推荐答案

不确定Access是否支持它，但是在大多数引擎(包括SQL Server)中，这被称为相关子查询，并且运行良好:

Not sure if Access supports it, but in most engines (including SQL Server) this is called a correlated subquery and works fine:

SELECT  TypesAndBread.Type, TypesAndBread.TBName,
        (
        SELECT  Count(Sandwiches.[SandwichID]) As SandwichCount
        FROM    Sandwiches
        WHERE   (Type = 'Sandwich Type' AND Sandwiches.Type = TypesAndBread.TBName)
                OR (Type = 'Bread' AND Sandwiches.Bread = TypesAndBread.TBName)
        ) As SandwichCount
FROM    TypesAndBread

可以通过索引Type和Bread并将子查询分布在UNION上来提高效率:

This can be made more efficient by indexing Type and Bread and distributing the subqueries over the UNION:

SELECT  [Sandwiches Types].[Sandwich Type] As TBName, "Sandwich Type" As Type,
        (
        SELECT  COUNT(*) As SandwichCount
        FROM    Sandwiches
        WHERE   Sandwiches.Type = [Sandwiches Types].[Sandwich Type]
        )
FROM    [Sandwiches Types]
UNION ALL
SELECT  [Breads].[Bread] As TBName, "Bread" As Type,
        (
        SELECT  COUNT(*) As SandwichCount
        FROM    Sandwiches
        WHERE   Sandwiches.Bread = [Breads].[Bread]
        )
FROM    [Breads]

这篇关于选择查询，选择一条选择语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！