一个表还好用CASE处理
但是关联表上处理就不行了 ..实在不懂原理~~
create database T69
go
use T69
go
create table Score
(
id int primary key identity(1,1),
[sid] int not null,
Java int ,
[Sql] int
)
go
create table Minzu
(
id int primary key identity(1,1),
name varchar(50) not null
)
go
create table Student
(
id int primary key identity(1,1),
name varchar(20) not null,
age int not null,
mzId int not null,
)
go
alter table score
add constraint fk_score_sid foreign key ([sid])
references student(id)
alter table student
add constraint fk_student_mzid foreign key ([mzid])
references minzu(id)
go
insert into Minzu values('维吾尔族')
insert into Minzu values('汉族')
insert into Minzu values('苗族')
insert into Minzu values('藏族')
insert into Minzu values('朝鲜族')
insert into Minzu values('俄罗斯族')
go
insert into Student values('a',20,2)
insert into Student values('b',25,2)
insert into Student values('c',29,3)
insert into Student values('d',20,1)
insert into Student values('e',20,1)
insert into Student values('f',30,1)
insert into Student values('g',35,1)
insert into Student values('h',33,1)
insert into Student values('i',35,3)
insert into Student values('j',18,4)
insert into Student values('k',19,4)
insert into Student values('l',17,4)
insert into Student values('m',40,1)
insert into Student values('n',41,1)
insert into Student values('o',38,1)
insert into Student values('p',27,1)
insert into Student values('q',26,1)
insert into Student values('r',24,5)
insert into Student values('s',24,1)
go
insert into Score values(1,50,60)
insert into Score values(2,89,70)
insert into Score values(3,88,80)
insert into Score values(4,90,45)
insert into Score values(5,90,95)
insert into Score values(6,65,75)
insert into Score values(7,70,60)
insert into Score values(8,72,73)
insert into Score values(9,80,45)
insert into Score values(10,90,100)
insert into Score values(11,100,60)
insert into Score values(12,35,60)
insert into Score values(13,null,60)
insert into Score values(14,null,null)
求破! |