sql知识1

/*（完成）趣味处理把0123...转换成: 〇，一，二...*/
drop procedure proc_ChangeNum
go
create procedure proc_ChangeNum
@number nvarchar(100),
@changeNum nvarchar(200) output
as
set @number = replace(@number,'0','〇')
set @number = replace(@number,'1','一')
set @number = replace(@number,'2','二')
set @number = replace(@number,'3','三')
set @number = replace(@number,'4','四')
set @number = replace(@number,'5','五')
set @number = replace(@number,'6','六')
set @number = replace(@number,'7','七')
set @number = replace(@number,'8','八')
set @number = replace(@number,'9','九')

set @changeNum=@number
go
declare @changeNum nvarchar(200)
exec proc_ChangeNum '1009067',@changeNum output
select 转换后的数=@changeNum
go

/*统计某一关键字出现的次数,且只显示出现次数超过多少次*/
select adress ,出现次数=count(adress)
from userInfo
group by adress
having count(adress)>1
go

/*相同地区的只显示price最高的那一条*/
select adress,price=max(price) from userInfo group by adress
/*可以按照大连，北京，广州这样从上到下显示*/
select adress,price from userInfo order by (select case adress when '广州' then 1 when '北京' then 2 when '大连' then 3 end) desc