假设有三个表，学生表，明细表（男），明细表（女）【明细表用来存成绩】
(如果sex=男，就要关联T_Man表，反之则关联T_Woman表)T_Student
ID Name Sex DetailID
1 小张 男 1
2 小王 女 1
3 小李 男 2
4 小赵 女 2
T_Man
DetailID Score
1 50
2 80
T_Woman
DetailID Score
1 90
2 30
需求如下：
查询学生ID，姓名（Name），性别（Sex）,分数（Score），是否及格（isPass）（及格返回1，不及格返回0）结果如下：
ID Name Sex Score isPass
1 小张 男 50 0
2 小王 女 90 1
3 小李 男 80 1
4 小赵 女 30 0
有些难度吧？我的可用分不多了，大家帮忙个忙吧
select * from T_student A
left join
(select '男' sex,* from T_Man
union all
select '女' sex,* from T_Woman)B
on A.sex=B.sex and A.detailid=B.detailid
select a.*,b.Score,case when srore<60 then 0 else 1 end isPass from T_student A
left join
(select '男' sex,* from T_Man
union all
select '女' sex,* from T_Woman)B
on A.sex=B.sex and A.detailid=B.detailid
create table t1
(
id int,
name varchar(10),
sex varchar(5),
did int
)
insert into t1
select 1,'小张','男',1 union all
select 2,'小王','女',1 union all
select 3,'小李','男',2 union all
select 4,'小赵','女',2
create table t2
(
did int,
score int
)
insert into t2
select 1,50 union all
select 2,80
create table t3
(
did int,
score int
)
insert into t3
select 1,90 union all
select 2,30;with aaa as
(select id,name,sex,case when sex='男' then (select score from t2 where t2.did=t1.did)
else (select score from t3 where t3.did=t1.did) end as score from t1)
select *,case when score<60 then 0 else 1 end as isPass from aaa----------------------
id name sex score isPass
1 小张 男 50 0
2 小王 女 90 1
3 小李 男 80 1
4 小赵 女 30 0
(
id int,
name nvarchar(10),
sex nvarchar(2),
detailid int
)
insert into #T_Student
select 1,'小张', '男',1 union all
select 2,'小王', '女',1 union all
select 3,'小李', '男',2 union all
select 4,'小赵', '女',2create table #T_Man
(
DetailID int,
Score int
)
insert into #T_Man
select 1,50 union all
select 2,80create table #T_Woman
(
DetailID int,
Score int
)
insert into #T_Woman
select 1,90 union all
select 2,30select *,case when score>60 then 1 else 0 end 'isPass' from (
select t.id,t.name,t.sex,m.Score from #T_Student t
left join #T_Man m on t.detailid=m.detailid
where t.sex='男'
union all
select t.id,t.name,t.sex,w.Score from #T_Student t
left join #T_Woman w on t.detailid=w.detailid
where t.sex='女' ) tb
order by id
drop table T_Student
GO
create table T_Student
(ID int ,Name varchar(50),Sex varchar(50),DetailID int)
insert into T_Student values(1,'小张','男',1)
insert into T_Student values(2,'小王','女',1)
insert into T_Student values(3,'小李','男',2)
insert into T_Student values(4,'小赵','女',2)
if OBJECT_ID('T_Man') IS not null
drop table T_Man
GO
create table T_Man
(DetailID int,Score int)
insert into T_Man values(1,50)
insert into T_Man values(2,80)
if OBJECT_ID('T_Woman') IS not null
drop table T_Woman
GO
create table T_Woman
(DetailID int,Score int)
insert into T_Woman values(1,90)
insert into T_Woman values(2,30)--ID Name Sex Score isPass
--1 小张 男 50 0
--2 小王 女 90 1
--3 小李 男 80 1
--4 小赵 女 30 0
-----------------------------------------------------
select ID,Name,Sex,
(case when Sex='男' then (select Score from T_Man where s.DetailID=T_Man.DetailID) when Sex='女' then (select Score from T_Woman where s.DetailID=T_Woman.DetailID) else ''
end ) as 'Score' ,
(case when (case when Sex='男' then (select Score from T_Man where s.DetailID=T_Man.DetailID) when Sex='女' then (select Score from T_Woman where s.DetailID=T_Woman.DetailID) else ''
end )>60 then 1
else 0 end
)as isPass
from T_Student s ID Name Sex Score isPass
----------- -------------------------------------------------- -------------------------------------------------- ----------- -----------
1 小张 男 50 0
2 小王 女 90 1
3 小李 男 80 1
4 小赵 女 30 0(4 行受影响)