按多个分组列计算每个组的前N个聚合值

Question

原问题标题: 如何在子查询中使用一个查询的字段

我在Access中编写查询方面经验不是很丰富，遇到了一些麻烦。

我有几个表。1个产品表，1个市场表和1个将市场和产品结合起来的事实表。我需要编写一个查询，可以将它们连接在一起，并针对特定的市场字段（MF）的每个实例，返回特定事实字段（FF）的第n高值（每个市场/产品总和），以及该事实值所链接的特定产品字段（PF）。希望这讲得清楚。

无论如何，这是我所拥有的查询：

select market.MF2, product.PF10, sum(fact.FF3) as FF3
from mMarket market, mProduct product, mFact fact
where market.Item_ID = fact.Market_ID
and product.Item_ID = fact.Product_ID
and FF3 = 
(
  select min(FF3) from 
  (
    select TOP 2 FF3 from 
    (
      select market2.MF2, product2.PF10, sum(fact2.FF3) as FF3
      from mMarket market2, mProduct product2, mFact fact2
      where market2.Item_ID = fact2.Market_ID
      and product2.Item_ID = fact2.Product_ID
      and market2.MF2 = market.MF2
      group by market2.MF2, product2.PF10
      order by 3 DESC
    )
  )
)
group by market.MF2, product.PF10

TOP 2部分是指轻松指定n的位置。 我的问题是，当我在Access中运行它时，它会提示我输入market.MF2的值（我认为这是指子查询中的实例）。 我想代码会从主查询中为每一行抓取该值，但很明显它没有这样做。

Tables below:

mMarket

Item_ID     MF2
---------------
1           64
2           28
3           73

mProduct

Item_ID     PF5        PF10
----------------------------
1           2221       Category1
2           6487       Category3
3           73234      Category2
4           76223      Category1
5           99342      Category2

mFact

Market_ID        Product_ID       FF3
--------------------------------------
1                1                1000
1                2                1500
1                3                500
1                4                1000
2                1                1500
2                3                1000
2                5                1500
3                1                1000
3                2                500
3                5                2000

查询有什么问题？我看不到它。 提前致谢。

期望结果：

If n was 1

MF2        PF10         FF3
----------------------------
64         Category1    2000
28         Category2    2500
73         Category2    2000

If n was 2

MF2        PF10         FF3
----------------------------
64         Category3    1500
28         Category1    1500
73         Category1    1000

- Chris

---

主查询可以从子查询中提取值，但反过来不行...而且你可以有一个单独的查询返回market.MF2，并从主查询中调用它 :) 有没有可能向我们展示一下您的表模式和一些示例数据？ - bonCodigo

哦，好的。我不确定你所说的“单独查询位”是什么意思。 - Chris

根据你的数据，Chris预期的结果是什么？ - bonCodigo

+1 我注意到我忘记为你有趣的问题投票了。 :) - bonCodigo

Answer 1

请查看以下参考资料：

• SQLFIDDLE

按市场查询总和：

按市场查询结果总和：

MF2 ITEM_ID PRODUCT_ID  PF5     PF10                                        SUM(F.FF3)
28  2       5,1,3       99342   Category2,Category1,Category2               4000
64  1       3,4,1,2     73234   Category2,Category1,Category1,Category3     4000
73  3       1,2,5       2221    Category1,Category3,Category2               3500

按市场、类别查询总和：

select m.mf2, p.pf10, p.pf5, 
group_concat(f.product_id)as prods, 
m.item_id , sum(f.ff3) as sff
from mmarket m
left join mfact f
on m.item_id = f.market_id
inner join mproduct p
on f.product_id = p.item_id
group by m.mf2, p.pf10
order by sff desc 
;

按市场、类别汇总的结果：

MF2     PF10        PF5     PRODS   ITEM_ID     SFF
28      Category2   99342   5,3     2           2500
64      Category1   76223   4,1     1           2000
73      Category2   99342   5       3           2000
28      Category1   2221    1       2           1500
64      Category3   6487    2       1           1500
73      Category1   2221    1       3           1000
64      Category2   73234   3       1           500
73      Category3   6487    2       3           500

更新答案 根据OP的后续评论

查询：

select x.*
from (
select m.mf2, p.pf10, sum(f.ff3) as sff
from mmarket m
left join mfact f
on m.item_id = f.market_id
inner join mproduct p
on f.product_id = p.item_id
group by m.mf2, p.pf10
  order by sff desc ) as x
limit 1
;

结果：

MF2     PF10          MSFF
28      Category2     2500

