sql 笔试题（二）

有三个表：
学生表：student  字段:stuID,chinesename,sex,scholarship(奖学金)
成绩表：grade    字段:graID,studID,courceID,grade
课程表：cource    字段:courceID,courceName,xuefen
求：查询分数都在90分以上的学生的stuID,chinesename
create table student(stuID int,chinesename nvarchar(20),sex int,scholarship money)
go
insert into student select 1,'allen',1,500 union all
select 2,'tom',1,0 union all
select 3,'jordan',1,1000 union all
select 4,'kobe',1,0
go
create table grade(graID int,stuID int,courceID int,grade decimal(4,1))
insert into grade select 1,4,1,80.8 union all
select 2,4,2,90 union all
select 3,4,3,100 union all
select 4,3,1,55 union all
select 5,3,2,86 union all
select 6,3,3,90.5 union all
select 7,1,1,95 union all
select 8,1,2,99.5 union all
select 9,1,3,96 union all
select 10,2,1,60 union all
select 11,2,2,66 union all
select 12,2,3,68
go
create table cource(courceID int,courceName nvarchar(20),xuefen int)
insert into cource select 1,'语文',5 union all
select 2,'数学',6 union all
select 3,'外语',6

select * from student where stuID in(
select stuID from grade
where grade>90
group by stuid having count(*)=(select count(*) from cource))

 

 

 

 

select s.stuID,s.chinesename from student s inner join grade g on s.stuID=g.stuID inner join cource c on c.courceID=g.courceID
where g.grade>=90 group by s.stuID,s.chinesename having count(g.courceID)=(select count(*) from cource)

 

学员分数表   
  姓名                     科目           分数   
  zhang   san         语文             60   
  lisi                   语文             71   
  wangwu               语文             61   
  zhaoliu             语文             90   
  .....       
    
    
  所得结果     
    
  姓名   
  zhang   san         语文             第四名   
  lisi                   语文             第二名   
  wangwu               语文             第三名   
  zhaoliu             语文             第一名   
              想破头也想不出来，敬请高人指点，谢谢！
 
 
  DECLARE   @T   TABLE(NAME   VARCHAR(50),SUBJECT   VARCHAR(20),GRADE   INT)   
  INSERT   @T   SELECT   ’zhang   san’,’语文’,60   
  UNION   ALL   SELECT   ’lisi’,’语文’,71   
  UNION   ALL   SELECT   ’wangwu’,’语文’,61   
  UNION   ALL   SELECT   ’zhaoliu’,’语文’,90   
    
  SELECT   
            NAME,   
            SUBJECT,   
            PM=(SELECT   COUNT(1)   FROM   @T   WHERE     GRADE>=A.GRADE)   
  FROM   @T   A   
 
 
 
 
 
 
 
 
 
