Question

sql 多表查询和行列转换，求帮助！

use master
go
if exists (select *from sysdatabases where name='stuDB')
drop database stuDB
create database stuDB
on primary
(
name='stuDB_data',
filename='F:\练习乱搞\stuDB_data.mdf',
size=5mb,
maxsize=100mb,
filegrowth=15%
)
log on
(
name='stuDB_log',
filename='f:\练习乱搞\stuDB_log.ldf',
size=2mb,
filegrowth=1mb
)
Use stuDB
if exists(select *from sysobjects where name='stuInfo')
drop table stuInfo
create table stuInfo
(
stuid int primary key identity(1,1),
stuname varchar(20), --学生姓名
)
insert stuInfo values('李四')
insert stuInfo values('王五')
insert stuInfo values('赵六')
select *from stuinfo
if exists (select *from sysobjects where name='stusubjec')
drop table stusubjec
create table stusubjec
(
stuid int primary key identity(1,1),
stusubname varchar(20), --科目名称
)
insert stusubjec values('.net')--1
insert stusubjec values('c语言')--2
insert stusubjec values('java')--3
insert stusubjec values('sql')--4
select *from stusubjec
if exists(select *from sysobjects where name='success')
drop table success
create table success
(
stuids int primary key identity(1,1),
stuid int references stuInfo(stuid),
stunameid int ,
stusuccess decimal(5,1) --成绩
)
alter table success
add constraint FK_stuNO
foreign key (stunameid) references stusubject(stuid)

insert into success values(1,1,88)
insert into success values(1,2,98)
insert into success values(1,3,68)
insert into success values(1,4,58)
insert into success values(2,1,58)
insert into success values(2,2,68)
insert into success values(2,3,78)
insert into success values(2,4,88)
insert into success values(3,1,98)
insert into success values(3,2,88)
insert into success values(3,3,78)
insert into success values(3,4,38)

想要转换成 （科目跟分数新添加后也跟着续上）

姓名 .net C语言 java sql
李四 88 98 68 58
王五 58 68 78 88
赵六 98 88 78 38

Answer 1

select distinct stu.stuname as 姓名,
max(case when subj.stusubname=='.net' then s.stusuccess else 0 end) as .net,
max(case when subj.stusubname=='C语言' then s.stusuccess else 0 end) as C语言,
max(case when subj.stusubname=='java' then s.stusuccess else 0 end) as java,
max(case when subj.stusubname=='sql' then s.stusuccess else 0 end) as sql
from success s
left join stuInfo stu on stu.stuid=s.stuid
left join stusubjec subj on subj.stuid=s.stunameid
group by stu.stuname

还有一种动态统计方法、

追问

那请问动态的怎么写呢？
静态的一旦添加了新的科目跟成绩后就还要手动添加。好像不太实用！
如果用view 表来弄会不会好弄点？可是我不太会写。求助！

追答

view只是把sql分步操作，不能提高性能，没必要。动态的网上例子挺多的，你参考下，现在没时间看。静态能实现功能先用起来，以后再升级。

Answer 2

咋没分。。。。

Answer 3

多表查询使用内连接,left join on 这种结构可以查询多条件选择的语句