首页 诗词 字典 板报 句子 名言 友答 励志 学校 网站地图
当前位置: 首页 > 教程频道 > 数据库 > SQL Server >

这个如何查询? 学表类的。 #100% 结贴

2013-12-13 
这个怎么查询?学表类的。。。 ################################100% 结贴数据示例如下:T_STUDENT(学员信息表

这个怎么查询? 学表类的。。。 ################################100% 结贴
数据示例如下:

T_STUDENT(学员信息表)数据示例如下:

STUDENT_ID   NAME  SEX(0表示男、1表示女) AGE

1        张三    0              20

2        李四   1              18



   
T_CLASS(课程信息表)数据示例如下:(假定课程表的数据不会发生变化)

CLASS_ID        NAME

1                语文

2                数学

3                英语

  
T_SCORE(考试成绩表)数据示例如下:

SCORE_ID      STUDENT_ID      CLASS_ID    SCORE

1                   1          1           96

2                   1          2            90

3                   2           1           96



   
问题一:写出对应SQL语句,查询所有缺考的学员以及对应的缺考科目。

问题二 请采用SQL输出学员考试成绩清单(动态吧),并按照总分进行排序,如下结果:
姓名         语文         数学          英语           总分

王五         2          3           4             9
[解决办法]
先写第一个把:


create table T_STUDENT(
STUDENT_ID int , 
NAME varchar(20),  
SEX int,
AGE int)

insert into T_STUDENT
select 1        ,'张三',    0    ,          20 union all
select 2        ,'李四',   1     ,         18
   
create table T_CLASS(CLASS_ID   int, NAME varchar(20))

insert into T_CLASS
select 1               ,'语文' union all
select 2                ,'数学' union all
select 3                ,'英语'

create table T_SCORE(SCORE_ID  int,STUDENT_ID int,  CLASS_ID int, SCORE int)

insert into T_SCORE
select 1   ,      1   ,    1    ,     96 union all
select 2  ,      1    ,     2  ,    90 union all
select 3  ,       2   ,     1   ,  96
go



select *
from 
(
select ts.STUDENT_ID,tc.CLASS_ID
from T_STUDENT ts
inner join T_CLASS tc
        on 1=1


except 
select STUDENT_ID,CLASS_ID
from T_SCORE  
)t
inner join  T_STUDENT ts
        on ts.STUDENT_ID = t.STUDENT_ID
inner join T_CLASS tc
        on tc.CLASS_ID = t.CLASS_ID
order by t.STUDENT_ID
/*
STUDENT_IDCLASS_IDSTUDENT_IDNAMESEXAGECLASS_IDNAME
131张三0203英语
232李四1183英语
222李四1182数学
*/  

    


[解决办法]
引用:
Quote: 引用:

先写第一个把:

create table T_STUDENT(
STUDENT_ID int , 
NAME varchar(20),  
SEX int,
AGE int)

insert into T_STUDENT
select 1        ,'张三',    0    ,          20 union all
select 2        ,'李四',   1     ,         18
   
create table T_CLASS(CLASS_ID   int, NAME varchar(20))

insert into T_CLASS
select 1               ,'语文' union all
select 2                ,'数学' union all
select 3                ,'英语'

create table T_SCORE(SCORE_ID  int,STUDENT_ID int,  CLASS_ID int, SCORE int)

insert into T_SCORE
select 1   ,      1   ,    1    ,     96 union all
select 2  ,      1    ,     2  ,    90 union all
select 3  ,       2   ,     1   ,  96
go



select *
from 
(
select ts.STUDENT_ID,tc.CLASS_ID
from T_STUDENT ts
inner join T_CLASS tc
        on 1=1
except 
select STUDENT_ID,CLASS_ID
from T_SCORE  
)t
inner join  T_STUDENT ts
        on ts.STUDENT_ID = t.STUDENT_ID
inner join T_CLASS tc
        on tc.CLASS_ID = t.CLASS_ID
order by t.STUDENT_ID
/*
STUDENT_IDCLASS_IDSTUDENT_IDNAMESEXAGECLASS_IDNAME
131张三0203英语
232李四1183英语
222李四1182数学
*/  

    

等着小当家你来啊 ,,一个就写了这么多, 看来40分少了点


呵呵,简化一下:
select *
from T_STUDENT ts
inner join T_CLASS tc
        on 1=1
where not exists(select 1 from T_SCORE  t
 where t.STUDENT_ID = ts.STUDENT_ID and
   t.CLASS_ID = tc.CLASS_ID)

/*
STUDENT_IDNAMESEXAGECLASS_IDNAME
1张三0203英语
2李四1182数学
2李四1183英语
*/  

[解决办法]

create table T_STUDENT(
STUDENT_ID int , 
NAME varchar(20),  
SEX int,
AGE int)

insert into T_STUDENT
select 1        ,'张三',    0    ,          20 union all
select 2        ,'李四',   1     ,         18
   
create table T_CLASS(CLASS_ID   int, NAME varchar(20))

insert into T_CLASS
select 1               ,'语文' union all
select 2                ,'数学' union all
select 3                ,'英语'

create table T_SCORE(SCORE_ID  int,STUDENT_ID int,  CLASS_ID int, SCORE int)

insert into T_SCORE
select 1   ,      1   ,    1    ,     96 union all
select 2  ,      1    ,     2  ,    90 union all
select 3  ,       2   ,     1   ,  96


go

declare @sql varchar(max)='';

select @sql=@sql+ N', sum(case when className=N'''+name+''' then score else 0 end) as '+name

from [dbo].[T_CLASS]

set @sql='
with t as
(
select b.NAME,c.NAME className,a.SCORE
from [dbo].[T_SCORE] a
join [T_STUDENT] b on a.STUDENT_ID=b.STUDENT_ID
join [dbo].[T_CLASS] c on a.CLASS_ID=c.CLASS_ID
)
select name
'+@sql+',sum(score) as [总分]
from t 
group by name';

Exec (@sql)


我来写一下第二道
[解决办法]
第2个:


呵呵,第二个:

declare @sql varchar(1000)

set @sql = ''

select @sql = @sql + 
              ',max(case when tc.name= '''+ NAME +''' then SCORE else null end) as ' + NAME               
from T_CLASS

set @sql = 'select ts.name '+@sql+',sum(SCORE) as 总分 '+


           'from T_STUDENT ts
            inner join T_SCORE t
                    on ts.student_id = t.student_id
            inner join T_CLASS tc
                    on tc.class_id = t.class_id
            group by ts.student_id,ts.name
            order by 总分 desc'

exec(@sql)
/*
name语文数学英语总分
张三    9690NULL186
李四    96NULLNULL96
*/

      

热点排行