急！！！在线等，超难SQL查询语句搞不定，请专家、高手帮忙！

Question

SELECT  A.RebuildCompanyID,
SUM(A.TargetPurchasedAmount) AS TargetPurchasedAmount,
SUM(A.SalesPromotionFee) AS SalesPromotionFee
FROM (
SELECT
RebuildCompanyM.RebuildCompanyID,
GroupMemberM.MemberID,
CAST(
CASE RebuildCalculation.PurchasedAmountEntrySpec
WHEN 0 THEN
ISNULL((RebuildClaimD.DiscountPurchasedAmount1 + 
RebuildClaimD.DiscountPurchasedAmount2 + 
RebuildClaimD.PurchasedAmount) - RebuildClaimD.DiscountAmount,0)
ELSE
ISNULL(RebuildSalesPromotionD.TargetPurchasedAmount,0)
END AS FLOAT) AS TargetPurchasedAmount,
CAST(
CASE RebuildCalculation.PurchasedAmountEntrySpec
WHEN 0 THEN
CASE (
SELECT COUNT(*) FROM Config
WHERE PurchaseForRefund <= RebuildClaimT.DiscountPurchasedAmount1 + 
RebuildClaimT.DiscountPurchasedAmount2 + 
RebuildClaimT.PurchasedAmount)
          WHEN 0 THEN 0
          ELSE (
SELECT ROUND(((RebuildClaimD.DiscountPurchasedAmount1 + 
RebuildClaimD.DiscountPurchasedAmount2 + 
RebuildClaimD.PurchasedAmount) - 
RebuildClaimD.DiscountAmount) * RefundRate,0)
FROM Config
WHERE PurchaseForRefund <= RebuildClaimT.DiscountPurchasedAmount1 + 
RebuildClaimT.DiscountPurchasedAmount2 + 
RebuildClaimT.PurchasedAmount)
END
ELSE
ISNULL(RebuildSalesPromotionD.SalesPromotionFee,0)
END AS FLOAT) AS SalesPromotionFee
FROM RebuildCompanyM,GroupMemberM
LEFT JOIN RebuildSalesPromotionD ON (RebuildSalesPromotionD.GeneratingMonth = '2008/01/01'
AND RebuildSalesPromotionD.RebuildCompanyID = RebuildCompanyM.RebuildCompanyID
AND RebuildSalesPromotionD.MemberID = GroupMemberM.MemberID)
LEFT JOIN RebuildClaimD ON (RebuildClaimD.GeneratingMonth = '2008/01/01'
AND RebuildClaimD.RebuildCompanyID = RebuildCompanyM.RebuildCompanyID
AND RebuildClaimD.MemberID = GroupMemberM.MemberID)
LEFT JOIN RebuildClaimT ON (RebuildClaimT.GeneratingMonth = '2008/01/01'
AND RebuildClaimT.MemberID = GroupMemberM.MemberID)
LEFT JOIN RebuildCalculation ON (RebuildCalculation.RebuildCompanyID = RebuildCompanyM.RebuildCompanyID
AND RebuildCalculation.MemberID = GroupMemberM.MemberID)
WHERE (RebuildCompanyM.DeleteFlag = 0
AND GroupMemberM.DeleteFlag = 0
AND GroupMemberM.IsInputRebuild = 1)) A
GROUP BY A.RebuildCompanyID
ORDER BY A.RebuildCompanyID ASC

Answer 1

为了看着方便改成SQL格式的，请大家帮忙看看。

Answer 2

