我有四张桌子:
命令:
OrderID Detail Discount
------------------------
1001 xxx True
1002 xxx True
1003 xxx True
1004 xxx False
1005 xxx True
1006 xxx TrueOrderDiscounts:
OrderID DiscountTypeID DiscountID
----------------------------------
1001 1 8
1002 2 12
1003 1 9
1005 2 13
1006 2 9优惠券 (DiscountTypeID = 1):
CouponID Title
------------------------
8 CouponTitle8
9 CouponTitle9Campaigns (DiscountTypeID = 2):
CampaignID Title
--------------------------
9 CampaignTitle9
12 CampaignTitle12
13 CampaignTitle13我需要一个查询,将所有4个表合并到一个表中,这将给出如下结果:
结果:
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请注意,某些优惠券ID可能以活动ID的形式存在。在这个场景中,像'9‘一样存在于CouponID和CampaignID中。
除了所需的查询之外,对于如何/为什么在构建查询时使用此类命令的正确解释将是很棒的,因为我不只是寻找答案,我还想自己处理类似的场景。谢谢!
发布于 2013-03-08 20:46:49
它应类似于以下内容:
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发布于 2013-03-08 21:17:55
下面的代码应该可以工作--我已经用做了一些修改,:我添加了一个'DiscountType‘表。
我知道,也许您想要的只是一个快速而简单的答案,但我认为数据模式可以设置得更好。对于每种折扣类型都有一个完整的表,这既不是可伸缩的,也不是灵活的,而且随着时间的推移,类似于建议的查询只会变得更糟、更麻烦和更吝啬。当然,这取决于您的需求。
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你自己看看这里在SqlFiddle。
https://stackoverflow.com/questions/15302725
复制相似问题