如何对每个列包含字符串和数字的数据表进行排序 [英] how to sort datatable which each column contains both string and numbers
问题描述
嗨所有
i想要对每个列包含字符串和数字的数据网格进行排序。
i希望它按数字顺序排序,并将字符串作为数据表中的底部字符串。
dataview.sort = columnName +DESC可以让我排序它按自然顺序排列。
例如,quantitiy列排序为1,10,100,1000,2,20,200,N / A.这不是我想要实现的
。
有没有人对此有任何想法
谢谢
star
这是一个解决方案,但它严格来说是TSQL(datatable?)和c#4.0将是前端:
USE [cpqaAnswers]
GO
CREATE TABLE [cpqa]。[tbl_CS_zeropadSolution003]
(
P_Id int,
amount varchar(50)
)
INSERT INTO [cpqa]。[tbl_CS_zeropadSolution003]
VALUES(1,'2340'),
(2, '4568'),
(3,'10000')
SELECT * FROM [cpqa]。[tbl_CS_zeropadSolution003] - 麻烦ints为chars
SELECT * FROM [cpqa]。[tbl_CS_zeropadSolution003] ORDER BY [金额] - 不对(原文如此)
问题有多少个地方......任意选择一个数字,这里六(6)......但它并不重要......令人反感的那种:
SELECT [P_Id],LEFT(REPLICATE('0', 6),6 - LEN([金额]))+ [金额] AS [零padded_amount] FROM [cpqa]。[tbl_CS_zeropadSolution003]
/ *
结果:
P_Id zeropadded_amount
1 002340
2 004568
3 010000
* /
这是好的排序:
SELECT [P_Id],LEFT(REPLICATE(' 0',6),6 - LEN([金额]))+ [金额] AS [zeropadded_amount] FROM [cpqa]。[tbl_CS_zeropadSolution003] ORDER BY [zeropadded_amount] DESC
/ *
结果:
P_Id zeropadded_amount
~~~~~~~~~~~~~~~~~~~~~~~~
3 010000
2 004568
1 002340
* /
// 假设您在ItemNo列中有混合文本和数字
// < span class =code-comment>创建表时,添加另一列将用于对此列进行排序
table.Columns.Add( SortKeyItemNo);
// 在填充表格时设置foreach循环中排序键的值
row [ SortKeyItemNo] = MakeSortKey(item [ ItemNo]);
// 创建排序键的例程
string MakeSortKey( string value )
{
string key = value ; // 初始化传入值的键
Int32 num = 0 ;
bool result = Int32 .TryParse( value , out num);
if (result)
{
// 使用填充值覆盖传入值
// 前缀为零的前缀编号,直到数字包含6个字符
key = num.ToString()。PadLeft( 6 ,' 0');
}
返回键;
}
我错了,你必须修改datagrid助手类。我认为网络形式不是胜利形式,我的错误。
// 在datagrid帮助器类中,在设置columnName变量之后但在设置dataView.Sort属性之前,在代码上方添加此行。
if (table.Columns.Contains( SortKeyItemNo))
{
columnName = SortKey + columnName;
}
将它放在datagrid帮助器类中,[...放在这里......]文本是......
columnName = ;
// 获取所点击列的名称。
if (dataGrid.TableStyles.Count!= 0 )
{
foreach (DataGridTableStyle item in dataGrid.TableStyles)
{
if (item.MappingName == dataTable.TableName)
{
columnName = item.GridColumnStyles [hitTest.Column] .MappingName;
}
}
}
else
{
columnName = dataTable.Columns [hitTest .COLUMN] .ColumnName;
}
[...放在这里......]
// 如果DataView的sort属性已经是当前的
// < span class =code-comment>列名,按降序对该列进行排序。
// 否则,按列名称排序。
if ( String .IsNullOrEmpty(dataView.Sort))
{
dataView.Sort = columnName + DESC;
}
else if (dataView.Sort == columnName + ASC)
{
dataView.Sort = columnName + DESC;
}
else dataView.Sort = columnName + ASC;
hi all
i would like to sort the datagrid which each column contains both string and numbers.
i want it sorted in numeric order and make the string as the bottom ones in the datatable.
The dataview.sort=columnName+"DESC" can just let me sort it in natural order.
for example ,the quantitiy column sort as 1,10,100,1000,2,20,200,N/A. And this is not what i
want to achieve.
Does anyone have any idea about this
thanks
star
Here is one solution but it's strictly TSQL ("datatable"?) and c#4.0 would be the frontend:
USE [cpqaAnswers] GO CREATE TABLE [cpqa].[tbl_CS_zeropadSolution003] ( P_Id int, amount varchar(50) ) INSERT INTO [cpqa].[tbl_CS_zeropadSolution003] VALUES(1, '2340'), (2, '4568'), (3, '10000') SELECT * FROM [cpqa].[tbl_CS_zeropadSolution003] -- trouble with "ints" as "chars" SELECT * FROM [cpqa].[tbl_CS_zeropadSolution003] ORDER BY [amount] -- not right (sic)
How many places is the question ... arbitrarily pick a number, here six (6) ... but it doesn't really matter ... the objectionable sort too:
SELECT [P_Id], LEFT(REPLICATE('0', 6), 6 - LEN([amount])) + [amount] AS [zeropadded_amount] FROM [cpqa].[tbl_CS_zeropadSolution003] /* result: P_Id zeropadded_amount 1 002340 2 004568 3 010000 */
Here's the good sort:
SELECT [P_Id], LEFT(REPLICATE('0', 6), 6 - LEN([amount])) + [amount] AS [zeropadded_amount] FROM [cpqa].[tbl_CS_zeropadSolution003] ORDER BY [zeropadded_amount] DESC /* result: P_Id zeropadded_amount ~~~~~~~~~~~~~~~~~~~~~~~~ 3 010000 2 004568 1 002340 */
// let's say you have mixed text and numbers in the ItemNo column // when you create your table, add another column that will be used to sort for this column table.Columns.Add("SortKeyItemNo");
// set the value for the sort key in your foreach loop when you populate your table row["SortKeyItemNo"] = MakeSortKey(item["ItemNo"]);
// routine to create sort key string MakeSortKey(string value) { string key = value; // initialize key to incoming value Int32 num = 0; bool result = Int32.TryParse(value, out num); if (result) { // override incoming value with padding value // prefixes leading zeros to number, until the number contains 6 characters key = num.ToString().PadLeft(6, '0'); } return key; }
I was wrong, you will have to modify the datagrid helper class. I was thinking web form not win form, my mistake.
// in the datagrid helper class, add this line above the code after you set the columnName variable but before you set the dataView.Sort property. if(table.Columns.Contains("SortKeyItemNo")) { columnName = "SortKey" + columnName; }
Place it in the datagrid helper class, where the [...place here...] text is...
columnName = ""; // Get the name of the column that was clicked. if (dataGrid.TableStyles.Count != 0) { foreach (DataGridTableStyle item in dataGrid.TableStyles) { if (item.MappingName == dataTable.TableName) { columnName = item.GridColumnStyles[hitTest.Column].MappingName; } } } else { columnName = dataTable.Columns[hitTest.Column].ColumnName; } [...place here...] // If the sort property of the DataView is already the current // column name, sort that column in descending order. // Otherwise, sort on the column name. if (String.IsNullOrEmpty(dataView.Sort)) { dataView.Sort = columnName + " DESC"; } else if (dataView.Sort == columnName + " ASC") { dataView.Sort = columnName + " DESC"; } else dataView.Sort = columnName + " ASC";
这篇关于如何对每个列包含字符串和数字的数据表进行排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!