根据OP的预期结果 - 更新以符合MS ACCESS SQL

有许多方法可以在MYSQL中实现这一点。但是我想给出MS ACCESS的答案。

我建议您将“按市场，类别汇总”的上述结果保存到临时表或MS ACCESS查询中。然后在最终查询中使用该查询。

• SQLFIDDLE

按市场和类别查询前1个的查询：

-- success final :) by Top 1st
select x.mf2, x.pf10, x.sff
from 
(select m.mf2, p.pf10, p.pf5, 
group_concat(f.product_id)as prods, 
m.item_id , sum(f.ff3) as sff
from mmarket m
left join mfact f
on m.item_id = f.market_id
inner join mproduct p
on f.product_id = p.item_id
group by m.mf2, p.pf10
order by sff desc) as x

where 

(select count(*)
 from
     (select m.mf2, p.pf10, p.pf5, 
      group_concat(f.product_id)as prods, 
      m.item_id , sum(f.ff3) as sff
      from mmarket m
      left join mfact f
      on m.item_id = f.market_id
      inner join mproduct p
      on f.product_id = p.item_id
      group by m.mf2, p.pf10
      order by sff desc) as y

where y.sff >= x.sff
and y.mf2 = x.mf2) =1 //-- Top 3rd, 2nd, 1st
order by x.sff desc
;

按类别排名的第一大市场结果：

MF2     PF10    SFF
28  Category2   2500
64  Category1   2000
73  Category2   2000

按类别排名的第二大市场结果：

MF2     PF10    SFF
28  Category1   1500
64  Category3   1500
73  Category1   1000

按类别排名的前三大市场结果：

MF2     PF10    SFF
64  Category2   500
73  Category3   500

- bonCodigo

---

啊，是的，抱歉我的错误。FF3应该按市场类别求和。然后对于每个市场，获取第n个最高事实总和的类别。所以当n为1时，它将获取每个市场具有最高FF3值的类别。 - Chris

你能否请添加一个手动记录你期望的结果，这会非常有帮助。你可以通过将其附加在你的问题末尾来更新你的问题。例如：期望的结果。 - bonCodigo

抱歉我不在，忘记这里的sqlfiddle引用还没有更新:( 请看一下并告诉我它是否对你有效。我尽量保持纯sql，就像你在MS ACCESS中使用它一样。否则，在MYSQL中使用over partition是关键 :) - bonCodigo

谢谢，但那个方法并没有帮助到我。我需要每个市场仅有1个记录，该记录包含该市场中具有最高事实值的类别，并且我不能硬编码有多少不同的市场。而且这个查询不允许指定您想要每个市场的第3个或第4个最高值（例如）。 - Chris

你好 @Chris，我想给你一个来自 MS ACCESS SQL 的答案。很抱歉回复晚了：我不是全职在 StackOverFlow 上；希望这可以帮到你。 - bonCodigo

Answer 2

好的，经过深思熟虑和尝试，我找到了一种获取正确结果的方法。唯一的问题是需要很长时间。我想到了一种为每个市场设置排名的方法，其中排名=1表示最大事实值，2表示第二高等等。

select StoreCode, Category, Sales, Ranking from 
(
    select main.MF2 as StoreCode, main.PF10 as Category, main.sFF as Sales, 
    (
        select count(*) from
        (
            select market.MF2, product.PF10, sum(fact.FF3) as sFF
            from mMarket market, mProduct product, mFact fact
            where market.Item_ID = fact.Market_ID
            and product.Item_ID = fact.Product_ID
            group by market.MF2, product.PF10
        ) as main2
        where main.MF2 = main2.MF2
        and main2.sFF >= main.sFF
    ) as ranking
    from
    (
        select market.MF2, product.PF10, sum(fact.FF3) as sFF
        from mMarket market, mProduct product, mFact fact
        where market.Item_ID = fact.Market_ID
        and product.Item_ID = fact.Product_ID
        group by market.MF2, product.PF10
    ) as main
}
where ranking = 1
order by 1,2

我知道我应该先将最内层的查询写入临时表，然后再运行这个查询，因为需要完全相同的查询两次。 除此之外，如果有人能想到更好的方法来完成这个任务或者加速查询，那就太好了 :)

感谢您的帮助，bonCodigo :)

- Chris

---

连接比使用where子句更有效率...为什么不将内部查询放到另一个查询中并调用它呢？ - bonCodigo

那么，克里斯最终怎么样了？ :) 最小化嵌套选择的数量，以及在查询中不需要的列，可以优化性能。 - bonCodigo