我什至不知道我是否以正确的方式进行此查询。
有一个Sandwiches表,其中包含大约7个字段,其中两个是组合框(TypeBread)。

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

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下的所有三明治。我有这个,只是为了确保它适用于所有三明治:

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


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

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构造查询。

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

谢谢您的帮助

编辑:
我的表结构是这样的:

Sandwiches的字段

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


其中Sandwich TypeBread是这些表的查找字段:

Sandwiches Types的字段

| Sandwich Type |


Breads的字段

| Bread |


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

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


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

我希望可以更好地解释它。

最佳答案

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

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


通过索引TypeBread并将子查询分布在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]

关于sql - 选择查询,选择一条选择语句,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/2043845/

10-09 05:49
查看更多