我如何从数据表中获得最高记录 [英] How do i get the highest record from datatable

问题描述

我的数据表中有以下记录（单列）

 S00118-JR10-B-NO-R-JS-26 
 S00118- JR10-B-NO-R-JS-22 
 S00118-JR10-B-NO-R-JS-24 
 S00201-0110-B-NO-R-ST-16 
 S00201 -0110-B-NO-R-ST-15 
 S00201-JRSR-B-RE-R-TS-20 
 S00118-0110-B-NO-R-JK-46 
 S00201-0104-M-NO-R-SH-11 
 S00118-0110-B-NO-R-JK-34 

我想要最后一个最高数字的明确记录如下

 S00118-JR10-B-NO-R-JS-26 
 S00201- 0110-B-NO-R-ST-16 
 S00201-JRSR-B-RE-R-TS-20 
 S00118-0110-B-NO-R-JK-46 
 S00201 -0104-M-NO-R-SH-11 

解决方案

如果它们都是两位数，那么试试：

  SELECT  MAX（MyColumn） FROM  MyTable 
  GROUP   BY  SUBSTRING（MyColumn， 1  ， 21 ）

如果不是，那么它就变成了PITA，因为字符串的默认排序顺序是基于第一个的差异不匹配的字符对 - 不是数字值。





想一想，不是那么多的PITA ：

  SELECT 前缀+ CAST（MAX（Val） AS   NVARCHAR ）
  FROM  
（ SELECT  SUBSTRING（MyColumn， 1 ， 22 ） AS 前缀，CAST（SUBSTRING（MyColumn， 23 ， 9999  ） AS   INT ） AS  Val  FROM  MyTable）a 
  GROUP   BY 前缀

[/ edit]

 List< string> filtered = test.OrderByDescending（x = >  Convert.ToInt32（x.Substring（x.LastIndexOf（   - ）+  1 ）））。GroupBy（x = >  x.Substring（ 0 ，x.LastIndexOf（   - ）））。选择（x = >  x.First（））。ToList（）; 

如果这对您不起作用，请尝试自行修复。

这是另一种方式使它成为



我用该列创建你的桌子。

 SELECT Mycolumn来自MyTable; 

 Mycolumn 
 S00118-JR10-B-NO-R-JS-26 
 S00118-JR10-B-NO-R-JS-22 
 S00118-JR10-B-NO-R-JS-24 
 S00201-0110-B-NO-R-ST- 16 
 S00201-0110-B-NO-R-ST-15 
 S00201-JRSR-B-RE-R-TS-20 
 S00118-0110-B-NO-R-JK -46 
 S00201-0104-M-NO-R-SH-11 
 S00118-0110-B-NO-R-JK-34 

这是我用来通过CTE获得结果的conde。

 WITH CTE（LEFTSTR， RIGHTSTR，Dupplicate）
 AS 
（SELECT DISTINCT 
 LEFT（Mycolumn，LEN（Mycolumn） - （CHARINDEX（' - '，REVERSE（Mycolumn）） -  1））AS LEFTSTR，
 RIGHT（Mycolumn，（CHARINDEX（' - '，REVERSE（Mycolumn）） -  1））AS RIGHTSTR，
 ROW_NUMBER（）OVER（左边分区（Mycolumn，LEN（Mycolumn） - （CHARINDEX（' - ） '，REVERSE（Mycolumn）） -  1））
 ORDER BY RIGHT（Mycolumn，（CHARINDEX（' - '，REVERSE（Mycolumn）） -  1））DESC）AS Dupplicate 
 FROM MyTable）
 
 SELECT（LEFTSTR + RIGHTSTR）AS MyColumn FROM CTE 
 WHERE Dupplicate = 1; 

这里结果是：

 MyColumn 
 S00118-0110-B-NO-R-JK-46 
 S00118- JR10-B-NO-R-JS-26 
 S00201-0104-M-NO-R-SH-11 
 S00201-0110-B-NO-R-ST-16 
 S00201 -JRSR-B-RE-R-TS-20 

I have below records in my datatable (in single column)

S00118-JR10-B-NO-R-JS-26
S00118-JR10-B-NO-R-JS-22
S00118-JR10-B-NO-R-JS-24
S00201-0110-B-NO-R-ST-16
S00201-0110-B-NO-R-ST-15
S00201-JRSR-B-RE-R-TS-20
S00118-0110-B-NO-R-JK-46
S00201-0104-M-NO-R-SH-11
S00118-0110-B-NO-R-JK-34

I want distinct record with higest number at the end as follows

S00118-JR10-B-NO-R-JS-26
S00201-0110-B-NO-R-ST-16
S00201-JRSR-B-RE-R-TS-20
S00118-0110-B-NO-R-JK-46
S00201-0104-M-NO-R-SH-11

解决方案

If they are all two digit, then try:

SELECT MAX(MyColumn) FROM MyTable
GROUP BY SUBSTRING(MyColumn, 1, 21)

If they aren't, then it becomes a PITA because the default sort order for strings is based on the difference between the first character pair that don't match - not a numeric value.

[edit]
Thinking about it, not that much of a PITA:

SELECT  Prefix + CAST(MAX(Val) AS NVARCHAR)  
FROM 
   (SELECT SUBSTRING(MyColumn, 1, 22) AS Prefix, CAST(SUBSTRING(MyColumn, 23, 9999) AS INT) AS Val FROM MyTable) a
GROUP BY Prefix

[/edit]

You can do it like this:

List<string> filtered = test.OrderByDescending(x => Convert.ToInt32(x.Substring(x.LastIndexOf("-") + 1))).GroupBy(x => x.Substring(0, x.LastIndexOf("-"))).Select(x => x.First()).ToList();

If this does not work for you, try fixing is yourself.

Here is other way to made it

I create your table with that column.

SELECT Mycolumn FROM MyTable;

Mycolumn
S00118-JR10-B-NO-R-JS-26
S00118-JR10-B-NO-R-JS-22
S00118-JR10-B-NO-R-JS-24
S00201-0110-B-NO-R-ST-16
S00201-0110-B-NO-R-ST-15
S00201-JRSR-B-RE-R-TS-20
S00118-0110-B-NO-R-JK-46
S00201-0104-M-NO-R-SH-11
S00118-0110-B-NO-R-JK-34

And here is the conde I use to get your result requsted with a CTE.

WITH CTE(LEFTSTR, RIGHTSTR, Dupplicate)
AS
(SELECT DISTINCT
		LEFT(Mycolumn,LEN(Mycolumn)-(CHARINDEX('-',REVERSE(Mycolumn))-1))AS LEFTSTR,
		RIGHT(Mycolumn,(CHARINDEX('-',REVERSE(Mycolumn))-1))AS RIGHTSTR,
		ROW_NUMBER() OVER(PARTITION BY LEFT(Mycolumn,LEN(Mycolumn)-(CHARINDEX('-',REVERSE(Mycolumn))-1)) 
						  ORDER BY RIGHT(Mycolumn,(CHARINDEX('-',REVERSE(Mycolumn))-1))DESC)AS Dupplicate
FROM MyTable)

SELECT (LEFTSTR + RIGHTSTR)AS MyColumn  FROM CTE
WHERE Dupplicate = 1;

And Here is the result:

MyColumn
S00118-0110-B-NO-R-JK-46
S00118-JR10-B-NO-R-JS-26
S00201-0104-M-NO-R-SH-11
S00201-0110-B-NO-R-ST-16
S00201-JRSR-B-RE-R-TS-20

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