从SQL表中获取最大值行 [英] Get maximum value row from SQL table
问题描述
我正在为项目之一编写代码。所以 非常好。现在我触及 get 特定行,其中 value 是来自列OeRT的最大。 下面我的表,此 一个转移数据于8月5日输入。当我使用OeDate查询时,OeShift& OeLoct,我在 DataSet Visualizer中获得 表。但我希望 最大值的行记录 in OeRT专栏。在此 case 我只想要行号 800053 , 记录的OeRT 值 131
。
我的尝试:
我正在为一个项目编写代码。所以这么好。现在我开始从列OeRT获取值为最大值的特定行。下面是我的表,这是8月5日输入的一个班次数据。当我用OeDate查询时,OeShift& OeLoct,我在DataSet Visualizer中获取此表。但是我想要在OeRT列中记录的最大值的行。在这种情况下,我只想要行号800053,记录OeRT值131.P1,帮助我只获得一个包含OeRT最大值的值。我的存储过程也在下面输入。
OeID OeDate OeShift OeLoct OePOT OeSPT OeUPT OeRT
800052 05- 08-2016 12:00:00 Shift_A 4 480 425 212 53
800053 05-08-2016 12:00:00 Shift_A 4 480 425 212 131
800054 05- 08-2016 12:00:00 Shift_A 4 480 425 212 123
800055 05-08-2016 12:00:00 Shift_A 4 480 425 212 108
800056 05- 08-2016 12:00:00 Shift_A 4 480 425 212 123
存储过程
USE [ TPM]
GO
/ ******对象:StoredProcedure [dbo]。[sp_OeeEntryDisplay]脚本日期:8/25/2016 3:15:33 PM ****** /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo]。[sp_OeeEntryDisplay]
@sCond nvarchar(max)
as
begin
声明@statement varchar(max);
set @statement ='select OeID,OeDate,OeShift,OeLoct,OePOT,OeSP T,OeUPT,OeRT,
来自TPM_OeeEntry
其中1 = 1'+ @sCond
执行(@statement);
结束
您可以使用临时表来实现目标。
ALTER proc [dbo]。 [sp_OeeEntryDisplay]
AS
创建 表 #Temp
(
SN int 身份,
OeID varchar ( 50 ),
OeDate 日期时间,
OeShift varchar ( 50 ),
OeLoct varchar ( 50 ),
OePOT varchar ( 50 ),
OeSPT varchar ( 50 ),
OeUPT varchar ( 50 ),
OeRT varchar ( 50 ),
)
将 插入 #temp(OeID,OeDate,OeShift,OeLoct, OePOT,OeSPT,OeUPT,OeRT)
选择 OeID,OeDate,OeShift,OeLoct,OePOT,OeSPT,OeUPT,OeRT,
来自 TPM_OeeEntry 订单 按 OeRT desc
选择 TOP 1 * 来自 #temp;
Drop table #temp;
希望这可以解决您的问题。
您也可以使用Common table Expression,如下所示: -
WITH T AS
(
SELECT *,
DENSE_RANK() OVER ( ORDER BY OeRT Desc ) AS Rnk
FROM TPM_OeeEntry
)
SELECT *
FROM T
WHERE Rnk = 1;
I am writing code for one of project. So for so good. Now I struck up to get particular row which the value is maximum from column OeRT. Below is my table, this is one shift data entered on 5th Aug. When I query with OeDate, OeShift & OeLoct, I am getting this table in DataSet Visualizer. But I want the row which is maximum value which recorded in OeRT column. In this case I want only row number 800053, which is recorded OeRT value 131
.
What I have tried:
I am writing code for one of project. So for so good. Now I struck up to get particular row which the value is maximum from column OeRT. Below is my table, this is one shift data entered on 5th Aug. When I query with OeDate, OeShift & OeLoct, I am getting this table in DataSet Visualizer. But I want the row which is maximum value which recorded in OeRT column. In this case I want only row number 800053, which is recorded OeRT value 131. Pl, help me to get only one which contains maximum value in OeRT. My stored procedure also entered below.
OeID OeDate OeShift OeLoct OePOT OeSPT OeUPT OeRT
800052 05-08-2016 12:00:00 Shift_A 4 480 425 212 53
800053 05-08-2016 12:00:00 Shift_A 4 480 425 212 131
800054 05-08-2016 12:00:00 Shift_A 4 480 425 212 123
800055 05-08-2016 12:00:00 Shift_A 4 480 425 212 108
800056 05-08-2016 12:00:00 Shift_A 4 480 425 212 123
Stored Procedure
USE [TPM]
GO
/****** Object: StoredProcedure [dbo].[sp_OeeEntryDisplay] Script Date: 8/25/2016 3:15:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[sp_OeeEntryDisplay]
@sCond nvarchar(max)
as
begin
declare @statement varchar(max);
set @statement = 'select OeID,OeDate,OeShift,OeLoct,OePOT,OeSPT,OeUPT,OeRT,
from TPM_OeeEntry
where 1=1' + @sCond
execute(@statement);
end
You can use Temporary tables to achieve your goal.
ALTER proc [dbo].[sp_OeeEntryDisplay] AS Create Table #Temp ( SN int Identity, OeID varchar(50), OeDate Datetime, OeShift varchar(50), OeLoct varchar(50), OePOT varchar(50), OeSPT varchar(50), OeUPT varchar(50), OeRT varchar(50), ) Insert into #temp(OeID,OeDate,OeShift,OeLoct,OePOT,OeSPT,OeUPT ,OeRT ) select OeID,OeDate,OeShift,OeLoct,OePOT,OeSPT,OeUPT,OeRT, from TPM_OeeEntry order by OeRT desc select TOP 1 * from #temp; Drop table #temp;
Hope this solves your problem.
you can also use Common table Expression for this as follows:-
WITH T AS ( SELECT *, DENSE_RANK() OVER (ORDER BY OeRT Desc) AS Rnk FROM TPM_OeeEntry ) SELECT * FROM T WHERE Rnk=1;
这篇关于从SQL表中获取最大值行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!