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

Question

我把完整的code贴上来，高手帮忙看一下。
这个SQL语句原本是cache数据库用的，在cache中可以正常运行。
现在想改成MSSQL，弄了2天还没弄出来，头都大了，高手帮忙看一下，不胜感激，谢谢！！！在查询分析器里执行出错：
msg 107, 级别 16, 3, 状态行 1
列前缀 'RebuildCompanyM' 与查询中使名或别名不匹配。
msg 107, 级别 16, 3, 状态行 1
列前缀 'RebuildCompanyM' 与查询中使名或别名不匹配。
msg 107, 级别 16, 3, 状态行 1
列前缀 'RebuildCompanyM' 与查询中使名或别名不匹配。
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

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 2

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

Answer 3

   FROM RebuildCompanyM,GroupMemberM
->   FROM RebuildCompanyM join GroupMemberM on 连接条件。

Answer 4

这个SQL语句还是个MM写的，偶惭愧啊

Answer 5

SQL SERVER 不支持这样写，要么用子查询要么用显式的各种join

Answer 6

SQL SERVER 不支持这样写，要么用子查询要么用显式的各种join

Answer 7

select * from t1,t2 left join ta  on t1.id = ta.id and t2.id = ta.id
/*
服务器: 消息 107，级别 16，状态 3，行 1
列前缀 't1' 与查询中所用的表名或别名不匹配。
*/

Answer 8

--> 模仿一个：TRY
select top 1 * from sysobjects a,syscolumns b left join systypes c on b.xusertype = c.xusertype

Answer 9

对，是MSSQL不支持这种写法，cache里就没问题。
我也想重新写，不过这个逻辑复杂程度超出我的水平，我搞不定，头都大了。请高手帮忙把这个拆分一下，谢谢！！！

Answer 10

哦，是的是的，on里面出现了，cross join是没有on的把on里面出现的写在WHERE才行

Answer 11

这本来是一个存储过程，执行后返回3个值RebuildCompanyID,TargetPurchasedAmount,SalesPromotionFee
封装到一个xsd的DataSet中。

Answer 12

如果你真的是交叉连接，可以如下修改，但你要确定结果是不是正确,这可是笛卡儿。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 
FROM RebuildCompanyM cross join 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 13

select top 1 * from sysobjects a,syscolumns b left join systypes c on b.xusertype = c.xusertype
--> okselect top 1 * from syscolumns b,sysobjects a left join systypes c on b.xusertype = c.xusertype
/*
服务器: 消息 107，级别 16，状态 2，行 2
列前缀 'b' 与查询中所用的表名或别名不匹配。
*/select top 1 * from syscolumns b,sysobjects a left join systypes c on 1=1 where b.xusertype = c.xusertype
-->ok

Answer 14

--将中间from 的一段加上内连接即可
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)

Answer 15

表的引用不符合T-SQL语句格式.

Answer 16

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

Answer 17

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 RebuildSalesPromotionD left join GroupMemberM on RebuildSalesPromotionD.MemberID = GroupMemberM.MemberID
      LEFT JOIN RebuildCompanyM ON  RebuildSalesPromotionD.RebuildCompanyID = RebuildCompanyM.RebuildCompanyID
      LEFT JOIN RebuildClaimD ON  RebuildClaimD.RebuildCompanyID = RebuildCompanyM.RebuildCompanyID
      LEFT JOIN RebuildClaimT ON  RebuildClaimT.MemberID = GroupMemberM.MemberID
      LEFT JOIN RebuildCalculation ON RebuildCalculation.RebuildCompanyID = RebuildCompanyM.RebuildCompanyID
    WHERE RebuildCompanyM.DeleteFlag = 0
        AND GroupMemberM.DeleteFlag = 0
        AND GroupMemberM.IsInputRebuild = 1
        and RebuildClaimD.MemberID = GroupMemberM.MemberID
        and RebuildClaimT.MemberID = GroupMemberM.MemberID
        and RebuildCalculation.MemberID = GroupMemberM.MemberID
        and RebuildSalesPromotionD.GeneratingMonth = '2008/01/01'
        and RebuildClaimD.GeneratingMonth = '2008/01/01'
        and RebuildClaimT.GeneratingMonth = '2008/01/01') A
GROUP BY A.RebuildCompanyID
ORDER BY A.RebuildCompanyID ASC

Answer 18

20楼的方法执行出错。
msg 107, 级别 16, 3, 状态行 1
列前缀 'RebuildSalesPromotionD' 与查询中使名或别名不匹配。 18楼、23楼的方法执行没有报错。
等待测试数据。

Answer 19

干嘛要搞怎么复杂的SQL语句,把大问题拆分成小问题,然后一个一个解决呀.搞那么长一串,连业务逻辑都不知道,估计没人知道你想干嘛

Answer 20

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
看一下
|       FROM    RebuildCompanyM,
|       |       GroupMemberM
|       |       LEFT JOIN RebuildSalesPromotionDRebuildCompanyM与GroupMemberM变成笛卡尔了,怎么感觉都不是很顺

Answer 21

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)简化如下:
from A, B
left join C on C.1 = A.1 and C.2 = B.1
left join D on D.1 = A.1 and D.2 = B.1
left join E on E.1 = B.1
left join F on F.1 = A.1 and E.2 = B.1
我猜想应该是这样:
from A, B
left join C on C.1 = A.1 and C.2 = B.1
left join D on D.1 = A.1 and D.2 = B.1
left join E on E.1 = A.1 and E.2 = B.1
left join F on F.1 = A.1 and E.2 = B.1
为了保持一致.
这个语句是有问题,A与B其实就是笛卡尔积。这是问题1，
还有报错原因就是select A.RebuildCompanyID from ...
虚拟的A表中是没有RebuildCompanyID字段的，而是有RebuildCompanyM.RebuildCompanyID字段。

Answer 22

FROM RebuildCompanyM,GroupMemberM 
->    FROM RebuildCompanyM 
      left join GroupMemberM on 1 =1

Answer 23

单纯看语句没什么问题，根据提示，应该首先检查两边数据库结构是否一致FROM RebuildCompanyM,GroupMemberM这句在SQL中效率奇差

Answer 24

没有心情了解 业务逻辑……我建议你自己重写，拆分步骤子查询在sql2005不一定比临时表来的快