如何比较数据表中的值 [英] How to compare values from datatable

问题描述

您好我的代码是

Hi my code is

If LineNo = Family Then
                        If FailCount >= 500 Then 'This is for the sum of that line 
                            'Get the highest percentage by taking Count(*) / total count * 100
                            For Each Row As DataRow In tblRawTesterShutdown.Rows
                                With objShutdownReport
                                    .ShutdownType = _ShutdownType
                                    .TesterID = LineNo
                                    .QuadID = "0"
                                    .E_Code = Row.Item("E_FIELD").ToString.Trim
                                    .F_Code = Row("F_FIELD").ToString.Trim
                                    .G_Code = Row("G_FIELD").ToString.Trim
                                    .Status = Row("HSA_STATUS").ToString.Trim
                                    .Family = Row("FAMILY").ToString.Trim
                                    .Count = CInt(Row.Item("TOTAL"))
                                    .StartTime = StartTime
                                    .Timestamp = NowTime
                                    .CACHE_FAMILY = Row.Item("CACHE_FAMILY").ToString.Trim

                                    If Percentage =  Then 
'<--- At this part it will get percentage value actually, so here it should find
'the top value and minus the second value. Anywan can help how to do the looping
'here. sample data is shown below.
                                       
                                    Else
                                        
                                    End If
                                End With
                            Next
                        Else
                            MsgBox("No shutdown")
                        End If
                    End If 'end here

这是我的代码。实际上，我需要一个帮助来获得特定行数的最高百分比，减去第二个百分比。我对逻辑很困惑。因此，对于一个示例，第一个百分比是0.006应该减去0.004，因为它应该忽略0.001。有了差异，那么它应该再次减去0.004才能找到差异。请帮助我。提前致谢。

This is my code. I need an help actually to get the top percentage from particular row number and minus the second top of percentage. I quite confuse with the logic. So for an example the first percentage is 0.006 should minus with 0.004 since it should ignore 0.001. With the difference then it should minus again with 0.004 to find the difference. Pls help me. Thanks in advance.

LINE_NO E_FIELD F_FIELD G_FIELD FAMILY HSA_STATUS CACHE_FAMILYCountOfLineNo TotalOfLineNo %	
23059B	00353	00418	00684	SUMMIT	   S	   WER	              3       526     0.006
64006M	00353	00418	00684	SUMMIT	   R	   WER	              2	      2838    0.001
23059B	00353	00418	00758	SUMMIT	   S	   WER	              2	      526     0.004

[edit]代码块添加，Spurious bold删除 - OriginalGriff [/ edit]

[edit]Code block added, Spurious bold removed - OriginalGriff[/edit]

推荐答案

我有点困惑......因为我很好记住 [ ^ ]您正在使用MS SQL服务器来存储数据。

比较服务器端的数据要快于比较客户端的数据。



看看下面的例子：

I''m little confused... As i good remember[^] you are using MS SQL server to store data.
Comparing data on server side is much faster than comparing data on client side.

Have a look at below example:

DECLARE @tbl TABLE(LINE_NO VARCHAR(10), TotalOfLineNo INT, Percentage REAL)

INSERT INTO @tbl (LINE_NO, TotalOfLineNo, Percentage)
VALUES('23059B', 526, 0.006)
INSERT INTO @tbl (LINE_NO, TotalOfLineNo, Percentage)
VALUES('64006M', 2838, 0.001)
INSERT INTO @tbl (LINE_NO, TotalOfLineNo, Percentage)
VALUES('23059B', 526, 0.004)

SELECT t1.LINE_NO, t1.TotalOfLineNo, COALESCE(t1.P1,0) AS P1, COALESCE(t2.P2,0) AS P2, COALESCE(CONVERT(REAL, t1.P1 - t2.P2),0) AS PDiff
FROM (
	SELECT LINE_NO, TotalOfLineNo, Percentage AS P1, ROW_NUMBER() OVER (ORDER BY Percentage DESC) + 1 AS RowNo
	FROM @tbl) AS t1 LEFT JOIN (
			SELECT LINE_NO, TotalOfLineNo, Percentage AS P2, ROW_NUMBER() OVER (ORDER BY Percentage DESC) AS RowNo
			FROM @tbl) AS t2 ON t1.RowNo = t2.RowNo

结果：

result:

LINE_NO Total.. P1      P2      PDiff
23059B	526	0,006	0,004	0,002
23059B	526	0,004	0,001	0,003
64006M	2838	0,001	0	0

这就是你想要实现的目标吗？

Is that what you want to achieve?

这篇关于如何比较数据表中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！