a表:studentID workID store
1 1 98
1 2 95
2 1 90
2 3 80b表:
workID workName
1 zuoye
2 shiti
3 wenti查询结果:studentID workID workName store
1 1 zuoye 98
1 2 shiti 95
或者
studentID workID workName store
2 1 zuoye 98
2 3 wenti 80就是我想通过studentID,用workID关联查询得出结果。
请问SQL语句怎么实现
1 1 98
1 2 95
2 1 90
2 3 80b表:
workID workName
1 zuoye
2 shiti
3 wenti查询结果:studentID workID workName store
1 1 zuoye 98
1 2 shiti 95
或者
studentID workID workName store
2 1 zuoye 98
2 3 wenti 80就是我想通过studentID,用workID关联查询得出结果。
请问SQL语句怎么实现
create table #A
(
studentID int,
workID int,
store int
)
GO
insert into #A
select 1,1,98 union all
select 1,2,95 union all
select 2,2,90 union all
select 2,3,80
GOcreate Table #B
(
workID int,
workName varchar(50)
)
Go
insert into #B
select 1,'zuoye' union all
select 2,'shiti' union all
select 3,'wenti'
GO
select a.studentID,a.workID,b.workName,a.store from #A a,#B b
where a.workID=b.workID and a.studentID=1select a.studentID,a.workID,b.workName,a.store from #A a,#B b
where a.workID=b.workID and a.studentID=21 1 zuoye 98
1 2 shiti 95
2 2 shiti 90
2 3 wenti 80
declare @a table (studentid int,workid int,store int)
insert into @a select 1,1,98
union all select 1,2,95
union all select 2,1,90
union all select 2,3,80
declare @b table (workid int,workname nvarchar(10))
insert into @b select 1,'zuoye'
union all select 2,'shiti'
union all select 3,'wenti'
select a.studentid,a.workid,b.workname,a.store
from @a a join @b b on a.workid=b.workid and a.studentid=1
---
select a.studentid,a.workid,b.workname,a.store
from @a a join @b b on a.workid=b.workid and a.studentid=2
(4 行受影响)(3 行受影响)
studentid workid workname store
----------- ----------- ---------- -----------
1 1 zuoye 98
1 2 shiti 95(2 行受影响)studentid workid workname store
----------- ----------- ---------- -----------
2 1 zuoye 90
2 3 wenti 80(2 行受影响)
A.WORKID = B.WORKID