--try: 全部用别名。为何不用呢，即简洁又明了
SELECT  A.RebuildCompanyID,
SUM(A.TargetPurchasedAmount) AS TargetPurchasedAmount,
SUM(A.SalesPromotionFee) AS SalesPromotionFee
FROM (
SELECT
t1.RebuildCompanyID,
t2.MemberID,
CAST(
CASE t6.PurchasedAmountEntrySpec
WHEN 0 THEN
ISNULL((t4.DiscountPurchasedAmount1 + 
t4.DiscountPurchasedAmount2 + 
t4.PurchasedAmount) - t4.DiscountAmount,0)
ELSE
ISNULL(t3.TargetPurchasedAmount,0)
END AS FLOAT) AS TargetPurchasedAmount,
CAST(
CASE t6.PurchasedAmountEntrySpec
WHEN 0 THEN
CASE (
SELECT COUNT(*) FROM Config
WHERE PurchaseForRefund    <= t5.DiscountPurchasedAmount1 + 
t5.DiscountPurchasedAmount2 + 
t5.PurchasedAmount)
          WHEN 0 THEN 0
          ELSE (
SELECT ROUND(((t4.DiscountPurchasedAmount1 + 
t4.DiscountPurchasedAmount2 + 
t4.PurchasedAmount) - 
t4.DiscountAmount) * RefundRate,0)
FROM Config
WHERE PurchaseForRefund    <= t5.DiscountPurchasedAmount1 + 
t5.DiscountPurchasedAmount2 + 
t5.PurchasedAmount)
END
ELSE
ISNULL(t3.SalesPromotionFee,0)
END AS FLOAT) AS SalesPromotionFee
FROM RebuildCompanyM as t1,GroupMemberM as t2
LEFT JOIN RebuildSalesPromotionD as t3 ON (t3.GeneratingMonth = '2008/01/01'
AND t3.RebuildCompanyID = t1.RebuildCompanyID
AND t3.MemberID = t2.MemberID)
LEFT JOIN RebuildClaimD as t4 ON (t4.GeneratingMonth = '2008/01/01'
AND t4.RebuildCompanyID = t1.RebuildCompanyID
AND t4.MemberID = t2.MemberID)
LEFT JOIN RebuildClaimT as t5 ON (t5.GeneratingMonth = '2008/01/01'
AND t5.MemberID = t2.MemberID)
LEFT JOIN RebuildCalculation as t6 ON (t6.RebuildCompanyID = t1.RebuildCompanyID
AND t6.MemberID = t2.MemberID)
WHERE (t1.DeleteFlag = 0
AND t2.DeleteFlag = 0
AND t2.IsInputRebuild = 1)) A
GROUP BY A.RebuildCompanyID
ORDER BY A.RebuildCompanyID ASC

Answer 3

 --就是此段,加上个内连接即可
 FROM RebuildCompanyM,GroupMemberM
    LEFT JOIN RebuildSalesPromotionD ON (RebuildSalesPromotionD.GeneratingMonth = '2008/01/01'
                        AND RebuildSalesPromotionD.RebuildCompanyID = RebuildCompanyM.RebuildCompanyID
                        AND RebuildSalesPromotionD.MemberID = GroupMemberM.MemberID)
    LEFT JOIN RebuildClaimD ON (RebuildClaimD.GeneratingMonth = '2008/01/01'
                    AND RebuildClaimD.RebuildCompanyID = RebuildCompanyM.RebuildCompanyID
                    AND RebuildClaimD.MemberID = GroupMemberM.MemberID)
    LEFT JOIN RebuildClaimT ON (RebuildClaimT.GeneratingMonth = '2008/01/01'
                    AND RebuildClaimT.MemberID = GroupMemberM.MemberID)
    LEFT JOIN RebuildCalculation ON (RebuildCalculation.RebuildCompanyID = RebuildCompanyM.RebuildCompanyID
                    AND RebuildCalculation.MemberID = GroupMemberM.MemberID)
    WHERE (RebuildCompanyM.DeleteFlag = 0
        AND GroupMemberM.DeleteFlag = 0
        AND GroupMemberM.IsInputRebuild = 1)) A
-----------------------------------------
--改为
 FROM RebuildCompanyM inner join GroupMemberM on (RebuildSalesPromotionD.RebuildCompanyID = RebuildCompanyM.RebuildCompanyID)
    LEFT JOIN RebuildSalesPromotionD ON (RebuildSalesPromotionD.GeneratingMonth = '2008/01/01'
                        AND RebuildSalesPromotionD.RebuildCompanyID = RebuildCompanyM.RebuildCompanyID
                        AND RebuildSalesPromotionD.MemberID = GroupMemberM.MemberID)
    LEFT JOIN RebuildClaimD ON (RebuildClaimD.GeneratingMonth = '2008/01/01'
                    AND RebuildClaimD.RebuildCompanyID = RebuildCompanyM.RebuildCompanyID
                    AND RebuildClaimD.MemberID = GroupMemberM.MemberID)
    LEFT JOIN RebuildClaimT ON (RebuildClaimT.GeneratingMonth = '2008/01/01'
                    AND RebuildClaimT.MemberID = GroupMemberM.MemberID)
    LEFT JOIN RebuildCalculation ON (RebuildCalculation.RebuildCompanyID = RebuildCompanyM.RebuildCompanyID
                    AND RebuildCalculation.MemberID = GroupMemberM.MemberID)
    WHERE (RebuildCompanyM.DeleteFlag = 0
        AND GroupMemberM.DeleteFlag = 0
        AND GroupMemberM.IsInputRebuild = 1)) A

