sql中的查询顺序问题 [英] problem with order by query in sql

问题描述

朋友我有桌子
列名称是纸箱
输出如下:当我通过纸箱asc发出订单时

hi friend i have table
columns name is carton
output is comming such as:when i given order by carton asc

1-11
1-14
1-5
15-19
20-30
2-2
31-31
5-4
5-4
5-4
5-4
5-4
6-5

我想要这些记录
订购
喜欢
1-5
1-14
2-2

我会尝试按asc语句进行排序，但会显示给出的这些值
但对记录没有影响
因为这些记录选择了第一个值
多数民众赞成在问题
plz help

i want to these records
order by
like
1-5
1-14
2-2

i''ll try order by asc statement but it show these value that is given
but there is no effect on record
because these record pick 1st value
thats the problem
plz help

推荐答案

如果[carton]字段的类型是文本，则ORDER BY子句 [ ^ ]将其视为文本，而不是数字.

您需要用-"分割数据.如何?

If the type of [carton] field is text, ORDER BY clause[^] treats it as a text, not numbers.

You need to Split your data by ''-''. How?

SELECT  [carton], CONVERT(INT,SUBSTRING([carton], 1, CHARINDEX('-',[carton])-1)) AS [c1], 
		CONVERT(INT,SUBSTRING([carton], CHARINDEX('-',[carton])+1 , LEN([carton])-CHARINDEX('-',[carton])+1)) AS [c2]
FROM YOUR_TABLE_NAME
ORDER BY [c1], [c2], [carton]

我认为最好的解决方案是将信息分成两个单独的列.一列中的第一个数字，另一列中的第二个数字.

我建议这样做是因为在数据库中，您永远不应将不同的数据连接到单个列中.您始终可以在显示数据时对其进行格式化.

如果需要使用当前数据结构，则可以按order by子句拆分元素.像这样的东西:

In my opinion the best solution would be to break the information into two separate columns. The first number in one column and the second number in another column.

I''m suggesting this because in the database you should never concatenate different data into a single column. You can always format the data when it''s displayed.

If you need to use the current data structure, you can split the elements in the order by clause. Something like:

...
ORDER BY CAST( SUBSTRING( fieldname, 1, CHARINDEX( fieldname, '-')) AS int),
         CAST( SUBSTRING( fieldname, CHARINDEX( fieldname, '-'), 9999) AS int)

如果要使用C# with LINQ排序数据，则可以使用以下步骤

If the data is to be ordered using C# with LINQ then following procedure can be used

//If the above data is in cartonData DataTable in the Carton Column

var orderedCartonData = cartonData.AsEnumerable()
		.OrderBy (d => d.Field<string>("Carton").Split('-')
		.Aggregate ("", (x,y) => x.PadLeft(5,'0') + y.PadLeft(5,'0')));
//If the data is in a List then
List<string> data = new List<string>(){
	"1-11","1-14","1-5","15-19","20-30","2-2",
	"31-31","5-4","5-4","5-4","5-4","5-4","6-5"	};
var orderedData = data.OrderBy (d => d.Split('-')
	.Aggregate ("",(x,y) => x.PadLeft(5,'0')+y.PadLeft(5,'0')));

//The contents of orderedCartonData
//1-5 
//1-11 
//1-14 
//2-2 
//5-4 
//5-4 
//5-4 
//5-4 
//5-4 
//6-5 
//15-19 
//20-30 
//31-31 

这里的主要要点是，对于Sort 项1-5、1-14、2-2，即使将它们分成两个单独的数据项，当按文本排序时，14仍将排在5之前.为了克服这个问题，如上面的查询中所示，可以为数据加上0000100005, 0000100014, 0000200002之类的0前缀.

The main point here is that, to Sort the items as 1-5, 1-14, 2-2, even if we split these in to two separate data items, still 14 will come first before 5 when sorted as text. To overcome this the data can be prefixed with 0s like 0000100005, 0000100014, 0000200002 as shown in the above query.

这篇关于sql中的查询顺序问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！