Sql Server查询varchar排序 [英] Sql Server Query varchar sort

问题描述

你好..



i有一张桌子像



CREATE TABLE table_name

（

P_Id int，

金额varchar（50）

）



---------------------------



数据类似



Id金额

--------------------

1 2340

2 4568

3 10000



现在我想按数量对表格进行排序问题是金额是 varchar 所以它像这样排序表



Id金额

------ --------------

3 10000

1 2340

2 4568



但我想要这样



Id金额

------ --------------

3 10000

2 4568

1 2340





任何帮助请。 。

Hello..

i have one table like

CREATE TABLE table_name
(
P_Id int,
amount varchar(50)
)

---------------------------

Data Like

Id amount
--------------------
1 2340
2 4568
3 10000

Now i want to sort table by amount but one problem is amount is varchar so it sort table like this

Id amount
--------------------
3 10000
1 2340
2 4568

but i want like this

Id amount
--------------------
3 10000
2 4568
1 2340


any help please. .

推荐答案

糟糕的桌面设计！请重新设计表格。数值必须是数字而不是文本！



请参考此链接： http://msdn.microsoft.com/en-us/library/ms187752.aspx [ ^ ]







请按照以下步骤更正您的桌面设计。

Bad table design! Please, re-design table. Numeric values must be numeric not text!

Refer this link: http://msdn.microsoft.com/en-us/library/ms187752.aspx[^]



Please, follow these steps to correct your table design.

ALTER TABLE table_name
(
    ADD COLUMN namount INT
)

UPDATE t1 SET t1.namount = CONVERT(INT, t2.amount)
FROM table_name AS t1 INNER JOIN table_name AS t2 ON t1.P_Id = t2.P_Id

ALTER TABLE table_name
(
    DROP COLUMN amount
)

SELECT *
FROM table_name
ORDER BY namount

如果您不想升级表架构，请使用：

If you don''t want to upgrade your table schema, please, use this:

SELECT *
FROM table_name
ORDER BY CONVERT(INT,amount) DESC

[/ EDIT]

[/EDIT]

select * from tablename order by convert(int,amount)

如果工作正常，请标记答案

please mark answer if its work correctly

这是最令人难忘的解决方案：

Here is the most memorable solution:

USE [cpqaAnswers]
GO
CREATE TABLE [cpqa].[tbl_AP_zeropadSolution003]
(
	P_Id int,
		amount varchar(50)
)
INSERT INTO [cpqa].[tbl_AP_zeropadSolution003]
	VALUES(1, ''2340''),
	(2, ''4568''),
	(3, ''10000'')
	
SELECT * FROM [cpqa].[tbl_AP_zeropadSolution003] -- trouble with "ints" as "chars"
SELECT * FROM [cpqa].[tbl_AP_zeropadSolution003] ORDER BY [amount] -- not right (sic)

问题有多少个地方......任意选择一个数字，这里有六（6）......但这并不重要......令人反感的是：

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_AP_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_AP_zeropadSolution003] ORDER BY [zeropadded_amount] DESC
/*
 result:

 P_Id	zeropadded_amount
 ~~~~~~~~~~~~~~~~~~~~~~~~
    3	           010000
    2	           004568
    1	           002340
*/

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