自引用表中按主要类别对 SQL 进行分组

Question

我需要获取按主要类别和Seller分组的总销售额列表。请注意，主要类别可以有销售（这是我目前能想到的最好的例子）。

源表

+--------------------------------------+
|ID   |Name        |Seller|Qty|ParentID|
+--------------------------------------+
|10   |Egg         |John  |5  |NULL    |
|10   |Egg         |Anna  |2  |NULL    |
|10-01|Egg - Small |John  |3  |10      |
|10-01|Egg - Small |Anna  |4  |10      |
|10-02|Egg - Medium|John  |2  |10      |
|10-02|Egg - Medium|Bob   |11 |10      |
|10-03|Egg - Large |Anna  |7  |10      |
+--------------------------------------+

期望的输出

+------------------+
|ID|Name|Seller|Qty|
+------------------+
|10|Egg |John  |10 | <- SUM of all sales John has made for any type of egg
|10|Egg |Anna  |13 |
|10|Egg |Bob   |11 |
+------------------+

我正在接近这个查询，但如果有人没有在主要类别上进行销售，那么当我使用 MIN(Name) 时，他们会得到错误的Name。

当前查询

SELECT 
    SUBSTRING(t1.ID, 1, 2) AS 'ID',
    MIN(t1.Name) AS 'Name', 
    t1.Seller, 
    SUM(t1.Qty) AS 'Qty'
FROM EggTest t1
GROUP BY 
    SUBSTRING(t1.ID, 1, 2),
    t1.Seller

电流输出

+--------------------------+
|ID|Name        |Seller|Qty|
+--------------------------+
|10|Egg         |Anna  |13 |
|10|Egg - Medium|Bob   |11 | <- Bob has not made sales on the main category
|10|Egg         |John  |10 |
+--------------------------+

编辑：看到多个答案已经表明SUBSTRING(Name, 1, 3)它对我不起作用。 Name并不总是以"蛋"开头。

更新：

现在尝试此查询：

WITH report AS(
  SELECT 
    ID = CASE WHEN s.ParentID IS NOT NULL THEN s.ParentID ELSE s.ID END,
    Name = CASE WHEN s.ParentID IS NOT NULL THEN p.Name ELSE s.Name END,
    s.Seller,
    s.Qty
  FROM EggTest s
  LEFT JOIN EggTest p ON p.ID = s.ParentID
)
SELECT ID, Name, Seller, SUM(Qty) AS 'Total'
FROM report
GROUP BY ID, Name, Seller;

但我得到了这个奇怪的结果：

+--------------------+
|ID|Name|Seller|Total|
+--------------------+
|10|Egg |Anna  |24   | <- Wrong (Should be 13)
|10|Egg |Bob   |22   | <- Wrong (Should be 11)
|10|Egg |John  |15   | <- Correct(!!)
+--------------------+

在report表中，我得到了一些重复项：

+------------------+
|ID|Name|Seller|Qty|
+------------------+
|10|Egg |John  |5  |
|10|Egg |Anna  |2  |
|10|Egg |John  |3  |
|10|Egg |John  |3  |
|10|Egg |Anna  |4  |
|10|Egg |Anna  |4  |
|10|Egg |John  |2  |
|10|Egg |John  |2  |
|10|Egg |Anna  |7  |
|10|Egg |Anna  |7  |
|10|Egg |Bob   |11 |
|10|Egg |Bob   |11 |
+------------------+

Answer 1

我将源表名称视为 [销售]

您可以使用以下内容

with report as(
   select ID = case when s.ParentID is not null then s.ParentID else s.ID end,
          Name= case when s.ParentID is not null then p.Name else s.Name end,
          s.Seller,
          s.Qty
   from Sales s
   left join Sales p on p.ID = s.ParentID and p.Seller = s.Seller
)
select ID,Name,Seller,sum(Qty) as Qty
from report
group by ID,Name,Seller

这里有一个使用 Distinct

这里有一个包含Seller in the left join的演示，它将为您提供卖方鲍勃的项目名称作为NULL，如果您具有正确的数据完整性，则左侧连接应该可以工作，这意味着项目和类别的单独表

回复您的最后一条评论，这里有一个演示如何使您的数据清晰

希望这对你有帮助

Answer 2

试试这个查询。如果您需要解释，请询问:)但这是相当简单的查询:)

SELECT MAX(SUBSTRING(ID, 1, 2)) AS ID, 
       SUBSTRING(Name, 1, 3) AS Name,
       Seller, 
       SUM(Qty) AS Qty 
FROM TABLE_NAME
GROUP BY Seller, SUBSTRING(Name, 1, 3)

Answer 3

我不确定ID是否总是nn[-nn]格式，以及Name是否可以处理鸡蛋以外的其他东西......

无论如何，这应该有效：

;with
m as (
    select *, nullif(charindex('-', ID), 0) div_id, nullif(charindex(' - ', name), 0) div_cat
    from EggTest 
),
c as (
    select *, 
        SUBSTRING(ID, 1, isnull(div_id-1, 1000)) main_ID, 
        SUBSTRING(name, 1, isnull(div_cat-1, 1000)) main_cat, 
        nullif(SUBSTRING(name, isnull(div_cat, 1000)+2, 1000), '') sub_cat
    from m
)
select main_ID ID, main_cat [Name], Seller, sum(qty) Qty
from c
group by main_ID, main_cat, seller

输出：

ID  Name    Seller  Qty
10  Egg     Anna    13
10  Egg     Bob     11
10  Egg     John    10