在sql server中选择前50行

问题描述：

我有SQL Server 2014中使用的以下T-SQL代码。在sql server中选择前50行

此代码会产生1000行。但是我只需要从顶端50行（来自供应商栏）。

在下面的代码中，如果我使用SELECT Top 50 s.[CusNo] Supplier那么我没有得到想要的结果。

在下面的代码中需要做什么修改才能得到前50行（“供应商”列），而现有结果没有任何变化。

SELECT s.[CusNo] Supplier, 
RTRIM(CAST(s.[Customer] AS VARCHAR(50))) AS Name, 
s.[ConcessionNo] Concession, 
RTRIM(CAST(s.[ConcessionName] AS VARCHAR(50))) AS ConcessionName, 

sum(case when s.Date between convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) - 1) and convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) + 5) 
      then s.SELLINC else 0 end) ActualSales, 

    sum(case when s.Date between convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) -1 /* On 14/Feb/2021 modify +6 to -1 */) and convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) + 5 /* On 14/Feb/2021 modify +12 to +5 */) 
      then s.SELLINC else 0 end) LastYrVariance, 

    (sum(case when s.Date between convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) - 1) and convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) + 5) then s.SELLINC else 0 end))- 

    (sum(case when s.Date between convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) -1 /* On 14/Feb/2021 modify +6 to -1 */) and convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) + 5 /* On 14/Feb/2021 modify +12 to +5 */) then s.SELLINC else 0 end)) LastYrVariancePounds, 

    (IsNull(sum(case when s.Date between convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) - 1) and convert(date,dateadd(wk, datediff(wk, 0, getdate()) - 1, 0) + 5) 
      then s.SELLINC else 0 end)-sum(case when s.Date between convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) -1 /* On 14/Feb/2021 modify +6 to -1 */) and convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) + 5 /* On 14/Feb/2021 modify +12 to +5 */) 
      then s.SELLINC else 0 end),0)/NullIf(sum(case when s.Date between convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) -1 /* On 14/Feb/2021 modify +6 to -1 */) and convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) + 5 /* On 14/Feb/2021 modify +12 to +5 */) 
      then s.SELLINC else 0 end),0))*100 LastYrVariancePercentage, 


    sum(case when s.Date 
     BETWEEN   
      convert(varchar(10), DATEADD(day, DATEDIFF(day, '19000107', DATEADD(month, DATEDIFF(MONTH, 0, CONVERT(date, CONVERT(VARCHAR(4), (CASE WHEN MONTH(GetDate()) = 1 THEN CONVERT(VARCHAR(4), GetDate(), 112) - 1 ELSE CONVERT(VARCHAR(4), GetDate(), 112) END), 112) + '0101')), 30))/7 * 7, '19000107'), 120) 
     AND   
      Convert(date, dateadd(wk, datediff(wk, 0, GETDATE()) - 1, 0) + 5)  
      then s.SELLINC else 0 end) YrToDateActual, 


    sum(case when s.Date  
     BETWEEN 
      convert(varchar(10), DATEADD(day, DATEDIFF(day, '19000107', DATEADD(month, DATEDIFF(MONTH, 0, CONVERT(date, CONVERT(VARCHAR(4), (CASE WHEN MONTH(DATEADD(year,-1,GetDate())) = 1 THEN CONVERT(VARCHAR(4), DATEADD(year,-1,GetDate()), 112) - 1 ELSE CONVERT(VARCHAR(4), DATEADD(year,-1,GetDate()), 112) END), 112) + '0101')), 30))/7 * 7, '19000107'), 120) 
     AND 
      convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) + 5 /* On 14/Feb/2021 modify +12 to +5 */) 
      then s.SELLINC else 0 end) LastYrToDateActual, 


    (sum(case when s.Date 
    BETWEEN   
      convert(varchar(10), DATEADD(day, DATEDIFF(day, '19000107', DATEADD(month, DATEDIFF(MONTH, 0, CONVERT(date, CONVERT(VARCHAR(4), (CASE WHEN MONTH(GetDate()) = 1 THEN CONVERT(VARCHAR(4), GetDate(), 112) - 1 ELSE CONVERT(VARCHAR(4), GetDate(), 112) END), 112) + '0101')), 30))/7 * 7, '19000107'), 120) 
     AND   
      Convert(date, dateadd(wk, datediff(wk, 0, GETDATE()) - 1, 0) + 5) 
      then s.SELLINC else 0 end)) 

    - 

    (sum(case when s.Date 
     BETWEEN 
      convert(varchar(10), DATEADD(day, DATEDIFF(day, '19000107', DATEADD(month, DATEDIFF(MONTH, 0, CONVERT(date, CONVERT(VARCHAR(4), (CASE WHEN MONTH(DATEADD(year,-1,GetDate())) = 1 THEN CONVERT(VARCHAR(4), DATEADD(year,-1,GetDate()), 112) - 1 ELSE CONVERT(VARCHAR(4), DATEADD(year,-1,GetDate()), 112) END), 112) + '0101')), 30))/7 * 7, '19000107'), 120) 
     AND 
      convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) + 5 /* On 14/Feb/2021 modify +12 to +5 */) 
      then s.SELLINC else 0 end)) YrToDateVariancePounds, 


    ((IsNull  
     (     
      (sum(case when s.Date 
      BETWEEN   
      convert(varchar(10), DATEADD(day, DATEDIFF(day, '19000107', DATEADD(month, DATEDIFF(MONTH, 0, CONVERT(date, CONVERT(VARCHAR(4), (CASE WHEN MONTH(GetDate()) = 1 THEN CONVERT(VARCHAR(4), GetDate(), 112) - 1 ELSE CONVERT(VARCHAR(4), GetDate(), 112) END), 112) + '0101')), 30))/7 * 7, '19000107'), 120) 
     AND   
      Convert(date, dateadd(wk, datediff(wk, 0, GETDATE()) - 1, 0) + 5) 
      then s.SELLINC else 0 end)) 

    - 

    (sum(case when s.Date 
     BETWEEN 
      convert(varchar(10), DATEADD(day, DATEDIFF(day, '19000107', DATEADD(month, DATEDIFF(MONTH, 0, CONVERT(date, CONVERT(VARCHAR(4), (CASE WHEN MONTH(DATEADD(year,-1,GetDate())) = 1 THEN CONVERT(VARCHAR(4), DATEADD(year,-1,GetDate()), 112) - 1 ELSE CONVERT(VARCHAR(4), DATEADD(year,-1,GetDate()), 112) END), 112) + '0101')), 30))/7 * 7, '19000107'), 120) 
     AND 
      convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) + 5 /* On 14/Feb/2021 modify +12 to +5 */) 
      then s.SELLINC else 0 end)) 

      ,0) 

    ) 

    /

    (NullIf 
     (
     sum(case when s.Date 
      BETWEEN 
      convert(varchar(10), DATEADD(day, DATEDIFF(day, '19000107', DATEADD(month, DATEDIFF(MONTH, 0, CONVERT(date, CONVERT(VARCHAR(4), (CASE WHEN MONTH(DATEADD(year,-1,GetDate())) = 1 THEN CONVERT(VARCHAR(4), DATEADD(year,-1,GetDate()), 112) - 1 ELSE CONVERT(VARCHAR(4), DATEADD(year,-1,GetDate()), 112) END), 112) + '0101')), 30))/7 * 7, '19000107'), 120) 
     AND 
      convert(date,dateadd(wk, datediff(wk, 0, dateadd(YEAR, - 1, getdate())) - 1, 0) + 5 /* On 14/Feb/2021 modify +12 to +5 */) 
      then s.SELLINC else 0 end)   

     ,0)))*100 LastYrToDateVariancePercentage 

