三张表的查询，在线等

2009-07-28 11:23:24 233 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

JavaWeb项目--【在线音乐播放器】onlineMusicPlayer 项目简介基于Java Servlet 构建的在线音乐服务器。项目功能登录、注册上传音乐删除某一个音乐信息删除选中的音乐信息查询音乐(包含查找指定/模糊匹配的音乐) 添加音乐到“喜欢列表”。查询喜欢的音乐(包含查找指定/模糊匹配的音乐) 项目技术简单的Web服务器设计能力前端知识：HTML+CSS+JavaScript 后端：Servlet 的使用强化 HTTP 协议的理解数据库：mysql Java集合的使用 json 的使用项目结构项目整体基于HTTP协议。前端使用H 阅读详情

现有三张表，如下：
create table Student
(
stuID int primary key,
stuName varchar(20)
)
create table Course
(
couID int primary key,
couName varchar(20)
)
create table Store
(
stuID int not null,
couID int not null,
store int
)

insert into Student values (001,'张三')
insert into Student values (002,'李四')
insert into Student values (003,'王五')
insert into Student values (004,'刘二')

insert into Course values (1001,'英语')
insert into Course values (1002,'数学')
insert into Course values (1003,'物理')
insert into Course values (1004,'语文')
insert into Course values (1005,'地理')

insert into Store values (004,1001,90)
insert into Store values (004,1002,91)
insert into Store values (004,1003,93)
insert into Store values (004,1004,94)
insert into Store values (004,1005,95)

要求查询后形成的报表如下：
学生姓名英语数学物理语文地理
刘二 90 91 93 94 95
王五 89 89 89 89 89

...全文

DM达梦数据库远程客户端工具连接6001错误，disql可以正常连接 DM达梦数据库远程客户端工具连接6001错误，disql可以正常连接阅读详情

fastrain

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

zjybushiren88888 2009-08-03

打赏
举报

路过.

lonelyk 2009-08-03

打赏
举报

行列转换有什么要注意的地方没有?我还没用过嘞

phh1989 2009-08-02

打赏
举报

[Quote=引用 6 楼 guguda2008 的回复:]
看来有必要写个行列转换生成器抢分了
[/Quote]
今天就看到不下三次了

mhc1314 2009-08-02

打赏
举报

学习

zhoujie_bobo 2009-08-02

打赏
举报

看来有必要写个行列转换生成器抢分了

这个倒是牛逼啊

sdhdy 2009-07-28

打赏
举报

/*

问题：假设有张学生成绩表(tb)如下:

姓名 课程 分数

张三 语文 74

张三 数学 83

张三 物理 93

李四 语文 74

李四 数学 84

李四 物理 94

想变成(得到如下结果)： 

姓名 语文 数学 物理 

---- ---- ---- ----

李四 74   84   94

张三 74   83   93

-------------------

*/



create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)

insert into tb values('张三' , '语文' , 74)

insert into tb values('张三' , '数学' , 83)

insert into tb values('张三' , '物理' , 93)

insert into tb values('李四' , '语文' , 74)

insert into tb values('李四' , '数学' , 84)

insert into tb values('李四' , '物理' , 94)

go



--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)

select 姓名,

  max(case 课程 when '语文' then 分数 else 0 end) 语文,

  max(case 课程 when '数学' then 分数 else 0 end) 数学,

  max(case 课程 when '物理' then 分数 else 0 end) 物理

from tb

group by 姓名



--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)

declare @sql varchar(8000)

set @sql = 'select 姓名 '

select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'

from (select distinct 课程 from tb) as a

set @sql = @sql + ' from tb group by 姓名'

exec(@sql) 



--SQL SERVER 2005 静态SQL。

select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b



--SQL SERVER 2005 动态SQL。

declare @sql varchar(8000)

select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程

set @sql = '[' + @sql + ']'

exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')



---------------------------------



/*

问题：在上述结果的基础上加平均分，总分，得到如下结果：

姓名 语文 数学 物理 平均分 总分 

---- ---- ---- ---- ------ ----

李四 74   84   94   84.00  252

张三 74   83   93   83.33  250

*/



--SQL SERVER 2000 静态SQL。

select 姓名 姓名,

  max(case 课程 when '语文' then 分数 else 0 end) 语文,

  max(case 课程 when '数学' then 分数 else 0 end) 数学,

  max(case 课程 when '物理' then 分数 else 0 end) 物理,

  cast(avg(分数*1.0) as decimal(18,2)) 平均分,

  sum(分数) 总分

from tb

group by 姓名



--SQL SERVER 2000 动态SQL。

declare @sql varchar(8000)

set @sql = 'select 姓名 '

select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'

from (select distinct 课程 from tb) as a

set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'

exec(@sql) 



--SQL SERVER 2005 静态SQL。

select m.* , n.平均分 , n.总分 from

