|
我正在使用northwind数据库通过创建一些或多或少复杂的查询来刷新我的SQL技能。不幸的是,我找不到最后一个用例的解决方案:“获得1997年每个类别的五个最大订单的总和。”# f- S/ h' _5 r( w9 T J
涉及的表是:( z c8 c1 a9 [% Z: s( O9 n
Orders(OrderId, OrderDate)% O6 ~" J+ f, J
Order Details(OrderId, ProductId, Quantity, UnitPrice)0 G, W, F8 G5 ^2 X5 I" J' A
Products(ProductId, CategoryId)1 X$ Z5 C9 T. C
Categories(CategoryId, CategoryName)
0 O1 F. _# k) E- ^' J: z, B我尝试了以下查询' u; L3 z& E5 Z& Q9 o; C3 M/ Q
SELECT c.CategoryName, SUM(
) F7 t. m. N- w% B (SELECT TOP 5 od2.UnitPrice*od2.Quantity : @; a) ^' V0 Q; {8 y
FROM [Order Details] od2, Products p2 Z1 [. n' ^3 ^& a- b
WHERE od2.ProductID = p2.ProductID& a% v6 E9 |& r+ L# X6 A( E# C
AND c.CategoryID = p2.CategoryID
0 F( z$ b5 F3 z9 [, I w ORDER BY 1 DESC))
" e# }: o" @3 D5 A- F& j2 ZFROM [Order Details] od, Products p, Categories c, Orders o
- _: p8 u, _, e! ]. a7 ]6 a+ zWHERE od.ProductID = p. ProductID
4 I6 g; A% H! r: |AND p.CategoryID = c.CategoryID) S! z s9 h" H2 d b( h
AND od.OrderID = o.OrderID
+ b: a3 ^4 u4 Z1 X% R5 L& oAND YEAR(o.OrderDate) = 1997
; c; K0 ~+ L7 I7 E2 A8 B5 VGROUP BY c.CategoryName
. [5 `1 X4 j, Q9 f$ m+ [好吧……原来,子查询在聚合函数中是不允许的。我已经阅读了有关此问题的其他文章,但是找不到针对我的特定用例的解决方案。希望你能帮我…
( w1 N5 b x7 a k3 y5 j , \9 _3 i$ |) }4 z! H
解决方案:
8 N" ] A) B+ {9 v 5 d9 i1 {0 D4 T0 _, U2 q
6 J n4 a; g; L$ m
/ W& L0 S$ r; r( r7 R 聚合函数中通常不允许使用子查询。相反,移动骨料 内 的子查询。在这种情况下,由于top 5:) E: u' e/ f5 u7 F
SELECT c.CategoryName,* L9 ]" D, x+ [( l6 [0 o' K
(select sum(val)
8 Q' W9 T3 V! G$ D' b from (SELECT TOP 5 od2.UnitPrice*od2.Quantity as val) S% b" j- [6 d6 M% U. f
FROM [Order Details] od2, Products p2
/ T' B: r/ T/ K9 r5 V3 ~+ |4 b WHERE od2.ProductID = p2.ProductID
: d$ x% M6 E. x8 m/ \) }6 f; ~+ y AND c.CategoryID = p2.CategoryID
8 V# O" c- b9 j/ e) I ORDER BY 1 DESC9 Y+ S6 y$ }" B! o% }4 ]
) t
% ]+ N, C- i, D1 K C: f7 r' k ) ^3 ^6 F( \; t5 z1 l- E8 V
FROM [Order Details] od, Products p, Categories c, Orders o 4 p6 k% Y7 `4 Y5 X7 }. d3 Q
WHERE od.ProductID = p. ProductID
% ~* y2 ?( Z; F" M; `. RAND p.CategoryID = c.CategoryID
, ~& m0 k! p; v$ ]AND od.OrderID = o.OrderID
. M3 [- Q' w* M! e+ qAND YEAR(o.OrderDate) = 1997
2 X5 M" }- g) I4 L7 ?GROUP BY c.CategoryName, c.CategoryId |
|