27,579
社区成员
发帖
与我相关
我的任务
分享一个难题请教，请大侠帮忙
有以下一个表结构
TableID CharacterID GameStartTime GameEndTime UserName Score DealerID RegionID
6 12 2010-6-12 2010-6-13 mult_nihao1 8 12 4
6 13 2010-6-12 2010-6-13 mult_nihao2 -8 12 4
6 14 2010-6-12 2010-6-13 mult_nihao3 0 12 4
6 15 2010-6-12 2010-6-13 mult_nihao4 0 12 4
7 12 2010-6-12 2010-6-13 mult_nihao1 -10 12 7
7 13 2010-6-12 2010-6-13 mult_nihao2 0 12 7
7 14 2010-6-12 2010-6-13 mult_nihao3 10 12 7
7 15 2010-6-12 2010-6-13 mult_nihao4 0 12 7
8 12 2010-6-12 2010-6-13 mult_nihao1 0 12 7
8 13 2010-6-12 2010-6-13 mult_nihao2 -10 12 7
8 14 2010-6-12 2010-6-13 mult_nihao3 0 12 7
8 22 2010-6-12 2010-6-13 mult_nihao20 10 12 7
我的问题是如何从上面的表中得到以下结构的数据
TableID Player1 Score1 Player2 Score2 Player3 Score3 Player4 Score4 Dealer
6 mult_nihao1 8 mult_nihao2 -8 mult_nihao3 0 mult_nihao4 0 12
7 mult_nihao1 -10 mult_nihao2 0 mult_nihao3 10 mult_nihao4 0 12
8 mult_nihao1 0 mult_nihao2 -10 mult_nihao3 0 mult_nihao20 10 12
TableID CharacterID GameStartTime GameEndTime UserName Score DealerID RegionID
6 12 2010-6-12 2010-6-13 mult_nihao1 8 12 4
6 13 2010-6-12 2010-6-13 mult_nihao2 -8 12 4
6 14 2010-6-12 2010-6-13 mult_nihao3 0 12 4
6 15 2010-6-12 2010-6-13 mult_nihao4 0 12 4
7 12 2010-6-12 2010-6-13 mult_nihao1 -10 12 7
7 13 2010-6-12 2010-6-13 mult_nihao2 0 12 7
7 14 2010-6-12 2010-6-13 mult_nihao3 10 12 7
7 15 2010-6-12 2010-6-13 mult_nihao4 0 12 7
8 12 2010-6-12 2010-6-13 mult_nihao1 0 12 7
8 13 2010-6-12 2010-6-13 mult_nihao2 -10 12 7
8 14 2010-6-12 2010-6-13 mult_nihao3 0 12 7
8 22 2010-6-12 2010-6-13 mult_nihao20 10 12 7
我的问题是如何从上面的表中得到以下结构的数据
TableID Player1 Score1 Player2 Score2 Player3 Score3 Player4 Score4 Dealer
6 mult_nihao1 8 mult_nihao2 -8 mult_nihao3 0 mult_nihao4 0 12
7 mult_nihao1 -10 mult_nihao2 0 mult_nihao3 10 mult_nihao4 0 12
8 mult_nihao1 0 mult_nihao2 -10 mult_nihao3 0 mult_nihao20 10 12
...全文
请发表友善的回复…
发表回复
andy_liucj 2010-06-09
- 打赏
- 举报
if exists(select 1 from sysobjects where name='tb') drop table tb
create table tb(TableID int,UserName varchar(20),Score int ,DealerID int)
--select * from tb
insert into tb
select 6 ,'mult_nihao1', 8, 12 union all
select 6 ,'mult_nihao2', -8, 12 union all
select 6 ,'mult_nihao3', 0, 12 union all
select 6 ,'mult_nihao4', 0, 12 union all
select 7 ,'mult_nihao1', -10, 12 union all
select 7 ,'mult_nihao2', 0, 12 union all
select 7 ,'mult_nihao3', 10, 12 union all
select 7 ,'mult_nihao4', 0, 12 union all
select 8 ,'mult_nihao1', 0, 12 union all
select 8 ,'mult_nihao2', -10, 12 union all
select 8 ,'mult_nihao3', 0, 12 union all
select 8 ,'mult_nihao20', 10, 12
;with t as
(
select sn=row_number() over (partition by TableID order by TableID),tb.* from tb
)
select TableID, max(case sn when 1 then UserName else null end) play1,
max(case sn when 1 then Score else null end) Score1,
max(case sn when 2 then UserName else null end) play2,
max(case sn when 2 then Score else null end) Score2,
max(case sn when 3 then UserName else null end) play3,
max(case sn when 3 then Score else null end) Score3,
max(case sn when 4 then UserName else null end) play4,
max(case sn when 4 then Score else null end) Score4,
DealerID from t group by TableID, DealerID
dawugui 2010-06-09
- 打赏
- 举报
create table tb(TableID int,CharacterID int,UserName varchar(20) , Score int,DealerID int)
insert into tb values(6 ,12 ,'mult_nihao1' ,8 ,12)
insert into tb values(6 ,13 ,'mult_nihao2' ,-8,12)
insert into tb values(6 ,14 ,'mult_nihao3' ,0,12)
insert into tb values(6 ,15 ,'mult_nihao4' ,0,12)
insert into tb values(7 ,12 ,'mult_nihao1' ,-10,12)
insert into tb values(7 ,13 ,'mult_nihao2' ,0,12)
insert into tb values(7 ,14 ,'mult_nihao3' ,10,12)
insert into tb values(7 ,15 ,'mult_nihao4' ,0,12)
insert into tb values(8 ,12 ,'mult_nihao1' ,0,12)
insert into tb values(8 ,13 ,'mult_nihao2' ,-10,12)
insert into tb values(8 ,14 ,'mult_nihao3' ,0,12)
insert into tb values(8 ,22 ,'mult_nihao20',10,12)
go
--SQL 2005静态，指相同tableid最多四个不同的CharacterID
select TableID,
max(case px when 1 then UserName else '' end) Player1,
max(case px when 1 then Score else 0 end) Score1,
max(case px when 2 then UserName else '' end) Player2,
max(case px when 2 then Score else 0 end) Score2,
max(case px when 3 then UserName else '' end) Player3,
max(case px when 3 then Score else 0 end) Score3,
max(case px when 4 then UserName else '' end) Player4,
max(case px when 4 then Score else 0 end) Score4,
max(DealerID) Dealer
from
(
select t.* , px = row_number() over(partition by TableID order by CharacterID) from tb t
) m
group by TableID
/*
TableID Player1 Score1 Player2 Score2 Player3 Score3 Player4 Score4 Dealer
----------- -------------------- ----------- -------------------- ----------- -------------------- ----------- -------------------- ----------- -----------
6 mult_nihao1 8 mult_nihao2 0 mult_nihao3 0 mult_nihao4 0 12
7 mult_nihao1 0 mult_nihao2 0 mult_nihao3 10 mult_nihao4 0 12
8 mult_nihao1 0 mult_nihao2 0 mult_nihao3 0 mult_nihao20 10 12
(3 行受影响)
*/
--SQL 2005动态，指相同tableid其CharacterID个数不定
declare @sql varchar(8000)
set @sql = 'select TableID '
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then UserName else '''' end) [Player' + cast(px as varchar) + ']'
+ ' , max(case px when ''' + cast(px as varchar) + ''' then Score else 0 end) [Score' + cast(px as varchar) + ']'
from (select distinct px from (select t.* , px = row_number() over(partition by TableID order by CharacterID) from tb t) m) as a
set @sql = @sql + ',max(Dealerid) Dealer from (select t.* , px = row_number() over(partition by TableID order by CharacterID) from tb t) m group by TableID'
exec(@sql)
/*
TableID Player1 Score1 Player2 Score2 Player3 Score3 Player4 Score4 Dealer
----------- -------------------- ----------- -------------------- ----------- -------------------- ----------- -------------------- ----------- -----------
6 mult_nihao1 8 mult_nihao2 0 mult_nihao3 0 mult_nihao4 0 12
7 mult_nihao1 0 mult_nihao2 0 mult_nihao3 10 mult_nihao4 0 12
8 mult_nihao1 0 mult_nihao2 0 mult_nihao3 0 mult_nihao20 10 12
(3 行受影响)
*/
drop table tbdawugui 2010-06-09
- 打赏
- 举报
create table tb(TableID int,CharacterID int,UserName varchar(20) , Score int,DealerID int)
insert into tb values(6 ,12 ,'mult_nihao1' ,8 ,12)
insert into tb values(6 ,13 ,'mult_nihao2' ,-8,12)
insert into tb values(6 ,14 ,'mult_nihao3' ,0,12)
insert into tb values(6 ,15 ,'mult_nihao4' ,0,12)
insert into tb values(7 ,12 ,'mult_nihao1' ,-10,12)
insert into tb values(7 ,13 ,'mult_nihao2' ,0,12)
insert into tb values(7 ,14 ,'mult_nihao3' ,10,12)
insert into tb values(7 ,15 ,'mult_nihao4' ,0,12)
insert into tb values(8 ,12 ,'mult_nihao1' ,0,12)
insert into tb values(8 ,13 ,'mult_nihao2' ,-10,12)
insert into tb values(8 ,14 ,'mult_nihao3' ,0,12)
insert into tb values(8 ,22 ,'mult_nihao20',10,12)
go
--SQL 2000静态，指相同tableid最多四个不同的CharacterID
select TableID,
max(case px when 1 then UserName else '' end) Player1,
max(case px when 1 then Score else 0 end) Score1,
max(case px when 2 then UserName else '' end) Player2,
max(case px when 2 then Score else 0 end) Score2,
max(case px when 3 then UserName else '' end) Player3,
max(case px when 3 then Score else 0 end) Score3,
max(case px when 4 then UserName else '' end) Player4,
max(case px when 4 then Score else 0 end) Score4,
max(Dealerid) Dealer
from
(
select t.* , px = (select count(1) from tb where TableID = t.TableID and CharacterID < t.CharacterID) + 1 from tb t
) m
group by TableID
/*
TableID Player1 Score1 Player2 Score2 Player3 Score3 Player4 Score4 Dealer
----------- -------------------- ----------- -------------------- ----------- -------------------- ----------- -------------------- ----------- -----------
6 mult_nihao1 8 mult_nihao2 0 mult_nihao3 0 mult_nihao4 0 12
7 mult_nihao1 0 mult_nihao2 0 mult_nihao3 10 mult_nihao4 0 12
8 mult_nihao1 0 mult_nihao2 0 mult_nihao3 0 mult_nihao20 10 12
（所影响的行数为 3 行）
*/
--SQL 2000动态，指相同tableid其CharacterID个数不定
declare @sql varchar(8000)
set @sql = 'select TableID '
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then UserName else '''' end) [Player' + cast(px as varchar) + ']'
+ ' , max(case px when ''' + cast(px as varchar) + ''' then Score else 0 end) [Score' + cast(px as varchar) + ']'
from (select distinct px from (select t.* , px = (select count(1) from tb where TableID = t.TableID and CharacterID < t.CharacterID) + 1 from tb t) m) as a
set @sql = @sql + ',max(Dealerid) Dealer from (select t.* , px = (select count(1) from tb where TableID = t.TableID and CharacterID < t.CharacterID) + 1 from tb t) m group by TableID'
exec(@sql)
/*
TableID Player1 Score1 Player2 Score2 Player3 Score3 Player4 Score4 Dealer
----------- -------------------- ----------- -------------------- ----------- -------------------- ----------- -------------------- ----------- -----------
6 mult_nihao1 8 mult_nihao2 0 mult_nihao3 0 mult_nihao4 0 12
7 mult_nihao1 0 mult_nihao2 0 mult_nihao3 10 mult_nihao4 0 12
8 mult_nihao1 0 mult_nihao2 0 mult_nihao3 0 mult_nihao20 10 12
*/
drop table tbdawugui 2010-06-09
- 打赏
- 举报
create table tb(TableID int,CharacterID int,UserName varchar(20) , Score int)
insert into tb values(6 ,12 ,'mult_nihao1' ,8 )
insert into tb values(6 ,13 ,'mult_nihao2' ,-8)
insert into tb values(6 ,14 ,'mult_nihao3' ,0)
insert into tb values(6 ,15 ,'mult_nihao4' ,0)
insert into tb values(7 ,12 ,'mult_nihao1' ,-10)
insert into tb values(7 ,13 ,'mult_nihao2' ,0)
insert into tb values(7 ,14 ,'mult_nihao3' ,10)
insert into tb values(7 ,15 ,'mult_nihao4' ,0)
insert into tb values(8 ,12 ,'mult_nihao1' ,0)
insert into tb values(8 ,13 ,'mult_nihao2' ,-10)
insert into tb values(8 ,14 ,'mult_nihao3' ,0)
insert into tb values(8 ,22 ,'mult_nihao20',10)
go
--SQL 2000静态，指相同tableid最多四个不同的CharacterID
select TableID,
max(case px when 1 then UserName else '' end) Player1,
max(case px when 1 then Score else 0 end) Score1,
max(case px when 2 then UserName else '' end) Player2,
max(case px when 2 then Score else 0 end) Score2,
max(case px when 3 then UserName else '' end) Player3,
max(case px when 3 then Score else 0 end) Score3,
max(case px when 4 then UserName else '' end) Player4,
max(case px when 4 then Score else 0 end) Score4
from
(
select t.* , px = (select count(1) from tb where TableID = t.TableID and CharacterID < t.CharacterID) + 1 from tb t
) m
group by TableID
/*
TableID Player1 Score1 Player2 Score2 Player3 Score3 Player4 Score4
----------- -------------------- ----------- -------------------- ----------- -------------------- ----------- -------------------- -----------
6 mult_nihao1 8 mult_nihao2 0 mult_nihao3 0 mult_nihao4 0
7 mult_nihao1 0 mult_nihao2 0 mult_nihao3 10 mult_nihao4 0
8 mult_nihao1 0 mult_nihao2 0 mult_nihao3 0 mult_nihao20 10
（所影响的行数为 3 行）
*/
--SQL 2000动态，指相同tableid其CharacterID个数不定
declare @sql varchar(8000)
set @sql = 'select TableID '
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then UserName else '''' end) [Player' + cast(px as varchar) + ']'
+ ' , max(case px when ''' + cast(px as varchar) + ''' then Score else 0 end) [Score' + cast(px as varchar) + ']'
from (select distinct px from (select t.* , px = (select count(1) from tb where TableID = t.TableID and CharacterID < t.CharacterID) + 1 from tb t) m) as a
set @sql = @sql + ' from (select t.* , px = (select count(1) from tb where TableID = t.TableID and CharacterID < t.CharacterID) + 1 from tb t) m group by TableID'
exec(@sql)
/*
TableID Player1 Score1 Player2 Score2 Player3 Score3 Player4 Score4
----------- -------------------- ----------- -------------------- ----------- -------------------- ----------- -------------------- -----------
6 mult_nihao1 8 mult_nihao2 0 mult_nihao3 0 mult_nihao4 0
7 mult_nihao1 0 mult_nihao2 0 mult_nihao3 10 mult_nihao4 0
8 mult_nihao1 0 mult_nihao2 0 mult_nihao3 0 mult_nihao20 10
*/
drop table tb
s8848 2010-06-09
- 打赏
- 举报
select CharacterID =(select * from table where tableid='6')
from table group by tableid
from table group by tableid
luoyoumou 2010-06-09
- 打赏
- 举报
[Quote=引用 1 楼 guguda2008 的回复:]
行转列，交给楼下
[/Quote]
-- 行转列，交给楼下
行转列，交给楼下
[/Quote]
-- 行转列，交给楼下
guguda2008 2010-06-09
- 打赏
- 举报
行转列，交给楼下
