Question

我有以下选择的 SQL 进行基本选择语句,尽管其中确实包括计算出来的列:

    Select *
    From
    (
      Select *,
             ROW_NUMBER() OVER
                              (ORDER BY
                                CASE WHEN @sortBy = 0 THEN R.DateCreated End Desc,
                                CASE WHEN @sortBy = 1 THEN R.DateCreated end Asc,
                                CASE WHEN @sortBy = 2 THEN TotalVotes END Desc,
                                CASE WHEN @sortBy = 2 THEN R.TotalFoundNotUseful END Desc
                              ) AS RowNumber 

      From
      (
        Select *, (TotalFoundUseful + TotalFoundNotUseful) As TotalVotes
      From Reviews
      Where (DealID = @dealID) And (TotalAbuses < 10) And (Deleted = 0)
    ) As R
  ) As Rev
  Where RowNumber BETWEEN @startRecord AND @endRecord

如果您仔细看一看, SELECT 语句本身执行过三次。我无法相信这是否必要。是否有办法可以将此语句缩减为两个选定的语句( 甚至可能一个 ) 。我其实不需要返回行号。它仅用于在一定范围内选择行。

Answer 1

您可以用两个方法将行号与原始选项放在对审评的原始选项中。如果您想要在 WRO_NUBER 这样的窗口函数上有一个条款, 您不能去一个窗口。

您必须写两次 < code> TotalFoundFoundful useful + completeFoundFound notworkful , 但只对它作一次评估, 这样不会影响性能。

我也不指望移动到两个对业绩有任何影响,但你应该测试一下。

Select *
    From
    (
  Select *, (TotalFoundUseful + TotalFoundNotUseful) As TotalVotes,
          ROW_NUMBER() OVER
              (ORDER BY
            CASE WHEN @sortBy = 0 THEN DateCreated End Desc,
            CASE WHEN @sortBy = 1 THEN DateCreated end Asc,
            CASE WHEN @sortBy = 2 THEN TotalFoundUseful + TotalFoundNotUseful END Desc,
            CASE WHEN @sortBy = 2 THEN TotalFoundUseful END Desc
          ) AS RowNumber 
 From Reviews
     Where (DealID = @dealID) And (TotalAbuses < 10) And (Deleted = 0)

  ) As Rev
  Where RowNumber BETWEEN @startRecord AND @endRecord

友情链接