TOP，SUM SQL语句

Question

我有一个名为RPT的表，其列如下：

1. CODENUMBER
2. AXSUBGROUP
3. ITEMSTRUCTURE
4. UNITSSOLD
5. DOLLARS

我需要向用户返回基于TOP 20 AXSUBGROUPS的UNITSSOLD以及UNITSSOLD和DOLLARS值。此外，我还需要返回属于这些前20位的ITEMSTRUCTURES (由代码编号加入)，以及它们的UNITSSOLD和DOLLARS。

我不知道该用哪种方式来查询这个问题，因为我需要按AXSUBGROUP和ITEMSTRUCTURE进行分组，但是ITEMSTRUCTURE通过CODENUMBER绑定到aXSUBGROUP。

我很感激你能提供的任何帮助。

谢谢。

编辑以更好地说明我的需求：

5,000

1. BAR - 10,000
   • BAR 2 oz - 5,000
   • BAR 1 oz -

​

5,000

• ACRYLIC

• 礼品盒- 8,000
• 松露盒-盒- 3,000

​

..。

下面是表中的示例数据(对不起-无法弄清楚如何格式化表.)

CodeNumber UnitsSold Dollars AxSubGroup ItemStructure
ABC123    |    9    | 500   | Bar      | 1 oz
ABC456    |    9    | 800   | Bar      | 2 oz
ABC789    |    9    | 500   | Bar      | 3 oz
DEF123    |    3    | 200   | Tin      | Round
DEF456    |    2    | 200   | Tin      | Rectangular
GHI123    |    1    | 200   | Truffle  | Luxe
GHI456    |    5    | 100   | Truffle  | Executive
GHI789    |    4    | 200   | Truffle  | Eco
JKL123    |    9    | 500   | Box      | 2 pc
JKL456    |    5    | 100   | Box      | 4 pc
JKL789    |    5    | 100   | Box      | 8 pc

..。如果有帮助的话，最初这是两个表- RPT (CodeNumber，UnitsSold，$)和FLEX (CodeNumber，AxSubGroup，ItemStructure)。

Answer 1

SELECT 
      RPT.CodeNumber
    , RPT.AxSubgroup
    , RPT.ItemStructure
    , RPT.UnitsSold
    , RPT.Dollars
    , grp.TotalUnitsSold
FROM 
      RPT
  JOIN
      ( SELECT TOP 20 
              AxSubgroup
            , SUM(UnitsSold) AS TotalUnitsSold
        FROM
              RPT 
        GROUP BY
              AxSubgroup
        ORDER BY
              TotalUnitsSold DESC
      ) AS grp
    ON 
      grp.AxSubgroup = RPT.AxSubgroup
ORDER BY
      RPT.AxSubgroup 

Answer 2

看来是ROW_NUMBER来救你了！

SELECT CODENUMBER
    , AXSUBGROUP
    , ITEMSTRUCTURE
    , UNITSSOLD
    , DOLLARS
FROM
(
    SELECT CODENUMBER
        , AXSUBGROUP
        , ITEMSTRUCTURE
        , UNITSSOLD
        , DOLLARS
        , ROW_NUMBER() OVER (PARTITION BY AXSUBGROUP ORDER BY UNITSSOLD DESC) AS r
    FROM RPT
) a
WHERE r <= 20

Answer 3

需要一个与示例源数据匹配的预期输出的更好示例。这个问题还有相当多的含糊不清之处。但这是另一次尝试：

使用包含4个"AxSubGroup“项的示例源数据("Bar”、"Tin“、”Tru之所以“、"Box")，此解决方案仅限于前3位AxSubGroups (而不是前20位)。

对于每个"UnitsSold“，" top”子查询和"AxSubGroup"，按降序排序，对它们进行编号，并获取前3。

 axsubgroup | totalsold | rownum
------------+-----------+--------
 Bar        |        27 |      1
 Box        |        19 |      2
 Truffle    |        10 |      3
 Tin        |         5 |      4
(4 rows)

然后将此集合连接回原始的RPT表，以获取每个顶级ax子组项的详细信息。

CREATE TABLE rpt(CodeNumber varchar(6), UnitsSold int, Dollars int, AxSubGroup varchar(32), ItemStructure varchar(32));

INSERT INTO rpt VALUES('ABC123', 9, 500, 'Bar', '1 oz');
INSERT INTO rpt VALUES('ABC456', 9, 800, 'Bar', '2 oz');
INSERT INTO rpt VALUES('ABC789', 9, 500, 'Bar', '3 oz');
INSERT INTO rpt VALUES('DEF123', 3, 200, 'Tin', 'Round');
INSERT INTO rpt VALUES('DEF456', 2, 200, 'Tin', 'Rectangular');
INSERT INTO rpt VALUES('GHI123', 1, 200, 'Truffle', 'Luxe');
INSERT INTO rpt VALUES('GHI456', 5, 100, 'Truffle', 'Executive');
INSERT INTO rpt VALUES('GHI789', 4, 200, 'Truffle', 'Eco');
INSERT INTO rpt VALUES('JKL123', 9, 500, 'Box', '2 pc');
INSERT INTO rpt VALUES('JKL456', 5, 100, 'Box', '4 pc');
INSERT INTO rpt VALUES('JKL789', 5, 100, 'Box', '8 pc');


WITH top AS (

     -- Top AxSubGroups based on UnitsSold with order number assigned, restricted to top 20
    SELECT AxSubGroup, totalSold, rowNum
      FROM (
             -- Top AxSubGroups based on UnitsSold with order number assigned
             SELECT AxSubGroup, totalSold
                   ,ROW_NUMBER() OVER (ORDER BY totalSold DESC) AS rowNum
               FROM ( 
                      -- Top AxSubGroups based on UnitsSold
                      -- (do you really want this 'UnitsSold' or rather on 'gross': 'UnitsSold' * 'Dollars'?)
                      SELECT AxSubGroup, SUM(UnitsSold) AS totalSold
                        FROM rpt r
                        GROUP BY AxSubGroup
                        ORDER by totalSold DESC
                    ) x
               ORDER BY totalSold DESC
           ) y
      WHERE rowNum <= 3
)

SELECT t.AxSubGroup, t.totalSold, r.ItemStructure, r.UnitsSold, r.Dollars
  FROM top t
      ,rpt r
  WHERE r.AxSubGroup = t.AxSubGroup;

 axsubgroup | totalsold | itemstructure | unitssold | dollars
------------+-----------+---------------+-----------+---------
 Bar        |        27 | 1 oz          |         9 |     500
 Bar        |        27 | 2 oz          |         9 |     800
 Bar        |        27 | 3 oz          |         9 |     500
 Truffle    |        10 | Luxe          |         1 |     200
 Truffle    |        10 | Executive     |         5 |     100
 Truffle    |        10 | Eco           |         4 |     200
 Box        |        19 | 2 pc          |         9 |     500
 Box        |        19 | 4 pc          |         5 |     100
 Box        |        19 | 8 pc          |         5 |     100
(9 rows)

结果的前两列是对组的每个成员重复的汇总/总计列，而最后三列是构成和的详细信息。