Answer 4

我说明一下。
表RebuildCompanyM中包含RebuildCompanyID字段；
表GroupMemberM中包含MemberID字段；
表RebuildSalesPromotionD中包含RebuildCompanyID和MemberID字段；
表RebuildClaimD中包含RebuildCompanyID和MemberID字段；
表RebuildClaimT中包含MemberID字段；
表RebuildCalculation中包含RebuildCompanyID和MemberID字段；
所有的表中都包含GeneratingMonth字段RebuildCompanyID、MemberID、GeneratingMonth都是表中的主键,因此具有唯一性。我没用过笛卡尔,不过从上面分析,应该不需要用笛卡尔积。逻辑是以日期(GeneratingMonth字段)为查询条件,计算出符合条件的金额合计数。

Answer 5

先减少几个字段试试,排除法.
这SQL,看着就头晕.但是看起来逻辑又不是很复杂.
比这复杂多的语句有的是.叫别人看别人很难看得出耿,所以还得你自己调试.

Answer 6

SELECT  A.RebuildCompanyID,
SUM(A.TargetPurchasedAmount) AS TargetPurchasedAmount,
SUM(A.SalesPromotionFee) AS SalesPromotionFee
FROM (
SELECT
t1.RebuildCompanyID,
t2.MemberID,
CAST(
CASE t6.PurchasedAmountEntrySpec
WHEN 0 THEN
ISNULL((t4.DiscountPurchasedAmount1 + 
t4.DiscountPurchasedAmount2 + 
t4.PurchasedAmount) - t4.DiscountAmount,0)
ELSE
ISNULL(t3.TargetPurchasedAmount,0)
END AS FLOAT) AS TargetPurchasedAmount,
CAST(
CASE t6.PurchasedAmountEntrySpec
WHEN 0 THEN
CASE (
SELECT COUNT(*) FROM Config
WHERE PurchaseForRefund    <= t5.DiscountPurchasedAmount1 + 
t5.DiscountPurchasedAmount2 + 
t5.PurchasedAmount)
          WHEN 0 THEN 0
          ELSE (
SELECT ROUND(((t4.DiscountPurchasedAmount1 + 
t4.DiscountPurchasedAmount2 + 
t4.PurchasedAmount) - 
t4.DiscountAmount) * RefundRate,0)
FROM Config
WHERE PurchaseForRefund    <= t5.DiscountPurchasedAmount1 + 
t5.DiscountPurchasedAmount2 + 
t5.PurchasedAmount)
END
ELSE
ISNULL(t3.SalesPromotionFee,0)
END AS FLOAT) AS SalesPromotionFee
FROM RebuildCompanyM as t1,GroupMemberM as t2
LEFT JOIN RebuildSalesPromotionD as t3 ON (t3.GeneratingMonth = '2008/01/01'
AND t3.RebuildCompanyID = t1.RebuildCompanyID
AND t3.MemberID = t2.MemberID)
LEFT JOIN RebuildClaimD as t4 ON (t4.GeneratingMonth = '2008/01/01'
AND t4.RebuildCompanyID = t1.RebuildCompanyID
AND t4.MemberID = t2.MemberID)
LEFT JOIN RebuildClaimT as t5 ON (t5.GeneratingMonth = '2008/01/01'
AND t5.MemberID = t2.MemberID)
LEFT JOIN RebuildCalculation as t6 ON (t6.RebuildCompanyID = t1.RebuildCompanyID
AND t6.MemberID = t2.MemberID)
WHERE (t1.DeleteFlag = 0
AND t2.DeleteFlag = 0
AND t2.IsInputRebuild = 1)) A
GROUP BY A.RebuildCompanyID
ORDER BY A.RebuildCompanyID ASC

Answer 7

晕，这么复杂的表为什么不用临时表呢？又条理清楚，速度也快谁会给你仔细的看一大段代码啊？select * into #tb1 from table....select * into #tb2 from #tb1.............
........
drop table #tb1
drop table #tb2
.........