我这视图还有救吗?

Question

SELECT dbo.HSS_V_CustomerBaseInfoPayAllMoneyStage.AttrCustomerID, 
      dbo.HSS_V_CustomerBaseInfoPayAllMoneyStage.AttrStageID, 
      dbo.HSS_V_CustomerBaseInfoPayAllMoneyStage.AttrEndPayTime, 
      ISNULL(dbo.HSS_V_Cancel.AttrPayAmountCut, 0) AS AttrPayAmountCutCancel, 
      ISNULL(dbo.HSS_V_Cancel.AttrPayPerCut, 0) AS AttrPayPerCutCancel, 
      ISNULL(dbo.HSS_V_Cancel.AttrFinesAmountCut, 0) AS AttrFinesAmountCutCancel, 
      ISNULL(dbo.HSS_V_Cancel.AttrFinesPerCut, 0) AS AttrFinesPerCutCancel, 
      ROUND(dbo.HSS_V_CustomerBaseInfoPayAllMoneyStage.AttrAllMoney * ISNULL(dbo.HSS_V_Cancel.AttrPayPerCut,
       0) + ISNULL(dbo.HSS_V_Cancel.AttrPayAmountCut, 0), 1) 
      AS AttrPayAmountCutCancelAll, ISNULL(dbo.HSS_V_Offers.AttrPayAmountCut, 0) 
      AS AttrPayAmountCutOffers, ISNULL(dbo.HSS_V_Offers.AttrPayPerCut, 0) 
      AS AttrPayPerCutOffers, 
      ROUND(dbo.HSS_V_CustomerBaseInfoPayAllMoneyStage.AttrAllMoney * ISNULL(dbo.HSS_V_Offers.AttrPayPerCut,
       0) + ISNULL(dbo.HSS_V_Offers.AttrPayAmountCut, 0), 1) 
      AS AttrPayAmountCutOffersAll, ISNULL(dbo.HSS_V_Relief.AttrPayAmountCut, 0) 
      AS AttrAmountCutRelief, ISNULL(dbo.HSS_V_Relief.AttrPayPerCut, 0) 
      AS AttrPayPerCutRelief, 
      ROUND(dbo.HSS_V_CustomerBaseInfoPayAllMoneyStage.AttrAllMoney * ISNULL(dbo.HSS_V_Relief.AttrPayPerCut,
       0) + ISNULL(dbo.HSS_V_Relief.AttrPayAmountCut, 0), 1) AS AttrAmountCutReliefAll, 
      ISNULL(dbo.HSS_V_ReliefFines.AttrFinesAmountCut, 0) 
      AS AttrFinesAmountCutReliefFines, ISNULL(dbo.HSS_V_ReliefFines.AttrFinesPerCut, 
      0) AS AttrFinesPerCutReliefFines, ISNULL(dbo.HSS_V_PayList.AttrAmount, 0) 
      AS AttrAmountPay, ISNULL(dbo.HSS_V_PayList.AttrFines, 0) AS AttrFinesPay, 
      ((ROUND(dbo.HSS_V_CustomerBaseInfoPayAllMoneyStage.AttrAllMoney, 1) 
      - (dbo.HSS_V_CustomerBaseInfoPayAllMoneyStage.AttrAllMoney * ISNULL(dbo.HSS_V_Cancel.AttrPayPerCut,
       0) + ISNULL(dbo.HSS_V_Cancel.AttrPayAmountCut, 0))) 
      - (dbo.HSS_V_CustomerBaseInfoPayAllMoneyStage.AttrAllMoney * ISNULL(dbo.HSS_V_Offers.AttrPayPerCut,
       0) + ISNULL(dbo.HSS_V_Offers.AttrPayAmountCut, 0))) 
      - (dbo.HSS_V_CustomerBaseInfoPayAllMoneyStage.AttrAllMoney * ISNULL(dbo.HSS_V_Relief.AttrPayPerCut,
       0) + ISNULL(dbo.HSS_V_Relief.AttrPayAmountCut, 0)) 
      - ISNULL(dbo.HSS_V_PayList.AttrAmount, 0) AS AttrToPayAmount, 
      CASE WHEN ((ROUND(HSS_V_CustomerBaseInfoPayAllMoneyStage.AttrAllMoney, 1) 
      - (HSS_V_CustomerBaseInfoPayAllMoneyStage.AttrAllMoney * (isnull(HSS_V_Cancel.AttrPayPerCut,
       0)) + isnull(HSS_V_Cancel.AttrPayAmountCut, 0)) 
      - (HSS_V_CustomerBaseInfoPayAllMoneyStage.AttrAllMoney * (isnull(HSS_V_Offers.AttrPayPerCut,
       0)) + isnull(HSS_V_Offers.AttrPayAmountCut, 0)) 
      - (HSS_V_CustomerBaseInfoPayAllMoneyStage.AttrAllMoney * (isnull(HSS_V_Relief.AttrPayPerCut,
       0)) + isnull(HSS_V_Relief.AttrPayAmountCut, 0)) - (isnull(HSS_V_PayList.AttrAmount, 
      0))) * CASE WHEN datediff(day, 
      HSS_V_CustomerBaseInfoPayAllMoneyStage.AttrEndPayTime, getdate()) 
      > 0 THEN datediff(day, HSS_V_CustomerBaseInfoPayAllMoneyStage.AttrEndPayTime, 
      getdate()) * 0.001 ELSE 0 END * (1 - (isnull(HSS_V_Cancel.AttrFinesPerCut, 0) 
      + isnull(HSS_V_ReliefFines.AttrFinesPerCut, 0)))) 
      - isnull(HSS_V_Cancel.AttrFinesAmountCut, 0) 
      - isnull(HSS_V_ReliefFines.AttrFinesAmountCut, 0) 
      > 0 THEN ((ROUND(HSS_V_CustomerBaseInfoPayAllMoneyStage.AttrAllMoney, 1) 
      - (HSS_V_CustomerBaseInfoPayAllMoneyStage.AttrAllMoney * (isnull(HSS_V_Cancel.AttrPayPerCut,
       0)) + isnull(HSS_V_Cancel.AttrPayAmountCut, 0)) 
      - (HSS_V_CustomerBaseInfoPayAllMoneyStage.AttrAllMoney * (isnull(HSS_V_Offers.AttrPayPerCut,
       0)) + isnull(HSS_V_Offers.AttrPayAmountCut, 0)) 
      - (HSS_V_CustomerBaseInfoPayAllMoneyStage.AttrAllMoney * (isnull(HSS_V_Relief.AttrPayPerCut,
       0)) + isnull(HSS_V_Relief.AttrPayAmountCut, 0)) - (isnull(HSS_V_PayList.AttrAmount, 
      0))) * CASE WHEN datediff(day, 
      HSS_V_CustomerBaseInfoPayAllMoneyStage.AttrEndPayTime, getdate()) 
      > 0 THEN datediff(day, HSS_V_CustomerBaseInfoPayAllMoneyStage.AttrEndPayTime, 
      getdate()) * 0.001 ELSE 0 END * (1 - (isnull(HSS_V_Cancel.AttrFinesPerCut, 0) 
      + isnull(HSS_V_ReliefFines.AttrFinesPerCut, 0)))) 
      - isnull(HSS_V_Cancel.AttrFinesAmountCut, 0) 
      - isnull(HSS_V_ReliefFines.AttrFinesAmountCut, 0) 
      ELSE 0 END AS AttrToPayFinesAmount, CASE WHEN datediff(day, 
      HSS_V_CustomerBaseInfoPayAllMoneyStage.AttrEndPayTime, getdate()) 
      > 0 THEN datediff(day, HSS_V_CustomerBaseInfoPayAllMoneyStage.AttrEndPayTime, 
      getdate()) * 0.001 ELSE 0 END AS AttrFinesDays, 
      1 - (ISNULL(dbo.HSS_V_Cancel.AttrFinesPerCut, 0) 
      + ISNULL(dbo.HSS_V_ReliefFines.AttrFinesPerCut, 0)) AS AttrFinesPer, 
      ROUND(dbo.HSS_V_CustomerBaseInfoPayAllMoneyStage.AttrAllMoney, 1) 
      AS AttrAllMoney
