[易飞]二进制排序规则下-汉字字典排序

易飞的数据库是采取二进制排序规则，在统计小计和总计过程,位置出现偏差。

小-X,总-Z 应该会在相对后面才OK的。参照百度做法

处理方法:

一、首先准备一个拼音表

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

三、存储过程

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go -- ============================================= -- Author: <David Gong> -- Create date: <2011-12-08> -- Description: <统计每月供应商、材料类别分布情况> -- ============================================= ALTER Procedure [dbo].[UP_POPlan] ( @year as char(4)='2011' ) as begin declare @plan Table ( category nvarchar(20) ,supplier nvarchar(20) ,acct_m01 decimal(18,2) ,acct_m02 decimal(18,2) ,acct_m03 decimal(18,2) ,acct_m04 decimal(18,2) ,acct_m05 decimal(18,2) ,acct_m06 decimal(18,2) ,acct_m07 decimal(18,2) ,acct_m08 decimal(18,2) ,acct_m09 decimal(18,2) ,acct_m10 decimal(18,2) ,acct_m11 decimal(18,2) ,acct_m12 decimal(18,2) ) declare @planbycategory Table ( category nvarchar(20) ,supplier nvarchar(20) ,acct_m01 decimal(18,2) ,acct_m02 decimal(18,2) ,acct_m03 decimal(18,2) ,acct_m04 decimal(18,2) ,acct_m05 decimal(18,2) ,acct_m06 decimal(18,2) ,acct_m07 decimal(18,2) ,acct_m08 decimal(18,2) ,acct_m09 decimal(18,2) ,acct_m10 decimal(18,2) ,acct_m11 decimal(18,2) ,acct_m12 decimal(18,2) ) Insert into @plan(category,supplier,acct_m01)select left(TC004,2) as category ,MA002 as supplier,(TC019+TC020) as amount from PURTC inner join PURMA on MA001=TC004 where left(TC003,4)=@year and substring(TC003,5,2)='01' Insert into @plan(category,supplier,acct_m02)select left(TC004,2) as category ,MA002 as supplier,(TC019+TC020) as amount from PURTC inner join PURMA on MA001=TC004 where left(TC003,4)=@year and substring(TC003,5,2)='02' Insert into @plan(category,supplier,acct_m03)select left(TC004,2) as category ,MA002 as supplier,(TC019+TC020) as amount from PURTC inner join PURMA on MA001=TC004 where left(TC003,4)=@year and substring(TC003,5,2)='03' Insert into @plan(category,supplier,acct_m04)select left(TC004,2) as category ,MA002 as supplier,(TC019+TC020) as amount from PURTC inner join PURMA on MA001=TC004 where left(TC003,4)=@year and substring(TC003,5,2)='04' Insert into @plan(category,supplier,acct_m05)select left(TC004,2) as category ,MA002 as supplier,(TC019+TC020) as amount from PURTC inner join PURMA on MA001=TC004 where left(TC003,4)=@year and substring(TC003,5,2)='05' Insert into @plan(category,supplier,acct_m06)select left(TC004,2) as category ,MA002 as supplier,(TC019+TC020) as amount from PURTC inner join PURMA on MA001=TC004 where left(TC003,4)=@year and substring(TC003,5,2)='06' Insert into @plan(category,supplier,acct_m07)select left(TC004,2) as category ,MA002 as supplier,(TC019+TC020) as amount from PURTC inner join PURMA on MA001=TC004 where left(TC003,4)=@year and substring(TC003,5,2)='07' Insert into @plan(category,supplier,acct_m08)select left(TC004,2) as category ,MA002 as supplier,(TC019+TC020) as amount from PURTC inner join PURMA on MA001=TC004 where left(TC003,4)=@year and substring(TC003,5,2)='08' Insert into @plan(category,supplier,acct_m09)select left(TC004,2) as category ,MA002 as supplier,(TC019+TC020) as amount from PURTC inner join PURMA on MA001=TC004 where left(TC003,4)=@year and substring(TC003,5,2)='09' Insert into @plan(category,supplier,acct_m10)select left(TC004,2) as category ,MA002 as supplier,(TC019+TC020) as amount from PURTC inner join PURMA on MA001=TC004 where left(TC003,4)=@year and substring(TC003,5,2)='10' Insert into @plan(category,supplier,acct_m11)select left(TC004,2) as category ,MA002 as supplier,(TC019+TC020) as amount from PURTC inner join PURMA on MA001=TC004 where left(TC003,4)=@year and substring(TC003,5,2)='11' Insert into @plan(category,supplier,acct_m12)select left(TC004,2) as category ,MA002 as supplier,(TC019+TC020) as amount from PURTC inner join PURMA on MA001=TC004 where left(TC003,4)=@year and substring(TC003,5,2)='12' --分组合计 Insert into @planbycategory(category,supplier,acct_m01,acct_m02,acct_m03,acct_m04,acct_m05,acct_m06,acct_m07,acct_m08,acct_m09,acct_m10,acct_m11,acct_m12) select case when category='01' then 'LCD' when category='02' then 'BL' when category='03' then 'PCB' when category='04' then 'IC' when category='05' then '金属件' when category='06' then '连接器' when category='07' then '电子元器件' when category='08' then '辅料' when category='09' then '继电器' when category='98' then '设备' else '其他' end category , '总计' as supplier , convert(decimal(19,2),sum(isnull(acct_m01,0))) as 一月, convert(decimal(19,2),sum(isnull(acct_m02,0))) as 二月, convert(decimal(19,2),sum(isnull(acct_m03,0))) as 三月, convert(decimal(19,2),sum(isnull(acct_m04,0))) as 四月, convert(decimal(19,2),sum(isnull(acct_m05,0))) as 五月, convert(decimal(19,2),sum(isnull(acct_m06,0))) as 六月, convert(decimal(19,2),sum(isnull(acct_m07,0))) as 七月, convert(decimal(19,2),sum(isnull(acct_m08,0))) as 八月, convert(decimal(19,2),sum(isnull(acct_m09,0))) as 九月, convert(decimal(19,2),sum(isnull(acct_m10,0))) as 十月, convert(decimal(19,2),sum(isnull(acct_m11,0))) as 十一月, convert(decimal(19,2),sum(isnull(acct_m12,0))) as 十二月 from @plan group by category select a.category,a.supplier,a.一月,a.二月,a.三月,a.四月,a.五月,a.六月,a.七月,a.八月,a.九月,a.十月,a.十一月,a.十二月, (a.一月+a.二月+a.三月+a.四月+a.五月+a.六月+a.七月+八月+九月+十月+十一月+十二月) as 合计 from ( select case when category='01' then 'LCD' when category='02' then 'BL' when category='03' then 'PCB' when category='04' then 'IC' when category='05' then '金属件' when category='06' then '连接器' when category='07' then '电子元器件' when category='08' then '辅料' when category='09' then '继电器' when category='98' then '设备' else '其他' end category , supplier, convert(decimal(19,2),sum(isnull(acct_m01,0))) as 一月, convert(decimal(19,2),sum(isnull(acct_m02,0))) as 二月, convert(decimal(19,2),sum(isnull(acct_m03,0))) as 三月, convert(decimal(19,2),sum(isnull(acct_m04,0))) as 四月, convert(decimal(19,2),sum(isnull(acct_m05,0))) as 五月, convert(decimal(19,2),sum(isnull(acct_m06,0))) as 六月, convert(decimal(19,2),sum(isnull(acct_m07,0))) as 七月, convert(decimal(19,2),sum(isnull(acct_m08,0))) as 八月, convert(decimal(19,2),sum(isnull(acct_m09,0))) as 九月, convert(decimal(19,2),sum(isnull(acct_m10,0))) as 十月, convert(decimal(19,2),sum(isnull(acct_m11,0))) as 十一月, convert(decimal(19,2),sum(isnull(acct_m12,0))) as 十二月 from @plan group by category,supplier --小组合计 union all select * from @planbycategory --总计 union all select '总计' as category, '总计' as supplier, convert(decimal(19,2),sum(isnull(acct_m01,0))) as 一月, convert(decimal(19,2),sum(isnull(acct_m02,0))) as 二月, convert(decimal(19,2),sum(isnull(acct_m03,0))) as 三月, convert(decimal(19,2),sum(isnull(acct_m04,0))) as 四月, convert(decimal(19,2),sum(isnull(acct_m05,0))) as 五月, convert(decimal(19,2),sum(isnull(acct_m06,0))) as 六月, convert(decimal(19,2),sum(isnull(acct_m07,0))) as 七月, convert(decimal(19,2),sum(isnull(acct_m08,0))) as 八月, convert(decimal(19,2),sum(isnull(acct_m09,0))) as 九月, convert(decimal(19,2),sum(isnull(acct_m10,0))) as 十月, convert(decimal(19,2),sum(isnull(acct_m11,0))) as 十一月, convert(decimal(19,2),sum(isnull(acct_m12,0))) as 十二月 from @planbycategory ) a order by dbo.f_GetPY(a.category) , dbo.f_GetPY(a.supplier) ASC END

查询结果