使用公共表达式（CTE）需要的 SQL 查询

Question

使用公共表达式（CTE）需要的 SQL 查询

4

有人能帮我编写下面定义的 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相同值的项目的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

有人能帮忙吗？

谢谢 Jamuna

- user1500633

你的陈述与示例不符。在你的示例中没有 ItemParentType 和 NULL 值。我们可以假设 ItemParentType 是 ItemParent，而 0 应该是 NULL 吗？ - Lieven Keersmaekers

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Lieven Keersmaekers · Accepted Answer

以下语句：

使用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