需要使用 CTE 的 SQL 查询

Question

任何人都可以帮助我编写一个定义如下的 sql 查询:
我有两个表 Itemtype 和 Items

Itemtype
-------------------
ItemTypeID ItemParent TypeName
-------------------------------
1           0          XXX
2           1          YYY
3           1          ZZZ
4           0          SSS
5           4          GGG

Items
--------------------
ItemID ItemTypeID ItemCost
----------------------------------
1        1         5000
2        2        1000
3        4        250
4        3        2000
5        5        400

Output
---------------------------
ItemtypeName ItemCost
------------------------------
XXX            8000 (1000+5000+2000)
SSS            650 (250+400)

这是两个表:我使用 itemtypeid 加入了两个表。
现在我需要显示 ItemParentId = 0 的项目
和
计算itemtypeId和ItemparentTypeId值与ItemTypeID相同的Items的Itemcost。

我使用 CTE 编写了一个查询，但它没有显示 ItemTypeName。

WITH it_cte AS ( select itemtypeid from ItemType WHERE
ItemType.ItemParentType IS NULL UNION ALL select i.ItemTypeid from
ItemType i  INNER JOIN it_cte icte ON icte.itemtypeid = i.itemtypeid )
select ItemParentType,SUM(Items.ItemCost) as itemcost from ItemType 
left join Items on ItemType.ItemTypeID = Items.ItemTypeID  or
ItemType.ItemParentType= Items.ItemTypeID  group by
ItemType.ItemParentType

有人可以帮忙吗？

谢谢
贾木纳

Answer 1

以下陈述

使用 CTE检索每个 ItemTypeID 的列表与它的根 ItemTypeID

加入 ItemType获取 TypeName

加入 Items获取 ItemCost

TypeName 上的群组得到 ItemCost 的总和

SQL 语句

;WITH q AS (
  SELECT  ItemTypeID , Root = ItemTypeID
  FROM    ItemType
  WHERE   ItemParent = 0
  UNION ALL
  SELECT  t.ItemTypeID, q.Root
  FROM    q
          INNER JOIN ItemType t ON t.ItemParent = q.ItemTypeID
) 
SELECT  it.TypeName, SUM(i.ItemCost)
FROM    q
        INNER JOIN ItemType it ON it.ItemTypeID = q.Root
        INNER JOIN Items i ON i.ItemTypeID = q.ItemTypeID
GROUP BY
        it.TypeName        

测试脚本

;WITH ItemType (ItemTypeID, ItemParent, TypeName) AS (
  SELECT 1, 0, 'XXX' UNION ALL
  SELECT 2, 1, 'yyy' UNION ALL
  SELECT 3, 1, 'ZZZ' UNION ALL
  SELECT 4, 0, 'SSS' UNION ALL
  SELECT 5, 4, 'GGG'
)
, Items (ItemID, ItemTypeID, ItemCost) AS (
  SELECT 1, 1, 5000 UNION ALL
  SELECT 2, 2, 1000 UNION ALL
  SELECT 3, 4, 250 UNION ALL
  SELECT 4, 3, 2000 UNION ALL
  SELECT 5, 5, 400
)
, q AS (
  SELECT  ItemTypeID , Root = ItemTypeID
  FROM    ItemType
  WHERE   ItemParent = 0
  UNION ALL
  SELECT  t.ItemTypeID, q.Root
  FROM    q
          INNER JOIN ItemType t ON t.ItemParent = q.ItemTypeID
) 
SELECT  it.TypeName, SUM(i.ItemCost)
FROM    q
        INNER JOIN ItemType it ON it.ItemTypeID = q.Root
        INNER JOIN Items i ON i.ItemTypeID = q.ItemTypeID
GROUP BY
        it.TypeName