请邹健、happydreamer、pbsql版主等高手进来帮我看一下这个SQL语句的问题，非常的着急

[CQP]

有表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

[CQP]

to libin_ftsafe(子陌红尘：当libin告别ftsafe):

我要的是排序后的结果，你给你的结果集是没有排序的

[CQP]

我现在就是不知道怎么解决这个排序的问题，这个问题困扰我好几天了

[zjcxc]

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 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
*/

[zjcxc]

改排序, 只是改 num 的生成算法而已.

[being21]

呵呵，就是没有看明白你要排什么序？？？

[子陌红尘]

简单的说，把"高八库一楼3号"转为"高8库1楼03号"这样的值比较适合现在的排序要求

[CQP]

to zjcxc(邹建:
排出来的序还是不对啊

[子陌红尘]

增加一个函数处理排序的问题，只做了简单的实现，没有优化：

create table TB_TEST(Place varchar(20),InCount int)
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
go

create 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

[CQP]

刚才忘了补充一点:是有一张表tb_Place和TB_TEST里面的Place字段对应的
PlaceCode,Place ,也就是要根据tb_Place表里PlaceCode字段来排序

[dawugui]

http://community.csdn.net/Expert/topic/5134/5134826.xml?temp=.9858667
和这个内容一样.


--我加了一行数据
--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
go

create 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 , capitalnumber

declare @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 tab2



jgid 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

[子陌红尘]

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(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

[子陌红尘]

既然有排序参考字段，用子查询把Place映射为PlaceCode排个序即可。

[zjcxc]

看错了楼主的排序

[zjcxc]

-- 可以这样

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.*,
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

[zjcxc]

建议楼主在存储数据的时候, 就把 库/楼/号 这三个信息取出来存储到单独的列中

处理的时候再解析的话, 在数据量大的时候, 是很有问题的

[CQP]

真是太感谢libin_ftsafe(子陌红尘：当libin告别ftsafe) 、zjcxc(邹建) 等高手了
问题已经解决了:)

[tytyyanyan]

create table table1
(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)

[tytyyanyan]

是这个意思吗？

[tytyyanyan]

楼主好像是这个意思，呵呵。。用这个方法比较容易

[lzhs]

楼主，其他子陌的语句己经能够实现你要的功能
但是由于你要求对Place进行排序，而且Place里面既有中文又有数字，因此建议你重新建一张表，用于对Place进行排序（如果可行的话），并对每一个Place给出一个值，用于表示在排序中所处的位置。

或者，有一个方法：
xp_execresultset 'Select ''Select * From TB_TEST Order by Place Collate '' +name FROM ::fn_helpcollations()',master－－用你的实际数据库取代

看一下哪个结果集返回的结果能够满足你所要求的排序规则，就用那个对应的Collate来加在Order By后面。