FROM [dbo].[CustomerReports] s 
WHERE s.BRN = 1 or s.BRN = 2 or s.BRN = 3 or s.BRN = 4 or s.BRN = 5 or s.SELLINC is null or s.SELLINC = '0' 
GROUP BY s.[CusNo], s.[Customer], s.ConcessionNo, s.ConcessionName 
order by YrToDateActual desc

当我在SSRS中运行此查询时，我得到下面的结果（这是正确的）。但显示所有数据。

如果我使用SELECT TOP 50秒。[CusNo]供应商（代码休息是一样的）然后我得到下面，

所以，请参阅突出部分在这两个图像。当我在SQL代码中添加Top 50时，第2张图片中不存在第1张图片中出现的几行（第3列）。

请参阅我对您的其他问题的回答：http://stackoverflow.com/a/42652957/1593840 – Will

答

试试这个：

With cte as (**your long query goes here**) 
Select Top(50) * From cte

答

排序顺序在查询是YrToDateActual desc，所以TOP 50是给你最高的50个YrToDateActual金额。您的SSRS报告是按供应商编号和名称对进行分组，但通过这两列中的任何一个，看起来都不是排序，所以我猜测这些供应商的订单在SSRS输出中有些随机。在这种情况下，我认为你永远无法获得原始SSRS输出的前50行。您可以做的最好的方法是您的SSRS输出（例如按供应商编号），然后将此类排序添加到您的查询中（order by Supplier, YrToDateActual desc）。将TOP 50添加到查询中会在前50个结果后切断新的排序SSRS输出。

谢谢，我现在会尝试 – user2331670

没有它的不工作仍然是同一个问题 – user2331670

答

你想在报告中看到什么？您是否希望根据总价值（例如Actual£Total）以及所有让步（按照您的第一张截图）查看前50名供应商？

如果是这样，您将需要按供应商的总量排列并选择排名为50或以下的供应商。

下面是一个非常简单的示例，可能有所帮助。在这里，我使用了SUM（）OVER（）而不是使用GROUP BY，但这意味着您可能想要根据需要将SELECT更改为SELECT DISTINCT，因为您将获得大量重复行。很明显，这并没有得到你冗长的案例陈述，但你应该能够很容易地适应它。

SELECT * FROM 
    (
     SELECT 
      x.* 
      , DENSE_RANK() OVER(ORDER BY SupplierTotal DESC) as SupplierRank 
      , DENSE_RANK() OVER(PARTITION BY SupplierID ORDER BY ConcessionTotal DESC) as ConcessionRank 
     FROM 
      ( SELECT DISTINCT 
      SupplierID, ConcessionID, MyOtherRequiredColumns, 
      SUM(myValue) OVER(PARTITION BY SupplierID) AS SupplierTotal, 
      SUM(myValue) OVER(PARTITION BY SupplierID, ConcessionID) AS ConcessionTotal 
      FROM MyTable 
     ) x 
    ) z 
    WHERE SupplierRank <= 50

这也将供应商排名中的让步，这可能是有用的..

你也将不得不改变你汇报，你必须总结的值了（例如99389在实际£列）。不要总结下面的行，将表达式更改为= FIRST（Fields！SupplierTotal。价值）

在sql server中选择前50行

相关推荐