SELECT   *   INTO   #TB_SCORE   
  FROM   
  (   
  SELECT   ’zhang   san’姓名,’语文’课程,’60’成绩   
  UNION   ALL   
  SELECT   ’lisi’姓名,’语文’课程,’71’成绩   
  UNION   ALL   
  SELECT   ’wangwu’姓名,’语文’课程,’61’成绩   
  UNION   ALL   
  SELECT   ’zhaoliu’姓名,’语文’课程,’90’成绩   
  UNION   ALL   
  SELECT   ’youran’姓名,’语文’课程,’60’成绩   
  )A   
    
  SELECT   *   FROM   #TB_SCORE   
    
  SELECT   姓名,课程,#TB_SCORE.成绩,’第’+CONVERT(VARCHAR(2),名次)+’名’AS   名次   
  FROM   #TB_SCORE   
  INNER   JOIN   
  (   
    SELECT   成绩,名次=(SELECT   COUNT(1)   FROM(SELECT   DISTINCT   成绩   FROM   #TB_SCORE)B   WHERE   B.成绩>=A.成绩)   
    FROM   
    (SELECT   DISTINCT   成绩   FROM   #TB_SCORE)A   
  )T   ON   T.成绩   =   #TB_SCORE.成绩   
  ORDER   BY   名次
  select   姓名=a.name,科目=a.kemo,   
  名次=case     when   (select   count(fenshu)from   Type   where   fenshu>a.fenshu)+1=1   then   ’第一名’   
  when   (select   count(fenshu)from   Type   where   fenshu>a.fenshu)+1=2   then   ’第二名’   
  when   (select   count(fenshu)from   Type   where   fenshu>a.fenshu)+1=3   then   ’第三名’   
  when   (select   count(fenshu)from   Type   where   fenshu>a.fenshu)+1=4   then   ’第四名’   
  end     from   Type   as   a

 

京尚良楷诚网络 sql笔试题

时间：2009年1月13日 作者：

  
 
群上的兄弟，谁帮忙做下这个题目：   
    
  一个简单的表TABLE   有100条以上的信息，其中包括：   
  产品                   颜色                           数量   
  产品1                   红色                           123   
  产品1                   蓝色                           126   
  产品2                   蓝色                           103   
  产品2                   红色                           NULL   
  产品2                   红色                           89   
  产品1                   红色                           203   
  。。。。。。。。。。。。   
  请用SQL语句完成以下问题：   
    
    
  1。按产品分类，仅列出各类商品中红色多于蓝色的商品名称及差额数量：   
    
    
    
    
    
  2。按产品分类，将数据按下列方式进行统计显示   
              产品                   红色                       蓝色   
 
 
 
 
按产品分类，将数据按下列方式进行统计显示   
  create   table   test(产品   varchar(10),颜色   varchar(10),数量   int)   
  insert   test   select   ’产品1’,’红色’,123   
  insert   test   select   ’产品1’,’蓝色’,126   
  insert   test   select   ’产品2’,’蓝色’,103   
  insert   test   select   ’产品2’,’红色’,NULL   
  insert   test   select   ’产品2’,’红色’,89   
  insert   test   select   ’产品1’,’红色’,203   
  select   *   from   test   
  select   产品,红色=sum(case   when   颜色=’红色’   then   数量   else   0   end),   
  蓝色=sum(case   when   颜色=’蓝色’   then   数量   else   0   end)   from   test   group   by   产品
 
 
 
  select   产品,sum(case   when   颜色=’红色’   then   数量   else   0   end)-sum(case   when   颜色=’蓝色’   then   数量   else   0   end)   as   差额   
  from   test     
  group   by   产品   
  having   sum(case   when   颜色=’红色’   then   数量   else   0   end)>sum(case   when   颜色=’蓝色’   then   数量   else   0   end)   
 
 
 
如果只有两种颜色也可以这样:   
  1:   
  select   产品,sum(IsNull(数量,0)   *     Replace(replace(颜色,’红色’,1),’蓝色’,-1))   
  from   test   
  group   by   产品   
  having   sum(IsNull(数量,0)   *     Replace(replace(颜色,’红色’,1),’蓝色’,-1))>0   
    
  2:   
  select   产品,sum(IsNull(数量,0)   *     Replace(replace(颜色,’红色’,1),’蓝色’,0))   
  ,sum(IsNull(数量,0)   *     Replace(replace(颜色,’红色’,0),’蓝色’,1))   
  from   test   
  group   by   产品   
  having   sum(IsNull(数量,0)   *     Replace(replace(颜色,’红色’,1),’蓝色’,-1))>0   
 
 
 
create   table   test(产品   varchar(10),颜色   varchar(10),数量   int)   
  insert   test   select   ’产品1’,’红色’,123   
  insert   test   select   ’产品1’,’蓝色’,126   
  insert   test   select   ’产品2’,’蓝色’,103   
  insert   test   select   ’产品2’,’红色’,NULL   
  insert   test   select   ’产品2’,’红色’,89   
  insert   test   select   ’产品1’,’红色’,203   
  --1   
  select   产品,sum(a)   
  from   
  (   
  select   产品,-数量   as   a   
  from   test   
  where   颜色=’蓝色’   
  union   all   
  select   产品,数量   as   a   
  from   test   
  where   颜色=’红色’   
  )   as   b   
  group   by   产品   
  having   sum(a)>0   
  --2   
  select   产品,红色=sum(case   when   颜色=’红色’   then   数量   else   0   end),   
  蓝色=sum(case   when   颜色=’蓝色’   then   数量   else   0   end)   from   test   group   by   产品   
  --表怪怪的