有表TB_TEST,共有两个字段Place varchar(50),InCount int记录如下格式:Place InCount
高八库一楼12号 192
高八库一楼3号 744
高二库二楼13号 189
高二库二楼15号 99
高二库二楼17号 114
高二库二楼21号 222
高二库二楼4号 196
高二库二楼8号 243
高二库三楼10号 229共有9条记录
我要把这个记录集变成如下记录集:
PlaceA InCountA PlaceB InCountB PlaceC InCountC PlaceD InCountD PlaceE InCountE PlaceF InCountF PlaceG InCountG
高二库二楼4号 196 高二库二楼8号 243 高二库二楼13号 189 高二库二楼15号 99 高二库二楼17号 114 高二库二楼21号 222 高二库三楼10号 229 PlaceA InCountA PlaceB InCountB PlaceC InCountC PlaceD InCountD PlaceE InCountE PlaceF InCountF PlaceG InCountG
高八库一楼3号 744 高八库一楼12号 192 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL相当于把原来的七行,转换成现在的一行,不够的用NULL值填充,并按照Place字段来排序,主要是排序有点不好搞,
上面的九条转化成现在二行,不够的用NULL值填充
这个问题是我项目中碰到的问题,非常的着急,本人在线等待此问题的解决,Thanks
高八库一楼12号 192
高八库一楼3号 744
高二库二楼13号 189
高二库二楼15号 99
高二库二楼17号 114
高二库二楼21号 222
高二库二楼4号 196
高二库二楼8号 243
高二库三楼10号 229共有9条记录
我要把这个记录集变成如下记录集:
PlaceA InCountA PlaceB InCountB PlaceC InCountC PlaceD InCountD PlaceE InCountE PlaceF InCountF PlaceG InCountG
高二库二楼4号 196 高二库二楼8号 243 高二库二楼13号 189 高二库二楼15号 99 高二库二楼17号 114 高二库二楼21号 222 高二库三楼10号 229 PlaceA InCountA PlaceB InCountB PlaceC InCountC PlaceD InCountD PlaceE InCountE PlaceF InCountF PlaceG InCountG
高八库一楼3号 744 高八库一楼12号 192 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL相当于把原来的七行,转换成现在的一行,不够的用NULL值填充,并按照Place字段来排序,主要是排序有点不好搞,
上面的九条转化成现在二行,不够的用NULL值填充
这个问题是我项目中碰到的问题,非常的着急,本人在线等待此问题的解决,Thanks
insert into TB_TEST select '高八库一楼12号',192
insert into TB_TEST select '高八库一楼3号 ',744
insert into TB_TEST select '高二库二楼13号',189
insert into TB_TEST select '高二库二楼15号',99
insert into TB_TEST select '高二库二楼17号',114
insert into TB_TEST select '高二库二楼21号',222
insert into TB_TEST select '高二库二楼4号 ',196
insert into TB_TEST select '高二库二楼8号 ',243
insert into TB_TEST select '高二库三楼10号',229
goselect
max(case a.num%7 when 0 then a.Place end) as PlaceA ,
max(case a.num%7 when 0 then a.InCount end) as InCountA,
max(case a.num%7 when 1 then a.Place end) as PlaceB ,
max(case a.num%7 when 1 then a.InCount end) as InCountB,
max(case a.num%7 when 2 then a.Place end) as PlaceC ,
max(case a.num%7 when 2 then a.InCount end) as InCountC,
max(case a.num%7 when 3 then a.Place end) as PlaceD ,
max(case a.num%7 when 3 then a.InCount end) as InCountD,
max(case a.num%7 when 4 then a.Place end) as PlaceE ,
max(case a.num%7 when 4 then a.InCount end) as InCountE,
max(case a.num%7 when 5 then a.Place end) as PlaceF ,
max(case a.num%7 when 5 then a.InCount end) as InCountF,
max(case a.num%7 when 6 then a.Place end) as PlaceG ,
max(case a.num%7 when 6 then a.InCount end) as InCountG
from
(select t.*,(select count(*) from TB_TEST where Place<t.Place) as num from TB_TEST t) a
group by
a.num/7
go
drop table TB_TEST
go
insert into TB_TEST select '高八库一楼12号',192
insert into TB_TEST select '高八库一楼3号 ',744
insert into TB_TEST select '高二库二楼13号',189
insert into TB_TEST select '高二库二楼15号',99
insert into TB_TEST select '高二库二楼17号',114
insert into TB_TEST select '高二库二楼21号',222
insert into TB_TEST select '高二库二楼4号 ',196
insert into TB_TEST select '高二库二楼8号 ',243
insert into TB_TEST select '高二库三楼10号',229
goselect
max(case a.num%7 when 0 then a.Place end) as PlaceA ,
max(case a.num%7 when 0 then a.InCount end) as InCountA,
max(case a.num%7 when 1 then a.Place end) as PlaceB ,
max(case a.num%7 when 1 then a.InCount end) as InCountB,
max(case a.num%7 when 2 then a.Place end) as PlaceC ,
max(case a.num%7 when 2 then a.InCount end) as InCountC,
max(case a.num%7 when 3 then a.Place end) as PlaceD ,
max(case a.num%7 when 3 then a.InCount end) as InCountD,
max(case a.num%7 when 4 then a.Place end) as PlaceE ,
max(case a.num%7 when 4 then a.InCount end) as InCountE,
max(case a.num%7 when 5 then a.Place end) as PlaceF ,
max(case a.num%7 when 5 then a.InCount end) as InCountF,
max(case a.num%7 when 6 then a.Place end) as PlaceG ,
max(case a.num%7 when 6 then a.InCount end) as InCountG
from
(select t.*,(select count(*) from TB_TEST where Place<t.Place) as num from TB_TEST t) a
group by
a.num/7
go
/*
PlaceA InCountA PlaceB InCountB PlaceC InCountC PlaceD InCountD PlaceE InCountE PlaceF InCountF PlaceG InCountG
-------------------- ----------- -------------------- ----------- -------------------- ----------- -------------------- ----------- -------------------- ----------- -------------------- ----------- -------------------- -----------
高八库一楼12号 192 高八库一楼3号 744 高二库二楼13号 189 高二库二楼15号 99 高二库二楼17号 114 高二库二楼21号 222 高二库二楼4号 196
高二库二楼8号 243 高二库三楼10号 229 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
*/
drop table TB_TEST
go
max(case a.num%7 when 0 then a.Place end) as PlaceA ,
max(case a.num%7 when 0 then a.InCount end) as InCountA,
max(case a.num%7 when 1 then a.Place end) as PlaceB ,
max(case a.num%7 when 1 then a.InCount end) as InCountB,
max(case a.num%7 when 2 then a.Place end) as PlaceC ,
max(case a.num%7 when 2 then a.InCount end) as InCountC,
max(case a.num%7 when 3 then a.Place end) as PlaceD ,
max(case a.num%7 when 3 then a.InCount end) as InCountD,
max(case a.num%7 when 4 then a.Place end) as PlaceE ,
max(case a.num%7 when 4 then a.InCount end) as InCountE,
max(case a.num%7 when 5 then a.Place end) as PlaceF ,
max(case a.num%7 when 5 then a.InCount end) as InCountF,
max(case a.num%7 when 6 then a.Place end) as PlaceG ,
max(case a.num%7 when 6 then a.InCount end) as InCountG
from
(select t.*,(select count(*) from TB_TEST where InCount > t.InCount OR (InCount = t.InCount AND Place<t.Place)) as num from TB_TEST t) a
group by
a.num/7
go
/*
PlaceA InCountA PlaceB InCountB PlaceC InCountC PlaceD InCountD PlaceE InCountE PlaceF InCountF PlaceG InCountG
-------------------- ----------- -------------------- ----------- -------------------- ----------- -------------------- ----------- -------------------- ----------- -------------------- ----------- -------------------- -----------
高八库一楼3号 744 高二库二楼8号 243 高二库三楼10号 229 高二库二楼21号 222 高二库二楼4号 196 高八库一楼12号 192 高二库二楼13号 189
高二库二楼17号 114 高二库二楼15号 99 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
*/
排出来的序还是不对啊
insert into TB_TEST select '高八库一楼12号',192
insert into TB_TEST select '高八库一楼3号',744
insert into TB_TEST select '高二库二楼13号',189
insert into TB_TEST select '高二库二楼15号',99
insert into TB_TEST select '高二库二楼17号',114
insert into TB_TEST select '高二库二楼21号',222
insert into TB_TEST select '高二库二楼4号',196
insert into TB_TEST select '高二库二楼8号',243
insert into TB_TEST select '高二库三楼10号',229
gocreate function f_str(@Place varchar(20))
returns varchar(20)
as
begin
declare @t table(str1 varchar(2),str2 varchar(2))
insert into @t select '1','一'
union select '2','二'
union select '3','三'
union select '4','四'
union select '5','五'
union select '6','六'
union select '7','七'
union select '8','八'
union select '9','九'
select @Place=replace(@Place,str2,str1) from @t
set @Place=left(@Place,charindex('楼',@Place))+right('00'+stuff(@Place,1,charindex('楼',@Place),''),3) return @Place
end
go
select
max(case a.num%7 when 0 then a.Place end) as PlaceA ,
max(case a.num%7 when 0 then a.InCount end) as InCountA,
max(case a.num%7 when 1 then a.Place end) as PlaceB ,
max(case a.num%7 when 1 then a.InCount end) as InCountB,
max(case a.num%7 when 2 then a.Place end) as PlaceC ,
max(case a.num%7 when 2 then a.InCount end) as InCountC,
max(case a.num%7 when 3 then a.Place end) as PlaceD ,
max(case a.num%7 when 3 then a.InCount end) as InCountD,
max(case a.num%7 when 4 then a.Place end) as PlaceE ,
max(case a.num%7 when 4 then a.InCount end) as InCountE,
max(case a.num%7 when 5 then a.Place end) as PlaceF ,
max(case a.num%7 when 5 then a.InCount end) as InCountF,
max(case a.num%7 when 6 then a.Place end) as PlaceG ,
max(case a.num%7 when 6 then a.InCount end) as InCountG
from
(select
t.*,
(select count(*) from TB_TEST where dbo.f_str(Place)<dbo.f_str(t.Place)) as num
from
TB_TEST t) a
group by
a.num/7
go/*
PlaceA InCountA PlaceB InCountB PlaceC InCountC PlaceD InCountD PlaceE InCountE PlaceF InCountF PlaceG InCountG
-------------------- ----------- -------------------- ----------- -------------------- ----------- -------------------- ----------- -------------------- ----------- -------------------- ----------- -------------------- -----------
高二库二楼4号 196 高二库二楼8号 243 高二库二楼13号 189 高二库二楼15号 99 高二库二楼17号 114 高二库二楼21号 222 高二库三楼10号 229
高八库一楼3号 744 高八库一楼12号 192 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
*/drop function f_str
drop table TB_TEST
go
PlaceCode,Place ,也就是要根据tb_Place表里PlaceCode字段来排序
和这个内容一样.
--我加了一行数据
--insert into tab1(jgid ,capitalnumber, tr_type) values('9453','351101319451','tru')if object_id('pubs..tab1') is not null
drop table tab1
go
if object_id('pubs..tab2') is not null
drop table tab2
gocreate table tab1
(
jgid varchar(10),
capitalnumber varchar(20),
tr_type varchar(10)
)
insert into tab1(jgid ,capitalnumber, tr_type) values('9452','351101314351','tru')
insert into tab1(jgid ,capitalnumber, tr_type) values('9452','351101314352','stru')
insert into tab1(jgid ,capitalnumber, tr_type) values('9452','351101314353','tru')
insert into tab1(jgid ,capitalnumber, tr_type) values('9452','351101314354','tru')
insert into tab1(jgid ,capitalnumber, tr_type) values('9452','351101314356','stru')
insert into tab1(jgid ,capitalnumber, tr_type) values('9452','351101314358','tru')
insert into tab1(jgid ,capitalnumber, tr_type) values('9453','351101319451','tru')select * , px=(select count(1) from tab1 where jgid=a.jgid and capitalnumber<a.capitalnumber)+1 into tab2 from tab1 a
order by jgid , capitalnumberdeclare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ', max(case when px = ''' + rtrim(cast(px as varchar(10))) + ''' then capitalnumber else null end) as capitalnumber' + rtrim(cast(px as varchar(10))) + ',
max(case when px = ''' + rtrim(cast(px as varchar(10))) + ''' then tr_type else null end) as tr_type' + rtrim(cast(px as varchar(10)))
from tab2 group by px order by px
set @sql = 'select jgid ' + @sql + ' from tab2 group by jgid'
EXEC(@sql)drop table tab1
drop table tab2jgid capitalnumber1 tr_type1 capitalnumber2 tr_type2 capitalnumber3 tr_type3 capitalnumber4 tr_type4 capitalnumber5 tr_type5 capitalnumber6 tr_type6
---- -------------- -------- -------------- -------- -------------- -------- -------------- -------- -------------- -------- -------------- --------
9452 351101314351 tru 351101314352 stru 351101314353 tru 351101314354 tru 351101314356 stru 351101314358 tru
9453 351101319451 tru NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
max(case a.num%7 when 0 then a.Place end) as PlaceA ,
max(case a.num%7 when 0 then a.InCount end) as InCountA,
max(case a.num%7 when 1 then a.Place end) as PlaceB ,
max(case a.num%7 when 1 then a.InCount end) as InCountB,
max(case a.num%7 when 2 then a.Place end) as PlaceC ,
max(case a.num%7 when 2 then a.InCount end) as InCountC,
max(case a.num%7 when 3 then a.Place end) as PlaceD ,
max(case a.num%7 when 3 then a.InCount end) as InCountD,
max(case a.num%7 when 4 then a.Place end) as PlaceE ,
max(case a.num%7 when 4 then a.InCount end) as InCountE,
max(case a.num%7 when 5 then a.Place end) as PlaceF ,
max(case a.num%7 when 5 then a.InCount end) as InCountF,
max(case a.num%7 when 6 then a.Place end) as PlaceG ,
max(case a.num%7 when 6 then a.InCount end) as InCountG
from
(select
t.*,
(select count(s.*) from TB_TEST s where (select PlaceCode from tb_Place where Place=s.Place)<(select PlaceCode from tb_Place where Place=t.Place)) as num
from
TB_TEST t) a
group by
a.num/7
go
max(case a.num%7 when 0 then a.Place end) as PlaceA ,
max(case a.num%7 when 0 then a.InCount end) as InCountA,
max(case a.num%7 when 1 then a.Place end) as PlaceB ,
max(case a.num%7 when 1 then a.InCount end) as InCountB,
max(case a.num%7 when 2 then a.Place end) as PlaceC ,
max(case a.num%7 when 2 then a.InCount end) as InCountC,
max(case a.num%7 when 3 then a.Place end) as PlaceD ,
max(case a.num%7 when 3 then a.InCount end) as InCountD,
max(case a.num%7 when 4 then a.Place end) as PlaceE ,
max(case a.num%7 when 4 then a.InCount end) as InCountE,
max(case a.num%7 when 5 then a.Place end) as PlaceF ,
max(case a.num%7 when 5 then a.InCount end) as InCountF,
max(case a.num%7 when 6 then a.Place end) as PlaceG ,
max(case a.num%7 when 6 then a.InCount end) as InCountG
from(
select t.*,
num = (
select count(*) from TB_TEST
where
RTRIM(CHARINDEX(SUBSTRING(Place, 2, 1), '一二三四五六七八九十')) +
RTRIM(CHARINDEX(SUBSTRING(Place, 4, 1), '一二三四五六七八九十')) +
RIGHT(100 + SUBSTRING(Place, 6, CHARINDEX('号', Place) - 6), 2)
<
RTRIM(CHARINDEX(SUBSTRING(t.Place, 2, 1), '一二三四五六七八九十')) +
RTRIM(CHARINDEX(SUBSTRING(t.Place, 4, 1), '一二三四五六七八九十')) +
RIGHT(100 + SUBSTRING(t.Place, 6, CHARINDEX('号', t.Place) - 6), 2)
OR(
Place = t.Place AND InCount > t.InCount)
)
FROM TB_TEST t
) a
group by a.num/7
问题已经解决了:)
(place varchar(20),
incount int)
insert table1 values('一',2)
insert table1 values('二',3)
insert table1 values('三',3)declare @c varchar(200)
set @c=''
select @c=@c+place+' '+ cast(InCount as varchar(50))+';'from table1 order by place
print left(@c,len(@c)-1)
但是由于你要求对Place进行排序,而且Place里面既有中文又有数字,因此建议你重新建一张表,用于对Place进行排序(如果可行的话),并对每一个Place给出一个值,用于表示在排序中所处的位置。或者,有一个方法:
xp_execresultset 'Select ''Select * From TB_TEST Order by Place Collate '' +name FROM ::fn_helpcollations()',master--用你的实际数据库取代看一下哪个结果集返回的结果能够满足你所要求的排序规则,就用那个对应的Collate来加在Order By后面。