27,579
社区成员
发帖
与我相关
我的任务
分享
create table student
(
s_id int primary key not null identity(1,1),
s_name nvarchar(10) not null,
s_age int not null,
s_sex nvarchar(2) not null,
)
create table subject
(
j_id int primary key not null identity(1,1),
s_id int FOREIGN KEY (s_id) REFERENCES student,
j_name nvarchar(10) not null
)
insert into student values('张三',18,'男')
insert into student values('李四',19,'男')
insert into student values('王红',18,'女')
insert into student values('李茜',19,'女')
insert into subject values(1,'语文')
insert into subject values(1,'数学')
insert into subject values(2,'英语')
insert into subject values(2,'体育')
insert into subject values(2,'物理')
insert into subject values(3,'音乐')
insert into subject values(3,'数学')
select * from student
select * from subject
SELECT *
FROM student s
OUTER APPLY ( SELECT TOP 1 j_name
FROM [subject]
WHERE s_id = s.s_id
) J
s_id s_name s_age s_sex j_name
----------- ---------- ----------- ----- ----------
1 张三 18 男 语文
2 李四 19 男 英语
3 王红 18 女 音乐
4 李茜 19 女 NULL
SELECT * ,
( SELECT TOP 1
j_name
FROM dbo.subject b
WHERE b.s_id = a.s_id
ORDER BY j_id
)AS j_name
FROM dbo.student a