从每个记录中选择最后一个值并绑定到Gridview [英] select last value from each Records and bind to Gridview

问题描述

我的数据库中的记录（带分隔符）如下所示：

100

100/1

100/2
101

101/1

102

102/1

102/2



我的问题是找到一种方法从每条记录中选择最后一个值并将它们绑定到Gridview（vb.net）。

I希望输出看起来如下：

100/2

101/1

102/2 ..



问候

Anas

The records (with delimiter) in my Database look like this:
100
100/1
100/2
101
101/1
102
102/1
102/2

My problem is to find a way to select the last values from each record and bind them to a Gridview (vb.net).
I want the output to look like the following:
100/2
101/1
102/2 ..

Regards
Anas

推荐答案

问题在于100/1是一个基于字符串的值，因此它将使用基于字符串的比较，而不是数字。你可以做到这一点，但它在SQL中非常繁琐 - 我会用一种正确的编程语言来实现它非常简单。

The problem with that is that "100/1" is a string based value, so it will work with a string based comparison, rather than numeric. You can do it, but it's pretty cumbersome in SQL - I'd do it in a "proper" programing language where it's pretty simple.

SELECT MAX(ID) FROM MyTable
GROUP BY SUBSTRING(ID, 0, CASE WHEN CHARINDEX('/', ID) > 0
                               THEN CHARINDEX('/', ID)
                               ELSE LEN(ID)
                          END)

如果可以更改你的数据库所以它是两个领域，它变成了al更简单！

If you can change your DB so it's two fields, it becomes a lot easier!

或者，您可以使用 REPLACE [ ^ ]功能。

Alternativelly, you can use REPLACE[^] function.

DECLARE @tmp TABLE (StringNumbers VARCHAR(10))

INSERT INTO @tmp (StringNumbers)
VALUES('100'), ('100/1'),
('100/2'), ('101'),
('101/1'), ('102'),
('102/1'), ('102/2'),
('103'), ('104')


SELECT StringNumbers, CONVERT(FLOAT, REPLACE(StringNumbers ,'/','.')) AS MyNumber
FROM @tmp
ORDER BY CONVERT(FLOAT, REPLACE(StringNumbers ,'/','.'))

想一想并根据需要更改代码;）

Think of it and change the code to your needs ;)

最后我找到了解决方案



SELECT col1，col2，col3 FROM Tableone WHERE

Id =（SELECT MAX（ID）FROM Tableone i WHERE i.empname = Tableone.Empname）

Finally i find the Solution that is

SELECT col1,col2,col3 FROM Tableone WHERE
Id=(SELECT MAX(ID) FROM Tableone i WHERE i.empname=Tableone.Empname)

这篇关于从每个记录中选择最后一个值并绑定到Gridview的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！