我有一个数据库的表m,结构类型如下id - int(主键,自动)
price - varchar(MAX)我这里有几条数据id price
1 200
2 30.00
3 15元
4 87.0元
5 98.00元我现在想读出这些数据,并这些数据是的排列顺序是按price从小到大排列的,具体如下id price
3 15元
2 30.00
4 87.0元
5 98.00元
1 200请问我在无法改变price类型的状态下,如何做呢?
price - varchar(MAX)我这里有几条数据id price
1 200
2 30.00
3 15元
4 87.0元
5 98.00元我现在想读出这些数据,并这些数据是的排列顺序是按price从小到大排列的,具体如下id price
3 15元
2 30.00
4 87.0元
5 98.00元
1 200请问我在无法改变price类型的状态下,如何做呢?
order by cast(replace(price,'元','') as dec(18,2))
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-09 11:32:49
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[price] varchar(7))
insert [tb]
select 1,'200' union all
select 2,'30.00' union all
select 3,'15元' union all
select 4,'87.0元' union all
select 5,'98.00元'
--------------开始查询--------------------------select * from [tb] order by cast(replace(price,'元','') as dec(18,2))
----------------结果----------------------------
/* id price
----------- -------
3 15元
2 30.00
4 87.0元
5 98.00元
1 200(5 行受影响)*/
go
create table [tb]([id] int,[price] varchar(7))
insert [tb]
select 1,'200' union all
select 2,'30.00' union all
select 3,'15元' union all
select 4,'87.0元' union all
select 5,'98.00元'
select * from tb
order by cast(replace(price,'元','') as dec(18,2))/**
id price
----------- -------
3 15元
2 30.00
4 87.0元
5 98.00元
1 200(所影响的行数为 5 行)
**/
go
create table [tb]([id] int,[price] varchar(7))
insert [tb]
select 1,'200' union all
select 2,'30.00' union all
select 3,'15元' union all
select 4,'87.0元' union all
select 5,'98.00元'SELECT * FROM TB order by cast(replace(price,'元','') as MONEY)
(所影响的行数为 5 行)id price
----------- -------
3 15元
2 30.00
4 87.0元
5 98.00元
1 200(所影响的行数为 5 行)
(
id int identity(1,1) primary key,
price varchar(30)
)
insert into #RR select '200'
insert into #RR select '30.00'
insert into #RR select '15元'
insert into #RR select '87.0元'
insert into #RR select '98.00元'
;with hgo as
(
select id,cast(replace(replace(replace(price,'元',''),'.00',''),'.0','') as int) price from #RR
)
select * from hgo order by price asc
(
id int identity(1,1) primary key,
price varchar(30)
)
insert into #RR select '200'
insert into #RR select '30.00'
insert into #RR select '15元'
insert into #RR select '87.0元'
insert into #RR select '98.00元'
;with hgo as
(
select id,cast(replace(replace(replace(price,'元',''),'.00',''),'.0','') as int) price from #RR
)
select * from hgo order by price asc
id price
----------- -----------
3 15
2 30
4 87
5 98
1 200