(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,

(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n

where m.姓名 = n.姓名



--SQL SERVER 2005 动态SQL。

declare @sql varchar(8000)

select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程

exec ('select m.* , n.平均分 , n.总分 from

(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m , 

(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n

where m.姓名 = n.姓名')



drop table tb    



------------------

------------------



/*

问题：如果上述两表互相换一下：即表结构和数据为：

姓名 语文 数学 物理

张三 74　　83　　93

李四 74　　84　　94

想变成(得到如下结果)： 

姓名 课程 分数 

---- ---- ----

李四 语文 74

李四 数学 84

李四 物理 94

张三 语文 74

张三 数学 83

张三 物理 93

--------------

*/



create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)

insert into tb values('张三',74,83,93)

insert into tb values('李四',74,84,94)

go



--SQL SERVER 2000 静态SQL。

select * from

(

 select 姓名 , 课程 = '语文' , 分数 = 语文 from tb 

 union all

 select 姓名 , 课程 = '数学' , 分数 = 数学 from tb

 union all

 select 姓名 , 课程 = '物理' , 分数 = 物理 from tb

) t

order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end



--SQL SERVER 2000 动态SQL。

--调用系统表动态生态。

declare @sql varchar(8000)

select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'

from syscolumns 

where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列

order by colid asc

exec(@sql + ' order by 姓名 ')



--SQL SERVER 2005 动态SQL。

select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t



--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。



--------------------

/*

问题：在上述的结果上加个平均分，总分，得到如下结果：

姓名 课程   分数

---- ------ ------

李四 语文   74.00

李四 数学   84.00

李四 物理   94.00

李四 平均分 84.00

李四 总分   252.00

张三 语文   74.00

张三 数学   83.00

张三 物理   93.00

张三 平均分 83.33

张三 总分   250.00

------------------

*/



select * from

(

 select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb 

 union all

 select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb

 union all

 select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb

 union all

 select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb

 union all

 select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb

) t

order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end



drop table tb

fastrain 2009-07-28

打赏
举报

自己先顶一个

olddown 2009-07-28

打赏
举报

行列转换，这样的帖子很多的，自己找一下。

fastrain 2009-07-28

打赏
举报

[Quote=引用 6 楼 guguda2008 的回复:]
看来有必要写个行列转换生成器抢分了
[/Quote]

高手，那你写个，大家都方便了。我也好好学习学习

linguojin11 2009-07-28

打赏
举报

行转列..

zhengduan964532 2009-07-28

打赏
举报





----drop table student

create table Student 

( 

stuID int primary key, 

stuName varchar(20) 

) 

create table Course 

( 

couID int primary key, 

couName varchar(20) 

) 

create table Store 

( 

stuID int not null, 

couID int not null, 

store int 

) 



insert into Student values (001,'张三') 

insert into Student values (002,'李四') 

insert into Student values (003,'王五') 

insert into Student values (004,'刘二') 



insert into Course values (1001,'英语') 

insert into Course values (1002,'数学') 

insert into Course values (1003,'物理') 

insert into Course values (1004,'语文') 

insert into Course values (1005,'地理') 



insert into Store values (004,1001,90) 

insert into Store values (004,1002,91) 

insert into Store values (004,1003,93) 

insert into Store values (004,1004,94) 

insert into Store values (004,1005,95) 











select distinct stuname,

       英语=max(case when couname='英语' then store else 0 end),

       数学=max(case when couname='数学' then store else 0 end),

       物理=max(case when couname='物理' then store else 0 end),

       语文=max(case when couname='语文' then store else 0 end),

       地理=max(case when couname='地理' then store else 0 end)

       from (

select a.stuID ,c.couname,a.stuname,b.store from   store b  

left join  Student a on a.stuID =b.stuid 

left join course c on b.couid=c.couid )c

group by stuName

华夏小卒 2009-07-28

打赏
举报

[Quote=引用 6 楼 guguda2008 的回复:]
看来有必要写个行列转换生成器抢分了
[/Quote]

呵呵 o(∩_∩)o...

guguda2008 2009-07-28

打赏
举报

看来有必要写个行列转换生成器抢分了

仙道彰 2009-07-28

打赏
举报

上面的都正确

feixianxxx 2009-07-28

打赏
举报

create table Student 

( 

stuID int primary key, 

stuName varchar(20) 

) 

create table Course 

( 

couID int primary key, 

couName varchar(20) 

) 

create table Store 

( 

stuID int not null, 

couID int not null, 

store int 

) 



insert into Student values (001,'张三') 

insert into Student values (002,'李四') 

insert into Student values (003,'王五') 

insert into Student values (004,'刘二') 



insert into Course values (1001,'英语') 

insert into Course values (1002,'数学') 

insert into Course values (1003,'物理') 

insert into Course values (1004,'语文') 

insert into Course values (1005,'地理') 



insert into Store values (004,1001,90) 

insert into Store values (004,1002,91) 

insert into Store values (004,1003,93) 

insert into Store values (004,1004,94) 

insert into Store values (004,1005,95) 

--2005的方法

select * from 

(select stuName,couName,store

from Student st join Store s on st.stuID=s.stuID join Course  c on s.couID=c.couID) t

pivot (

  max(store) for  couname in (英语 ,数学, 物理, 语文, 地理 )

)p

--2000的方法

select stuName,

英语=MAX(case when  couname='英语' then Store else 0 end),

数学=MAX(case when  couname='数学' then Store else 0 end),

物理=MAX(case when  couname='物理' then Store else 0 end),

语文=MAX(case when  couname='语文' then Store else 0 end),

地理=MAX(case when  couname='地理' then Store else 0 end)

from 

(select stuName,couName,store

from Student st join Store s on st.stuID=s.stuID join Course  c on s.couID=c.couID) t

group by  stuName

/*

stuName              英语          数学          物理          语文          地理

-------------------- ----------- ----------- ----------- ----------- -----------

刘二                   90          91          93          94          95



*/

三张表的查询，在线等

相关推荐