select a.number,b.parentname from @student a INNER JOIN @parent b on a.name = b.name where b.parentname = case b.parentname when b.parentname like 'father' then b.parentname case b.parentname when b.parentname like 'mother' then b.parentname case b.parentname when b.brother like 'mother' then b.parentname end
top 1,b表中每个学生的第一位家长
--2005SELECT A.number, B.parentname FROM @student AS A CROSS APPLY (SELECT TOP(1) * FROM @parent WHERE A.name=name) AS B
上面错了 SELECT A.number, B.parentname FROM @student AS A CROSS APPLY (SELECT TOP(1) * FROM @parent C WHERE A.name=NAME AND C.parentname IS NOT null ) AS B
单亲小孩应该也要加进去declare @student table ( number int, name char(20) ) insert into @student values(1,'York') insert into @student values(2,'Alan') insert into @student values(3,'Jim')declare @parent table ( name char(20), parentname char(20) ) insert into @parent values('York','York father') insert into @parent values('York','York mother') insert into @parent values('York','York brother')insert into @parent values('jim','jim father') insert into @parent values('jim','jim mother') insert into @parent values('jim','jim brother')insert into @parent values('Alan',null) insert into @parent values('Alan','Alan mother') insert into @parent values('Alan','Alan brother') SELECT A.number, B.parentname FROM @student AS A CROSS APPLY (SELECT TOP(1) * FROM @parent C WHERE A.name=NAME AND C.parentname IS NOT null ) AS B number parentname ----------- -------------------- 1 York father 2 Alan mother 3 jim father
select a.*,b.parentName from @student a INNER JOIN @parent b on a.name = b.name where b.parentName = (select top 1 parentName from @parent where name = b.name)number name parentName ----------- -------------------- -------------------- 1 York York father 3 Jim jim father 2 Alan Alan father (3 row(s) affected)
如果单亲或者没有父母的一算进去的话试试这个declare @student table ( number int, name char(20) ) insert into @student values(1,'York') insert into @student values(2,'Alan') insert into @student values(3,'Jim') insert into @student values(3,'Sadam') insert into @student values(3,'BUSH') declare @parent table ( name char(20), parentname char(20) ) insert into @parent values('York','York father') insert into @parent values('York','York mother') insert into @parent values('York','York brother') insert into @parent values('jim','jim father') insert into @parent values('jim','jim mother') insert into @parent values('jim','jim brother') insert into @parent values('Alan','Alan father') insert into @parent values('Alan','Alan mother') insert into @parent values('Sadam','Sadam father') select a.*,b.parentName from @student a Left JOIN @parent b on a.name = b.name where (b.parentName = (select top 1 parentName from @parent where name = b.name)) or (b.parentName is null)number name parentName ----------- -------------------- -------------------- 1 York York father 2 Alan Alan father 3 Jim jim father 3 Sadam Sadam father 3 BUSH NULL(5 row(s) affected)
select a.number,b.parentname
from @student a
INNER JOIN @parent b on a.name = b.name
where b.parentname = case b.parentname when b.parentname like 'father' then b.parentname
case b.parentname when b.parentname like 'mother' then b.parentname
case b.parentname when b.brother like 'mother' then b.parentname
end
A.number,
B.parentname
FROM @student AS A
CROSS APPLY (SELECT TOP(1) * FROM @parent WHERE A.name=name) AS B
上面错了
SELECT
A.number,
B.parentname
FROM @student AS A
CROSS APPLY (SELECT TOP(1) * FROM @parent C WHERE A.name=NAME AND C.parentname IS NOT null ) AS B
table
(
number int,
name char(20)
)
insert into @student values(1,'York')
insert into @student values(2,'Alan')
insert into @student values(3,'Jim')declare @parent
table
(
name char(20),
parentname char(20)
)
insert into @parent values('York','York father')
insert into @parent values('York','York mother')
insert into @parent values('York','York brother')insert into @parent values('jim','jim father')
insert into @parent values('jim','jim mother')
insert into @parent values('jim','jim brother')insert into @parent values('Alan',null)
insert into @parent values('Alan','Alan mother')
insert into @parent values('Alan','Alan brother') SELECT
A.number,
B.parentname
FROM @student AS A
CROSS APPLY (SELECT TOP(1) * FROM @parent C WHERE A.name=NAME AND C.parentname IS NOT null ) AS B
number parentname
----------- --------------------
1 York father
2 Alan mother
3 jim father
from @student a
INNER JOIN @parent b on a.name = b.name
where b.parentName = (select top 1 parentName from @parent where name = b.name)number name parentName
----------- -------------------- --------------------
1 York York father
3 Jim jim father
2 Alan Alan father (3 row(s) affected)
如果单亲或者没有父母的一算进去的话试试这个declare @student
table
(
number int,
name char(20)
)
insert into @student values(1,'York')
insert into @student values(2,'Alan')
insert into @student values(3,'Jim')
insert into @student values(3,'Sadam')
insert into @student values(3,'BUSH') declare @parent
table
(
name char(20),
parentname char(20)
)
insert into @parent values('York','York father')
insert into @parent values('York','York mother')
insert into @parent values('York','York brother') insert into @parent values('jim','jim father')
insert into @parent values('jim','jim mother')
insert into @parent values('jim','jim brother') insert into @parent values('Alan','Alan father')
insert into @parent values('Alan','Alan mother')
insert into @parent values('Sadam','Sadam father') select a.*,b.parentName
from @student a
Left JOIN @parent b on a.name = b.name
where (b.parentName = (select top 1 parentName from @parent where name = b.name)) or (b.parentName is null)number name parentName
----------- -------------------- --------------------
1 York York father
2 Alan Alan father
3 Jim jim father
3 Sadam Sadam father
3 BUSH NULL(5 row(s) affected)
最终采用了yangsnow_rain_wind的方法,因为liangCK的“CROSS APPLY”我看不懂,寒。。不过我认为他也是对的,谢谢你们。