通过asc Sql查询排序 [英] Order by asc Sql query

问题描述

0下来投票最爱





我有asc查询的sql命令，我必须订购包含整数值的varchar字段（我无法将数据类型更改为int，因为该表中已有大量数据更改可能会丢失数据）。分类结果如下：

1 2 3。 。 8 9 10 101 102 103。 。 109 11 110 111。 。 119 12



结果应为1 2 3。 。 8 9 10 11 12



查询是

0 down vote favorite


I have sql order by asc query and i have to order varchar field which contains integer value(I can't change the data type to int as there is already lots of data in that table changing may lose data). Result of the sorting is as following
1 2 3 . . 8 9 10 101 102 103 . . 109 11 110 111 . . 119 12

Result should be 1 2 3 . . 8 9 10 11 12

query is

dbAccess.execute("SELECT [id],[SNo],LetterSNO,[SubjectOfLetter],[FrmWhere],[WhomTosend],(Cast(Day(DateOfRecieve) as Varchar(3))+'/'+Cast(Month(DateOfRecieve) as Varchar(2))+'/'+Cast(Year(DateOfRecieve) as Varchar(4))) as DateOfRecieve ,[Remarks] ,[User_IPAddress], (Cast(Day(DateOfSending) as Varchar(3))+'/'+Cast(Month(DateOfSending) as Varchar(2))+'/'+Cast(Year(DateOfSending) as Varchar(4))) as DateOfSending FROM [MpscdrcTESTING].[dbo].[tbl_PostalEntry] where DateOfRecieve between '" + TextBox1.Text.Trim() + "' and  '" + TextBox2.Text.Trim() + "' order by SNo asc";

请帮助

please help

推荐答案

使用强制转换功能并通过以下方式更改查询。

use cast function and change the query by below.

dbAccess.execute("SELECT [id],CAST([SNo] as int) as SNumber,LetterSNO,[SubjectOfLetter],[FrmWhere],[WhomTosend],(Cast(Day(DateOfRecieve) as Varchar(3))+'/'+Cast(Month(DateOfRecieve) as Varchar(2))+'/'+Cast(Year(DateOfRecieve) as Varchar(4))) as DateOfRecieve ,[Remarks] ,[User_IPAddress], (Cast(Day(DateOfSending) as Varchar(3))+'/'+Cast(Month(DateOfSending) as Varchar(2))+'/'+Cast(Year(DateOfSending) as Varchar(4))) as DateOfSending FROM [MpscdrcTESTING].[dbo].[tbl_PostalEntry] where DateOfRecieve between '" + TextBox1.Text.Trim() + "' and  '" + TextBox2.Text.Trim() + "' order by 2 asc";

ORDER BY CAST(sNO as INT)

这将起作用

this will work

看到这个



http://stackoverflow.com/questions/ 16829663 / SQL服务器-query-varchar-data-sort-like-int [ ^ ]







dbAccess.execute（SELECT [id]，[SNo]，LetterSNO，[SubjectOfLetter]，[FrmWhere]，[WhomTosend]，（演员（Day（DateOfRecieve）为Varchar（3））+'/'+演员（月（ DateOfRecieve）as Varchar（2））+'/'+ Cast（Year（DateOfRecieve）as Varchar（4）））as DateOfRecieve，[Remarks]，[User_IPAddress]，（Cast（Day（DateOfSending）as Varchar（3）） +'/'+ Cast（Month（DateOfSending）为Varchar（2））+'/'+ Cast（Year（DateOfSending）为Varchar（4）））为DateOfSending FROM [MpscdrcTESTING]。[dbo]。[tbl_PostalEntry]其中DateOfRecieve介于'+ TextBox1.Text.Trim（）+'和'+ TextBox2.Text.Trim（）+'按CAST（[SNo] AS INT）命令asc;

See this

http://stackoverflow.com/questions/16829663/sql-server-query-varchar-data-sort-like-int[^]



dbAccess.execute("SELECT [id],[SNo],LetterSNO,[SubjectOfLetter],[FrmWhere],[WhomTosend],(Cast(Day(DateOfRecieve) as Varchar(3))+'/'+Cast(Month(DateOfRecieve) as Varchar(2))+'/'+Cast(Year(DateOfRecieve) as Varchar(4))) as DateOfRecieve ,[Remarks] ,[User_IPAddress], (Cast(Day(DateOfSending) as Varchar(3))+'/'+Cast(Month(DateOfSending) as Varchar(2))+'/'+Cast(Year(DateOfSending) as Varchar(4))) as DateOfSending FROM [MpscdrcTESTING].[dbo].[tbl_PostalEntry] where DateOfRecieve between '" + TextBox1.Text.Trim() + "' and '" + TextBox2.Text.Trim() + "' order by CAST([SNo] AS INT) asc";

这篇关于通过asc Sql查询排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！