6.交叉连接,自连接和联合查询

一.交叉连接
格式:
select * from table1 cross join table2
笛卡尔集

交叉连接是没条件的.
针对案例student库中的表student和class表进行交叉连接,然后和score表进行左外连接,得出没参加考试的学生.

交叉连接有两种方式:效果一样.
A.select * from student,class
B.select * from student cross join class

统计出一张学生成绩统计表,并按顺序排列.
select t1.sname,t1.total --可以加上平均分
from student,    
    (
    select sid ,sum(score) as 'total'
    from score
    group by sid
    ) as t1
where student.sid=t1.sid
order by t1.total desc

--学号,姓名,sql server ,电子技术,移动开发,总分,均分

select t1.sid as '学号',t1.sname as '姓名',t2.[SQL server],t2.移动开发,t2.电子技术,t2.总分,t2.均分
from student as t1,
    (select sid as '学号',
        SUM(case when cid=1001 then score else 0 end) as 'SQL server',
        SUM(case when cid=1002 then score else 0 end) as '移动开发',
        SUM(case when cid=1003 then score else 0 end) as '电子技术',
        SUM(score) as '总分',
        SUM(score)/3 as '均分'
    from score
    group by sid
    ) as t2
where t1.sid = t2.学号
order by t2.总分 desc

二.自连接(boss表)
1.统计员工的下属有多少.
    select t1.staff_id,t1.staff_name,count(*) as '下属人数'
    from boss t1
    inner join
    boss t2
    on t1.staff_id =t2.report_id
    group by t1.staff_id ,t1.staff_name
2.统计出哪些员工没有下属.
利用 左连接先把全部包括NULL的显示出来就容易了.
    select t1.staff_id,t1.staff_name,t2.staff_name
    from boss t1 left outer join boss t2
    on t1.staff_id=t2.report_id
然后对此结果集再分组 就可以得到全部有无下属的
    select t3.staff_id,t3.nameA,COUNT(t3.nameB) as '全部下属'
    from
    (
        select t1.staff_id,t1.staff_name as nameA,t2.staff_name as nameB
        from boss t1 left outer join boss t2
        on t1.staff_id=t2.report_id
    ) t3
    group by t3.staff_id,t3.nameA

然后加having 将0的查询出来,如下:
    select t3.staff_id as '工号',t3.nameA as '姓名',COUNT(t3.nameB) as '无下属者'
    from
    (
        select t1.staff_id,t1.staff_name as nameA,t2.staff_name as nameB
        from boss t1 left outer join boss t2
        on t1.staff_id=t2.report_id
    ) t3
    group by t3.staff_id,t3.nameA
    having COUNT(t3.nameB)=0
三.联合查询
select * from A union select * from B      --并集
select * from A intersect select * from B  --交集
select * from A except select * from B     --相减