将多个表值连接到一个表中以便清楚地看到事物

I have 4 tables:

我有4张桌子:

Orders:

OrderID Detail  Discount
------------------------
1001    xxx     True
1002    xxx     True
1003    xxx     True
1004    xxx     False
1005    xxx     True
1006    xxx     True

OrderDiscounts:

OrderID DiscountTypeID  DiscountID
----------------------------------
1001     1              8
1002     2              12
1003     1              9
1005     2              13
1006     2              9

Coupons (DiscountTypeID = 1):

优惠券(DiscountTypeID = 1):

CouponID    Title
------------------------
8           CouponTitle8
9           CouponTitle9

Campaigns (DiscountTypeID = 2):

广告系列(DiscountTypeID = 2):

CampaignID  Title
--------------------------
9           CampaignTitle9
12          CampaignTitle12
13          CampaignTitle13

I need a query that will merge all 4 tables into 1 table which will give some result like:

我需要一个查询,将所有4个表合并为1个表,这将给出一些结果,如:

Result:

OrderID Discount    DiscountType    DiscountTitle
-----------------------------------------------------
1001    True        Coupon          CouponTitle8
1002    True        Campaign        CampaignTitle12
1003    True        Coupon          CouponTitle9
1004    False               
1005    True        Campaign        CampaignTitle13
1006    True        Campaign        CampaignTitle9

Note that some Coupon ID may exist as a Campaign ID. Like '9' exists for both as CouponID and CampaignID in this scenario..

请注意,某些优惠券ID可能作为广告系列ID存在。在这种情况下,像CouponID和CampaignID一样存在“9”。

In addition to the query required, a proper explanation about how/why using such commands while building the query would be great as I don't just look for an answer but I'd like to handle similar scenarios by myself as well. Thanks!

除了所需的查询之外,在构建查询时如何/为什么使用这些命令的正确解释将是很好的,因为我不只是寻找答案,但我也想自己处理类似的情况。谢谢!

2 个解决方案

#1

It should look similar to below:

它应该类似于下面的内容:

SELECT Orders.OrderID,
CASE when OrderDiscounts.OrderID is NULL THEN False ELSE True end,
CASE when OrderDiscounts.DiscountTypeID  = 1 THEN 'Coupon' 
     when OrderDiscounts.DiscountTypeID  = 2 THEN 'Campaign' 
     else '' end,
CASE WHEN OrderDiscounts.DiscountTypeID  = 1 THEN Coupons.Title 
     WHEN OrderDiscounts.DiscountTypeID  = 2 THEN Campaigns.Title  
     ELSE '' end
FROM Orders
LEFT JOIN OrderDiscounts on Orders.OrderID = OrderDiscounts.OrderID
LEFT JOIN Coupons on OrderDiscounts.DiscountID = Coupons.CouponID
LEFT JOIN Campaigns on OrderDiscounts.DiscountID = Campaigns.CampaignID

#2

The following code should work - I've done it with a little modification: I've added a 'DiscountType' table.

下面的代码应该可以工作 - 我做了一些修改:我添加了一个'DiscountType'表。

I understand that maybe all you're looking for is a quick and easy answer, but I think the data schema could be setup better. Having an entire table for each discount type is not scalable nor flexible, and queries like the ones suggested will only get hairier, nastier, and meaner as time goes on. Of course, it all depends on your requirements.

我知道也许你正在寻找的只是一个快速简单的答案,但我认为数据模式可以更好地设置。为每种折扣类型设置一个完整的表格是不可扩展的,也不是灵活的,并且随着时间的推移,像建议的那样的查询只会变得更加毛茸茸,更加糟糕。当然,这一切都取决于您的要求。

SELECT
  OrderId = o.OrderId,
  Discount = CASE WHEN o.Discount = 1 THEN 'True' ELSE 'False' END,
  DiscountType = COALESCE(dt1.DiscountType, dt2.DiscountType, ''),
  DiscountTitle = COALESCE(coup.Title, camp.Title, '')
FROM
  Orders o
  LEFT JOIN 
    (OrderDiscounts od1       
     JOIN Coupons coup ON coup.CouponID = od1.DiscountID
     LEFT JOIN DiscountType dt1 ON dt1.DiscountTypeId = od1.DiscountTypeId
     ) ON o.OrderId = od1.OrderId
       AND o.Discount = 1
       AND od1.DiscountTypeID = 1
  LEFT JOIN 
    (OrderDiscounts od2 
     JOIN Campaigns camp ON camp.CampaignID = od2.DiscountID
     LEFT JOIN DiscountType dt2 ON dt2.DiscountTypeId = od2.DiscountTypeId
     ) ON 
       od2.OrderID = o.OrderID 
       AND o.Discount = 1
       AND od2.DiscountTypeID = 2

See for yourself here at SqlFiddle.

在SqlFiddle看看你自己。

#1