根据两个字段,新来生成一个字段的问题,急~~~
现在表有这个两个字段内容如下
lowprice highprice
0 50
50 100
100 150
150 200
200 250
250 300
300 999999
我想用根据这两个字段,新生成一个字段
price(记录如下)
50以下
50-100
100-150
150-200
200-250
250-300
300以上
请问应怎么定SQL
------解决方案--------------------Create Table TEST
(lowprice Int,
highprice Int)
Insert TEST Select 0, 50
Union All Select 50, 100
Union All Select 100, 150
Union All Select 150, 200
Union All Select 200, 250
Union All Select 250, 300
Union All Select 300, 999999
GO
Select
(Case When lowprice = 0 Then Rtrim(highprice) + N '以下 ' When highprice = 999999 Then Rtrim(lowprice) + N '以上 ' Else Rtrim(lowprice) + '- ' + Rtrim(highprice) End) As price
From
TEST
Go
Drop Table TEST
--Result
/*
price
50以下
50-100
100-150
150-200
200-250
250-300
300以上
*/
------解决方案--------------------select case when lowprice =0 then ' ' else lowprice end+case when highprice=999999 then '以上 ' else '- '+highprice end+case when lowprice =0 then '以下 ' else ' ' end from 表
------解决方案--------------------select case when lowprice =0 then cast(highprice as varchar)+ '以下 '
when lowprice> 0 and lowprice <=300 then cast(lowprice as varchar)+ '- '+cast(highprice as varchar)
when lowprice> 300 then '300以上 ' end
as col from table
------解决方案--------------------if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb
(
lowprice int,
highprice int
)
insert into tb(lowprice,highprice) values(0, 50)
insert into tb(lowprice,highprice) values(50, 100)
insert into tb(lowprice,highprice) values(100, 150)
insert into tb(lowprice,highprice) values(150, 200)
insert into tb(lowprice,highprice) values(200, 250)
insert into tb(lowprice,highprice) values(250, 300)
insert into tb(lowprice,highprice) values(300, 999999)
select
case when lowprice < 50 then '50以下 '
when lowprice > = 50 and lowprice < 300 then cast(lowprice as varchar) + '- ' + cast(highprice as varchar)
when lowprice > = 300 then '300以上 '
end as 合并后的结果
from tb
drop table tb
合并后的结果
------------
50以下
50-100
100-150
150-200
200-250
250-300
300以上
(所影响的行数为 7 行)
------解决方案--------------------借一楼数据
Create Table tb