FROM dbo.HSS_V_CustomerBaseInfoPayAllMoneyStage LEFT OUTER JOIN
      dbo.HSS_V_Cancel ON 
      dbo.HSS_V_CustomerBaseInfoPayAllMoneyStage.AttrCustomerID = dbo.HSS_V_Cancel.AttrCustomerID
       AND 
      dbo.HSS_V_CustomerBaseInfoPayAllMoneyStage.AttrStageID = dbo.HSS_V_Cancel.AttrStageID
       LEFT OUTER JOIN
      dbo.HSS_V_Offers ON 
      dbo.HSS_V_CustomerBaseInfoPayAllMoneyStage.AttrCustomerID = dbo.HSS_V_Offers.AttrCustomerID
       AND 
      dbo.HSS_V_CustomerBaseInfoPayAllMoneyStage.AttrStageID = dbo.HSS_V_Offers.AttrStageID
       LEFT OUTER JOIN
      dbo.HSS_V_Relief ON 
      dbo.HSS_V_CustomerBaseInfoPayAllMoneyStage.AttrCustomerID = dbo.HSS_V_Relief.AttrCustomerID
       AND 
      dbo.HSS_V_CustomerBaseInfoPayAllMoneyStage.AttrStageID = dbo.HSS_V_Relief.AttrStageID
       LEFT OUTER JOIN
      dbo.HSS_V_ReliefFines ON 
      dbo.HSS_V_CustomerBaseInfoPayAllMoneyStage.AttrCustomerID = dbo.HSS_V_ReliefFines.AttrCustomerID
       AND 
      dbo.HSS_V_CustomerBaseInfoPayAllMoneyStage.AttrStageID = dbo.HSS_V_ReliefFines.AttrStageID
       LEFT OUTER JOIN
      dbo.HSS_V_PayList ON 
      dbo.HSS_V_CustomerBaseInfoPayAllMoneyStage.AttrCustomerID = dbo.HSS_V_PayList.AttrCustomerID
       AND 
      dbo.HSS_V_CustomerBaseInfoPayAllMoneyStage.AttrStageID = dbo.HSS_V_PayList.AttrStageID

Answer 1

my god!估计FC看了也头疼。

Answer 2

My lady GAGA. 建议lz还是改清晰一些。 这样逻辑也好些。

Answer 3

没有统计分组排序，这个视图还好了，相关字段有index的话应该不会有什么问题，还要看你用什么查询条件，取出的数据有多少，取的数据多的话，用top少取点。用下查询计划看看吧

Answer 4

没Where??
至少加个表别名啦神人出世啊

Answer 5

本视图的作用是链接多个视图,where在程序应用中!

Answer 6

引用 24 楼 hbjlwhl 的回复:
弄个临时表吧！！先整理数据。
我现在就是用的临时表把这个视图的数据每天晚上通过调度包更新到临时表(因为每天的滞纳金要变化)这个做法入流吗?