代码之家 › 专栏 › 技术社区 › ravsun

合并多个表

sql-server

ravsun · 技术社区 · 6 年前

我有6张桌子。3个表分别按项目类别ID、项目子类别ID和项目ID提供月度销售数据。

ITEM_CATEGORY_ID || SALES
       1         ||   23
       2         ||   45

ITEM_SUBCATEGORY_ID || SALES
        11          ||    9
        12          ||   14
        21          ||    5
        22          ||   13
        23          ||   27

ITEM_ID || SALES
 111    ||  2
 112    ||  3
 113    ||  0
 114    ||  4
 121    ||  4
 122    ||  5
 123    ||  5
 211    ||  1
 212    ||  3
 213    ||  1
 221    ||  8
 222    ||  5
 223    ||  0
 231    || 17
 232    ||  7
 233    ||  3

其他3个表分别具有这些项目类别、项目子类别和项目的名称。

ITEM_CATEGORY_ID || Item_Category
       1         || Produce
       2         || Clothing

ITEM_CATEGORY_ID || ITEM_SUBCATEGORY_ID || Item_Subcategory
       1         ||          11         || Fruits
       1         ||          12         || Vegetables
       2         ||          21         || Formal
       2         ||          22         || PartyweaR
       2         ||          23         || Casual

ITEM_SUBCATEGORY_ID || ITEM_ID || Item
         11         ||   111   || Mango
         11         ||   112   || Apple
         11         ||   113   || Banana
         11         ||   114   || Strawberry
         12         ||   121   || Onion
         12         ||   122   || Potato
         12         ||   123   || Carrot
         21         ||   211   || Suits
         21         ||   212   || Trousers
         21         ||   213   || Shirts
         22         ||   221   || Designer Suits
         22         ||   222   || Tees
         22         ||   223   || Jeans
         23         ||   231   || Jackets
         23         ||   232   || Turtlenecks
         23         ||   233   || Belts

我需要将所有这些数据以下面提到的格式拉到一起:

Item_Category || Item_Subcategory || Item           || Item_Category_Sales || Item_SubCategory_Sales || Item_Sales
Produce       || Fruits           || Mango          ||       23            ||           9            ||   2
Produce       || Fruits           || Apple          ||       23            ||           9            ||   3
Produce       || Fruits           || Banana         ||       23            ||           9            ||   0
Produce       || Fruits           || Strawberry     ||       23            ||           9            ||   4
Produce       || Vegetables       || Onion          ||       23            ||          14            ||   4
Produce       || Vegetables       || Potato         ||       23            ||          14            ||   5
Produce       || Vegetables       || Carrot         ||       23            ||          14            ||   5
Clothing      || Formal           || Suits          ||       45            ||           5            ||   1
Clothing      || Formal           || Trousers       ||       45            ||           5            ||   3
Clothing      || Formal           || Shirts         ||       45            ||           5            ||   1
Clothing      || Partywear        || Designer Suits ||       45            ||          13            ||   8
Clothing      || Casual           || Tees           ||       45            ||          13            ||   5
Clothing      || Casual           || Jeans          ||       45            ||          13            ||   0
Clothing      || Casual           || Jackets        ||       45            ||          27            ||  17
Clothing      || Casual           || Turtlenecks    ||       45            ||          27            ||   7
Clothing      || Casual           || Belts          ||       45            ||          27            ||   3

我一直在尝试union all,但是我得到了这些表中所有单个记录的组合

SELECT t2.[Item_Category] AS [Item_Category],
   NULL [Item_Subcategory],
   NULL [Item],
   t1.[Sales] AS [Item_Category_Sales],
   NULL [Item_Subcategory_Sales],
   NULL [Item_Sales],
FROM MyDB.dbo.Item_Category_Sales_Data t1, MyDB.dbo.Item_Category_Data t2
WHERE t1.[ITEM_CATEGORY_ID] = t2.[ITEM_CATEGORY_ID]
UNION ALL
SELECT NULL [Item_Category],
   t2.[Item_Subcategory] AS [Item_Subcategory],
   NULL [Item],
   NULL [Item_Category_Sales],
   t1.[Sales] AS [Item_Subcategory_Sales],
   NULL [Item_Sales],
FROM MyDB.dbo.Item_Subcategory_Sales_Data t1, MyDB.dbo.Item_Subcategory_Data t2
WHERE t1.[ITEM_SUBCATEGORY_ID] = t2.[ITEM_SUBCATEGORY_ID]
UNION ALL
SELECT NULL [Item_Category],
   NULL [Item_Subcategory],
   t2.[Item] AS [Item],
   NULL [Item_Category_Sales],
   NULL [Item_Subcategory_Sales],
   t1.[Sales] AS [Item_Sales],
FROM MyDB.dbo.Item_Sales_Data t1, MyDB.dbo.Item_Data t2
WHERE t1.[ITEM_ID] = t2.[ITEM_ID];

1 回复 | 直到 6 年前

Kashyap MNVL 6 年前

SELECT IG.Item_Category ,
       ISG.Item_Subcategory ,
       I.Item
       ------- Other columns go here ---
       ---- You can use aliases to refer to columns from specific table---
FROM   [Item Categories] IG
       JOIN [Item SubCategories] ISG ON IG.ITEM_CATEGORY_ID = ISG.ITEM_CATEGORY_ID
       JOIN Items I ON ISG.ITEM_SUBCATEGORY_ID = I.ITEM_SUBCATEGORY_ID;