如何在MS Access 2007中嵌套这两个SQL查询？

时间：2022-09-19 23:51:13

I have a table called baskets with these columns:

我有一个名为baskets的表,其中包含以下列:

  • basket (name of the basket)

    • 篮子(篮子的名字)

  • colour (colour of the basket)

    • 颜色(篮子的颜色)

  • apples (the number of apples in the basket)

    • 苹果(篮子里的苹果数量)

  • bananas (the number of bananas in the basket)

    • 香蕉(篮子里的香蕉数量)

  • oranges (the number of oranges in the basket)

    • 橘子(篮子里的橘子数)

  • pears (the number of pears in the basket)

    • 梨(篮子里的梨子数量)

  • peaches (the number of peaches in the basket)

    • 桃子(篮子里的桃子数量)

With Query1, I determine the total number of fruit in each basket and I also include the colour of each basket:

使用Query1,我确定每个篮子中的水果总数,我还包括每个篮子的颜色:

SELECT basket, colour, apples+bananas+oranges+pears+peaches AS fruit
FROM baskets;

Query1 consists of three columns:

Query1由三列组成:

  • basket
  • colour
  • fruit (total number of fruit in the basket)

    • 水果(篮子里的水果总数)

With Query2, I determine the average number of fruits there are in all baskets of each colour by drawing the information from the result of Query1:

使用Query2,我通过从Query1的结果中提取信息来确定每种颜色的所有篮子中的平均水果数量:

SELECT DISTINCT
        candidate.colour,
        candidate.fruit
            (SELECT AVG(fruit)
                 FROM Query1 AS average
                 WHERE average.colour = candidate.colour) AS fruit
    FROM Query1 AS candidate;

Query2 consists of two columns:

Query2由两列组成:

  • colour
  • fruit

Is it possible to nest these queries so that I may obtain the result of Query2 with only one query?

是否可以嵌套这些查询,以便我可以只用一个查询获得Query2的结果?

Your help will be much appreciated. Thank you.

非常感谢您的帮助。谢谢。

2 个解决方案

#1

SELECT colour, AVG(apples+bananas+oranges+pears+peaches) AS fruit
FROM baskets
GROUP by colour;

#2

If you want the total fruit by colour of basket you would do something like this:

如果你想通过篮子颜色来获得总水果,你可以这样做:

SELECT colour, SUM(apples+bananas+oranges+pears+peaches) AS totalfruit
FROM baskets
GROUP By colour

#1

SELECT colour, AVG(apples+bananas+oranges+pears+peaches) AS fruit
FROM baskets
GROUP by colour;

#2

If you want the total fruit by colour of basket you would do something like this:

如果你想通过篮子颜色来获得总水果,你可以这样做:

SELECT colour, SUM(apples+bananas+oranges+pears+peaches) AS totalfruit
FROM baskets
GROUP By colour
标签：

相关文章