在sql数据库中,查询品牌表,根据品牌名称(brandName)让品牌表中的数据进行a-z的排序。
例如:
brandId brandName
1 微软Microsoft
2 阿帕奇Apachi
3 九阳
4 罗技Logitech
5 艾酷Acco
6 NORTH BAYOU NB
7 APC
8 Cisco Linksys
9 OCZ
10 博帝PATRIOT
11 奔腾Povos
12 尊莹宫
13 灿凌科技
14 IBM
15 索尼Sony效果:
brandId brandName
7 APC
2 阿帕奇Apachi
5 艾酷Acco
10 博帝PATRIOT
11 奔腾Povos
8 Cisco Linksys
13 灿凌科技
14 IBM
3 九阳
4 罗技Logitech
6 NORTH BAYOU NB
9 OCZ
15 索尼Sony
1 微软Microsoft
12 尊莹宫
例如:
brandId brandName
1 微软Microsoft
2 阿帕奇Apachi
3 九阳
4 罗技Logitech
5 艾酷Acco
6 NORTH BAYOU NB
7 APC
8 Cisco Linksys
9 OCZ
10 博帝PATRIOT
11 奔腾Povos
12 尊莹宫
13 灿凌科技
14 IBM
15 索尼Sony效果:
brandId brandName
7 APC
2 阿帕奇Apachi
5 艾酷Acco
10 博帝PATRIOT
11 奔腾Povos
8 Cisco Linksys
13 灿凌科技
14 IBM
3 九阳
4 罗技Logitech
6 NORTH BAYOU NB
9 OCZ
15 索尼Sony
1 微软Microsoft
12 尊莹宫
drop Function HZ2PY
go
Create Function HZ2PY
(
@cString nVarChar (200)
)
Returns nVarChar(100)
AS
Begin
Declare @nFor Int --字符串循环用
Declare @nPYFor Int --拼音串循环用
Declare @cPYString VarChar(100) --拼音串
Declare @cCurChar VarChar(2) --当前字符
Declare @vReturn VarChar (100) ----将返回的结果
Set @cPYString = '吖八嚓咑妸发旮铪讥讥咔垃呣拿讴趴七呥仨他哇哇哇夕丫匝咗'
Set @vReturn = ''
Set @nFor = 0
While @nFor < Len(@cString)
Begin
Set @nFor = @nFor + 1
Set @cCurChar = Substring(@cString,@nFor,1)
Set @nPYFor = 0
While @nPYFor < len(@cPYString)
Begin
Set @nPYFor = @nPYFor + 1
If @cCurChar < Substring(@cPYString,@nPYFor,1)
Begin
Set @vReturn = Rtrim(@vReturn) + (Case When @nPYFor <> 1 Then Char(63 + @nPYFor) Else @cCurChar End)
Break
End
Else
Begin
Continue
End
End
End
Return @vReturn
End
declare @tb table (id int,name varchar(20))
insert into @tb select 1,'波'
insert into @tb select 2,'病'
insert into @tb select 3,'啊'
insert into @tb select 4,'吹'
insert into @tb select 5,'崔'select *,dbo.hz2py(name) as '首字母' from @tbid name 首字母
1 波 B
2 病 B
3 啊 A
4 吹 C
5 崔 C
--首先准备一个拼音表
CREATE TABLE AWord(
PY varchar(10),
ZW nvarchar(10)
) ON [PRIMARY]
GO
--插入数据
insert into aword
select 'A',N'骜'
union all select 'B',N'簿'
union all select 'C',N'错'
union all select 'D',N'鵽'
union all select 'E',N'樲'
union all select 'F',N'鳆'
union all select 'G',N'腂'
union all select 'H',N'夻'
union all select 'J',N'攈'
union all select 'K',N'穒'
union all select 'L',N'鱳'
union all select 'M',N'旀'
union all select 'N',N'桛'
union all select 'O',N'沤'
union all select 'P',N'曝'
union all select 'Q',N'囕'
union all select 'R',N'鶸'
union all select 'S',N'蜶'
union all select 'T',N'箨'
union all select 'W',N'鹜'
union all select 'X',N'鑂'
union all select 'Y',N'韵'
union all select 'Z',N'咗'
GO
--建立一个获取拼音首字母的函数
create function f_GetPY
(
@str nvarchar(2)
)
returns nvarchar(2)
as
begin
set @str = left(@str, 1)
return (
case when unicode(@str) between 19968 and 19968+20901 then(
select top 1 PY+'.' from aword where zw>=@str
collate Chinese_PRC_CS_AS_KS_WS order by PY ASC
) else @str end
)
end
GO
--然后查询排序
select * from 表 order by dbo.f_GetPY(字段)
各位路过的帅哥美眉
想储各位的智慧想想
不管行与不行
请各位留下见解 啥的 谢了