学习记录–查询没有学全所有课的同学的学号、姓名、以及未学科目

逛贴吧的时候遇到了这样一个问题

这样的表结构需要【查询没有学全所有课的同学的学号、姓名、以及未学科目】

————脚本————————-

create table [dbo].[课程表](
    课程id [int] null,
    课程名称 [nvarchar](50) null,
    讲师id [int] null
) on [primary]

create table [dbo].成绩表(
    成绩id [int] null,
    课程id [int] null,
    成绩 [int] null
) on [primary]

create table [dbo].学生表(
    学生id [int] null,
    学生名称 [nvarchar](50) null,
    年龄 [int] null,
    性别 [nvarchar](50) null
) on [primary]


create table [dbo].讲师表(
    讲师id [int] null,
    讲师名称 [nvarchar](50) null
) on [primary]


insert [dbo].[成绩表] ([成绩id], [课程id], [成绩]) values (1, 1, 20)
go
insert [dbo].[成绩表] ([成绩id], [课程id], [成绩]) values (1, 2, 20)
go
insert [dbo].[成绩表] ([成绩id], [课程id], [成绩]) values (1, 3, 20)
go
insert [dbo].[成绩表] ([成绩id], [课程id], [成绩]) values (2, 1, 20)
go
insert [dbo].[成绩表] ([成绩id], [课程id], [成绩]) values (2, 2, 20)
go
insert [dbo].[成绩表] ([成绩id], [课程id], [成绩]) values (3, 3, 20)
go


insert [dbo].[讲师表] ([讲师id], [讲师名称]) values (1, n'张老师')
go
insert [dbo].[讲师表] ([讲师id], [讲师名称]) values (2, n'李老师')
go
insert [dbo].[讲师表] ([讲师id], [讲师名称]) values (3, n'王老师')
go
insert [dbo].[讲师表] ([讲师id], [讲师名称]) values (4, n'宋老师')
go

insert [dbo].[课程表] ([课程id], [课程名称], [讲师id]) values (1, n'语文', 1)
go
insert [dbo].[课程表] ([课程id], [课程名称], [讲师id]) values (2, n'数学', 2)
go
insert [dbo].[课程表] ([课程id], [课程名称], [讲师id]) values (3, n'英语', 3)
go
insert [dbo].[课程表] ([课程id], [课程名称], [讲师id]) values (4, n'体育', 4)
go

insert [dbo].[学生表] ([学生id], [学生名称], [年龄], [性别]) values (1, n'张三', 12, n'男')
go
insert [dbo].[学生表] ([学生id], [学生名称], [年龄], [性别]) values (2, n'李四', 12, n'男')
go
insert [dbo].[学生表] ([学生id], [学生名称], [年龄], [性别]) values (3, n'王五', 12, n'男')
go
insert [dbo].[学生表] ([学生id], [学生名称], [年龄], [性别]) values (4, n'赵六', 12, n'男')

解决问题，想法是【构造笛卡尔积】，然后通过【左连接】选出【成绩表】中不存在的【学生id】

  select    s.学生id,
            s.学生名称,
            s.年龄,
            s.性别,
            c.课程id,
            c.课程名称,
            c.讲师id
  from      dbo.学生表 s
  left join dbo.课程表 c on 1=1
  left join dbo.成绩表 on s.学生id = dbo.成绩表.学生id and 成绩表.课程id = c.课程id
  where dbo.成绩表.学生id is null

得到的结果：

接下来，我用的是sqlserver，所以使用【for xml】来构造

select b.学生id,
       b.学生名称,
       b.年龄,
       left(b.未学科目, len(b.未学科目) - 1) as 未学科目
  from (   select a.学生id,
                  a.学生名称,
                  a.年龄,
                  (   select z.课程名称 + ','
                        from (   select      s.学生id,
                                             s.学生名称,
                                             s.年龄,
                                             s.性别,
                                             c.课程id,
                                             c.课程名称,
                                             c.讲师id
                                   from      dbo.学生表 s
                                   left join dbo.课程表 c
                                     on 1        = 1
                                   left join dbo.成绩表
                                     on s.学生id   = dbo.成绩表.学生id
                                    and 成绩表.课程id = c.课程id
                                  where      dbo.成绩表.学生id is null) z
                       where z.学生id = a.学生id
                      for xml path('')) as 未学科目
             from (   select      s.学生id,
                                  s.学生名称,
                                  s.年龄,
                                  s.性别,
                                  c.课程id,
                                  c.课程名称,
                                  c.讲师id
                        from      dbo.学生表 s
                        left join dbo.课程表 c
                          on 1        = 1
                        left join dbo.成绩表
                          on s.学生id   = dbo.成绩表.学生id
                         and 成绩表.课程id = c.课程id
                       where      dbo.成绩表.学生id is null) a
            group by a.学生id,
                     a.学生名称,
                     a.年龄,
                     a.性别) as b;

最后结果

 

 

问题是解决了，但可能效率不高，毕竟笛卡尔积。