基于日期时间一小时内的最高结果对列表进行排序 [英] Sort list based on top result in an hour from datetime

问题描述

您好。

我正在尝试查询以计算表格中每小时的总条目数，并仅显示日期的最高值。

For例如，今天型号A的结果为6个100个，7个200个，8个50个。在这种情况下，我需要显示的是一列中只有200个，而另一列中的时间（7）。

我能够显示最高值但我无法显示时间，如果我显示时间然后顶部不起作用...

样品：

包括小时数：

没有排名靠前 [ ^ ]

不包括小时数：

WithTop [ ^ ]

我真的很感谢你的帮助！



我尝试了什么：



这是我到目前为止尝试的查询（没有时间）

Hello.
I'm trying to do a query to calculate the total entries per hour in a table and show only the top value for the date.
For example, Today model A has results at 6 for 100 pcs, at 7 for 200 pcs and at 8 for 50 pcs. In this case what I need to show is only 200 pcs in a column and the Time (7) in another column.
I have been able to show the top values but I cannot show the time, if I show the time then the top does not work...
Samples:
Including Hours:
Without Top[^]
Not Including Hours:
WithTop[^]
I really appreciate your help!

What I have tried:

This is the query that I've tried so far (Without Hour)

SELECT *
FROM
       (
       select count(barcodenum)as count1, date1, Model_sap, model_customer, linea
       from   (  
                           SELECT FinishGoods.linea, FinishGoods.Model_sap, model.model_customer,FinishGoods.BarCodeNum,CONVERT(VARCHAR(10), scanprod.fecha, 102) as date1, scanprod.fecha
                           FROM FinishGoods  INNER JOIN model ON FinishGoods.Model_sap = model.model_ext INNER JOIN scanprod ON FinishGoods.BarCodeNum = scanprod.barcodenum  
                           WHERE (FinishGoods.linea <> '0') AND (FinishGoods.linea <> '') AND FinishGoods.scandt is not null  AND (scanprod.fecha >= '2016-03-18 06:00:00' AND scanprod.fecha <= '2016-03-22 23:59:59'  ) 
                     ) as tbl1 
       group by date1,  DATEPART(Hour, fecha), Model_sap, model_customer,linea--,hora1
       --order by model_sap, linea
) AS HourlySalesData
PIVOT( max(count1) FOR [date1] IN ([2016.03.18],[2016.03.19],[2016.03.20],[2016.03.21],[2016.03.22])) AS DatePivot
order by model_customer

这一小时包括：

And This one with hour included:

SELECT *
FROM
       (
       select count(barcodenum)as count1, date1, Model_sap, model_customer, linea, (convert(int, DATEPART(Hour, fecha))) as hour_
       from   (  
                           SELECT FinishGoods.linea, FinishGoods.Model_sap, model.model_customer,FinishGoods.BarCodeNum,CONVERT(VARCHAR(10), scanprod.fecha, 102) as date1, scanprod.fecha
                           FROM FinishGoods  INNER JOIN model ON FinishGoods.Model_sap = model.model_ext INNER JOIN scanprod ON FinishGoods.BarCodeNum = scanprod.barcodenum  
                           WHERE (FinishGoods.linea <> '0') AND (FinishGoods.linea <> '') AND FinishGoods.scandt is not null  AND (scanprod.fecha >= '2016-03-18 06:00:00' AND scanprod.fecha <= '2016-03-22 23:59:59'  ) 
                     ) as tbl1 
       group by date1,  DATEPART(Hour, fecha), Model_sap, model_customer,linea--,hora1
       --order by model_sap, linea
) AS HourlySalesData
PIVOT( max(count1) FOR [date1] IN ([2016.03.18],[2016.03.19],[2016.03.20],[2016.03.21],[2016.03.22])) AS DatePivot
order by model_customer

推荐答案

你知道

ROW_NUMBER() OVER(PARTITION BY date1 ORDER BY count1 ASC) 

它返回一个从1开始到x的整数，作为分区中的列并按顺序排序，如下所示

it returns an integer begining from 1 to x groupped as the collumns in partition by and orderd as in order by something like this

SELECT *
FROM
       (
       select count(FinishGoods.BarCodeNum) as count1, CONVERT(VARCHAR(10), scanprod.fecha, 102) as date1, (convert(int, DATEPART(Hour, fecha))) as hour_,
	   ROW_NUMBER() OVER(PARTITION BY CONVERT(VARCHAR(10), scanprod.fecha, 102) ORDER BY count(FinishGoods.BarCodeNum) ASC)  as rnum
       FROM FinishGoods  INNER JOIN model ON FinishGoods.Model_sap = model.model_ext INNER JOIN scanprod ON FinishGoods.BarCodeNum = scanprod.barcodenum  
       WHERE (FinishGoods.linea <> '0') AND (FinishGoods.linea <> '') AND FinishGoods.scandt is not null  AND (scanprod.fecha >= '2016-03-18 06:00:00' AND scanprod.fecha <= '2016-03-22 23:59:59'  ) 
       group by CONVERT(VARCHAR(10), scanprod.fecha, 102), DATEPART(Hour, fecha)
       --order by model_sap, linea
) AS HourlySalesData where rnum=1

它应该工作

ROW_NUMBER（Transact-SQL）| Microsoft Docs [ ^ ]

这篇关于基于日期时间一小时内的最高结果对列表进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！