一个难题请教，请大侠帮忙

2010-06-09 06:53:59 167 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

用Multisim仿真石英晶体振荡电路：从等效模型到频响曲线实战本文通过Multisim软件，从构建石英晶体等效电路模型入手，详细演示了如何仿真分析其电抗频率特性曲线，并实战搭建与调试皮尔斯振荡电路。文章深入探讨了振荡必须工作在串联与并联谐振点之间感性区的原因，为电子设计者提供了从理论到仿真的完整可视化学习路径。阅读详情

有以下一个表结构
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

...全文

Halcon实战：基于图像清晰度自动筛选最佳对焦图片本文详细介绍了如何使用Halcon实现基于图像清晰度的自动筛选最佳对焦图片技术。通过分析灰度值统计和标准差等核心指标，结合实战代码示例，帮助开发者高效处理工业检测中的对焦问题，提升自动化检测的准确性和效率。阅读详情

Tt0012310127

应用实例 27,579 成员 68,540 内容

+ 关注 + 加入社区

切换为时间正序

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 tb

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

dawugui 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

luoyoumou 2010-06-09

打赏
举报

[Quote=引用 1 楼 guguda2008 的回复:]
行转列，交给楼下
[/Quote]

-- 行转列，交给楼下

guguda2008 2010-06-09

打赏
举报

行转列，交给楼下

一个难题请教，请大侠帮忙

相关推荐