sql三表联查问题
declare @sum varchar(3000)
数据库三表查询例题_数据库3表查询
数据库三表查询例题_数据库3表查询
数据库三表查询例题_数据库3表查询
select @sum=',sum(case when s.CID='+convert(varchar,id)+' then score else 0 end) as '+CName from course
exec
('select (select cname from Member where id=m.mid) as 姓名 '+@sum+' from Member m left joint Score s on m.id=s.mid group by mid'
)
select m.studentname 姓名,s.Chinese 语文,s.Math 数学,s.English 英语,s.History 历史
from Member m,Course c,Score s
where m.MID=s.MID
and c.CID=s.CID;
数据库三表连接查询怎么做
1、创建三张测试表；
create table test_a(aid int,aname varchar(20));
create table test_b(bid int,bname varchar(20));
create table test_c(aid int, bid int,value varchar(20));
2、三张表中分别插入数据；
insert into test_a values(1, 'aname1');
insert into test_b values(2, 'bname1');
insert into test_c values(1, 2, 'cvalue');
3、查询表中记录；
select 10, a. from test_a a
union all
select 20, b. from test_b b
union all
select from test_c c;
4、编写sql，进行三表关联；
select a.aname,b.bname,c.value
from test_c c join test_a a
on c.aid = a.aid
join test_b b
on c.bid = b.bid
select a.aname,b.bname,c.value
from c
inner join a
on c.aid=a.aid
inner join b
on c.bid=b.bid
有个简单的写法 select a.aname, b.bname c.value from a,b,c where a.aid = c.cid and c.bid = b.bid
sql三表联查，有三个表,通过三个表同的值查询其他结果，类似于 where a.id=b.id=c.id 请问怎么写？
select (你要查的) from a,b,c
where a.id = b.id and b.id = c.id and (你所要查询出来的条件)
b.id and b.id = c.id 只是把这些表连接起来了
select from
A inner join B on A.id=B.id
inner join C on A.id=C.id
where a.id=b.id and b.id = c.id
三表SQL查询题目求解
1:
select a.class_no, a.classname,g(b.entrance_math) as g_math
from class a , student b
where a.class_no = b.class_no
group by a.class_no, a.classname
Hing g(b.entrance_math) >= 60
2:
select b.stu_no, b.stuname, c.teacher_name
from class a , student b , teacher c
where a.class_no = b.class_no
And a.teacher_no = c.teacher_no
order by b.stu_no
3:
select a.class_no, a.class_name, c.teacher_name, c.teacher_major
from class a , teacher c
where a.teacher_no = c.teacher_no
and teacher_major = '计算机'
SQL三表连接查询
select a.,c.f from (select from a inner join b on a.a = b.a) as a inner join c on a.d = c.d
查询出表B的d=表C的d 所对应的f的值。
扩展资料：三个表左连接的sql语句
select
intro_web_page.id,
web_id,
web_name,
template_id ,
template_name,
template_param,
intro_web_page.sort_order
from intro_web_page left join intro_web on (web_id=intro_web.id)
left join intro_template on (intro_web_page.template_id=intro_template.id)
注：这里主要是看，当两个表和某一个表都有联系的时候，左连接left join on要怎么写。
更新语句
UPDATE issue INNER JOIN user ON `issue`.insert_uid and `user`.id INNER JOIN
(SELECT issue.id as id,issue.insert_uid as uid,`user`.school_id as school_id,school.area as area1 from issue,user,school
where issue.insert_uid=`user`.id and `user`.school_id=school.id) AS T1
ON issue.insert_uid=T1.uid
SET issue.area=T1.area1
求三表联合查询的SQL查询语句
车讯语句：select username,psw from (a1 left join a2 on a1.a1_id=a2.a1_id) left join a3 on a1.a1_id=a3.a1_id
这样写：
SELECT
S.SName AS 姓名, CS.CourseName AS 课程, C.Score AS 成绩
FROM Students AS S
INNER JOIN Score AS C ON (S.SCode = C.StudentID)
INNER JOIN Course AS CS ON (CS.CourseID = C.CourseID
扩展资料：SQL联合查询的分类
一、内连接查询：只查询左边表有且右边表也有的数据，本质上是依据外键关系，在笛卡尔积查询的基础上过滤出正确的数据。
语句有2种形式：
Select from dept ,emp where dept.id=emp.dept_id
Select from dept inner join emp on dept.id =emp.dept_id
二、外连接查询：外连接是用于查询俩边一边有一边没有的数据。
三、左外连接查询：在内连接的基础上增加上左边表有而右边表没有的数据
语句：Select from dept join emp on dept.id=emp.dept_id
四、右外连接：在内连接的基础上增加上右边表没有的记录
语句：Select from dept right join emp on dept.id =emp.dept_id
sql语句高手 现在有三张表，根据三表查询出一个结果，请问该怎么做，sql高手门请出手吧
这个问题很简单吧，我提供一个简单的解决方案，如下：
SELECT (SELECT [Name]
FROM [part]
WHERE [ID] = W.[部门ID]
) AS [部门]
,(SELECT [Name]
FROM [person]
WHERE [ID] = W.[值班人ID]
) AS [姓名]
,W.[开始时间]
,W.[结束时间]
FROM [duty] W
GROUP BY W.[部门ID]
,W.[值班人ID]
,W.[开始时间]
,W.[结束时间]
该问题是查询值班表，但查询的同时把值班人ID和部门ID换成中文名称，
因此，以值班表为主表来进行查询。语句如下：
select y.name,z.name,开始时间,结束时间 from duty,part y,person z
where 部门ID=y.ID and 值班人ID=z.ID;
select name from person where 部门ID in (select ID from part where name='要求查询的部门名称') 这样的？？// 你的查询条件是什么啊，查询的结果是ID还是名称啊，另外person表跟duty表貌似没什么关系啊~
select part.name,person.name,duty.开始时间,duty.结束时间 from person,part,duty where person.partID=part.id and person.partID=duty.部门ID
二楼那个是 结果。也可以用别名，像这个 select a.name, b.name as p_name, c.start_time, c.end_time from part a left join person b on a.id = b.partid left join duty c on a.id = c.值班人id
select name(部门), 姓名 ,开始时间 ,结束时间
from person,part,duty
where person.partID=part.id and person.partID=duty.部门ID
select part.name,person.name,duty.开始时间,duty.结束时间 from
duty left join person on duty.值班人ID=person.ID left join
part on duty.部门ID=part.ID
select b,name,c.name,a.开始时间,a.结束时间 from duty a,person b,part c where a.值班人ID=b.id and a.部门ID=c.ID
select b.name,a.name,c.start_date,c.end_date from person a,part b,duty c
where a.partID = b.ID and c.partID = b.ID and c.personID = a.ID