一个公司给我的面试题目,帮忙赐教!!!(ZT)

Question

这里有例子：
http://expert.csdn.net/Expert/topic/2364/2364046.xml?temp=.829693
[交流]动态SQL语句

Answer 1

up
不会吧
CSDN上的高手们今天都放睛呀

Answer 2

select '课程' as code,'' as Name,'     语文' as score1,'     数学' as score2,'     外语' as score3
union all
select '编号' as code,'姓名' as Name,'期中      期末' as score1,'期中      期末' as score2,'期中      期末' as score3
union all
select cast (Code as varchar(20)),Name,left(score1,charindex(',',score1)-1)+'        '+left(score2,charindex(',',score2)-1) as score1,
left(right(score1,len(score1)-charindex(',',score1)),charindex(',',right(score1,len(score1)-charindex(',',score1)))-1)+'       '+left(right(score2,len(score2)-charindex(',',score2)),charindex(',',right(score2,len(score2)-charindex(',',score2)))-1) as score2,
right(left(score1,len(score1)-1),charindex(',',REVERSE(score1))+1)+'        '+right(left(score2,len(score2)-1),charindex(',',REVERSE(score2))+1) as score3
from test

Answer 3

你们老板真变态可以用多个表来实现啊一个是记录科目,一个是成绩表,一个是记录用户信息
用户信息中可以用一个用户ID与成绩表的ID连接,而成绩表中记录中应加上科目成绩的ID ,试试吧

Answer 4

drop table test
go
drop table #t
go
CREATE TABLE test(
        Code     smallint NOT NULL,        --学生编号
        Name     varchar(10) NOT NULL,     --学生姓名
        Lessons  varchar(1000) NOT NULL,   --课程名称，每个名称以“,”结束
        Score1   varchar(1000) NOT NULL,   --期中成绩，每课程成绩以“,”结束
        Score2   varchar(1000) NOT NULL,   --期末成绩，每课程成绩以“,”结束        PRIMARY KEY (Code)
)
goinsert into test (Code,Name,Lessons,Score1,Score2) values(20301,'张三','语文,数学
,外语,','100,99,98,','97,96,95,')
go
insert into test (Code,Name,Lessons,Score1,Score2) values(20302,'李四','语文,数学
,外语,','90,89,88,','87,86,85,')
go
insert into test (Code,Name,Lessons,Score1,Score2) values(20303,'王五','语文,数学
,外语,','70,69,68,','67,66,65,')
go
/*
屏幕显示实例：
       课程                    语文              数学             外语
       编号     姓名      期中    期末      期中    期末     期中    期末   
       20301    张三      100     97        99      96       98      95
       20302    李四      90      87        89      86       88      85
       20303    王五      70      67        69      66       68      65
*/
create table #t(
        Code     smallint NOT NULL,        --学生编号
        Name     varchar(10) NOT NULL,     --学生姓名
        Lessons  varchar(100) NOT NULL,   --课程名称，每个名称以“,”结束
        Score1   int,
        Score2   int
)
go
Select 1
while @@rowcount > 0
begin
insert #t 
select code,name
,left(lessons,charindex(',',lessons)-1) 
,left(score1,charindex(',',score1)-1)
,left(score2,charindex(',',score2)-1)
from test
where charindex(',',lessons)>0 update test set lessons = right(lessons,len(lessons)-charindex(',',lessons)), 
score1 = right(score1,len(score1)-charindex(',',score1)) ,
score2 = right(score2,len(score2)-charindex(',',score2)) 
where charindex(',',lessons)>0
end
declare @sql varchar(8000)
set @sql = 'select code,name'
select @sql = @sql + ',sum(case lessons when '''+lessons+''' then cast(score1 as int) end) ['+rtrim(lessons)+'中]'
+ ',sum(case lessons when '''+lessons+''' then cast(score2 as int) end) ['+rtrim(lessons)+'末]'
 from (select distinct lessons as lessons from #t) as a
select @sql = @sql+' from #t group by code,name'
exec(@sql)
code   name       数学
中       数学
末       外语中         外语末         语文中         语文末         
------ ---------- ----------- ----------- ----------- ----------- ----------- ----------- 
20302  李四         89          86          88          85          90          87
20303  王五         69          66          68          65          70          67
20301  张三         99          96          98          95          100         97

Answer 5

好！！！！
不愧是马可
你那个while循环真是让我大开眼界
呵呵我想大力之类的高手不是没有注意吧
也许是我给的分真是太少了:D

Answer 6

好
看来马可今天状态极佳
来再问个问题^_^排名次的问题(要求一句sql语句,想好再给答案哦)id    name    shuxue    yuwen    yingyu    总分    名次
---------------------------------------------------------------------------
1    jksfff      95       76        99      270
2    loskfi      87       64        87      238
3    skfjsk      79       95        75      249
4    sfjskj      69       82        92      243
...   .....       ...       ....       ....        ......
--------------------------------------------------------------------------
前几个字段都是表里的，总分和名次是在数据窗口对象中添加的计算列，显示时要按ID排序（这个我会），我可以计算出总分,可是名次我不会计算（并显示出来，[难点]），各位帮忙看看，谢了先

Answer 7

create table t1(id int identity(1,1),name char(10),总分 int)
insert t1 (name,总分) 
select 'jksfff',270
union all select 'loskfi',238
union all select 'skfjsk',249
union all select 'sfjskj',243Select *,(Select sum(1) from t1 where 总分>=a.总分) as 名次 
from t1 a
order by (Select sum(1) from t1 where 总分>=a.总分)id          name       总分          名次          
----------- ---------- ----------- ----------- 
1           jksfff     270         1
3           skfjsk     249         2
4           sfjskj     243         3
2           loskfi     238         4（所影响的行数为 4 行）

Answer 8

为简单起见，只用id,name,总分 三个字段

Answer 9

稍改一下：
drop table t1
go
create table t1(id int identity(1,1),name char(10),总分 int)
insert t1 (name,总分) 
select 'jksfff',270
union all select 'loskfi',238
union all select 'skfjsk',249
union all select 'sfjskj',243
union all select 'sfjskj',249
Select *,IsNull((Select sum(1) from t1 where 总分>a.总分),0)+1 as 名次 
from t1 a
order by (Select sum(1) from t1 where 总分>=a.总分)
id          name       总分          名次          
----------- ---------- ----------- ----------- 
1           jksfff     270         1
3           skfjsk     249         2
5           sfjskj     249         2
4           sfjskj     243         4
2           loskfi     238         5（所影响的行数为 5 行）

Answer 10

drop table t1
go
create table t1(id int identity(1,1),name char(10),总分 int)
insert t1 (name,总分) 
select 'jksfff',270
union all select 'loskfi',238
union all select 'skfjsk',249
union all select 'sfjskj',249
union all select 'sfjskj',270
Select *,IsNull((Select sum(1) from t1 where 总分>a.总分),0)+1 as 名次 
from t1 a
order by (Select sum(1) from t1 where 总分>=a.总分)
id          name       总分          名次          
----------- ---------- ----------- ----------- 
1           jksfff     270         1
5           sfjskj     270         1
3           skfjsk     249         3
4           sfjskj     249         3
2           loskfi     238         5（所影响的行数为 5 行）没问题啊，楼主举出一个反例来。

Answer 11

你上面的名次应该是
id          name       总分          名次          
----------- ---------- ----------- ----------- 
1           jksfff     270         1
5           sfjskj     270         1
3           skfjsk     249         2
4           sfjskj     249         2
2           loskfi     238         3这样才对呀
你说是不是:D

Answer 12

这种要求啊，简单：
drop table t1
go
create table t1(id int identity(1,1),name char(10),总分 int)
insert t1 (name,总分) 
select 'jksfff',270
union all select 'loskfi',238
union all select 'skfjsk',249
union all select 'sfjskj',249
union all select 'sfjskj',270
Select *,IsNull((Select count(distinct 总分) from t1 where 总分>a.总分),0)+1 as 名次 
from t1 a
order by (Select sum(1) from t1 where 总分>=a.总分)

Answer 13

drop table t1
go
create table t1(id int identity(1,1),name char(10),总分 int)
insert t1 (name,总分) 
select 'jksfff',270
union all select 'loskfi',238
union all select 'skfjsk',249
union all select 'sfjskj',249
union all select 'sfjskj',270
Select *,IsNull((Select count(distinct 总分) from t1 where 总分>a.总分),0)+1 as 名次 
from t1 a
order by (Select sum(1) from t1 where 总分>=a.总分)id          name       总分          名次          
----------- ---------- ----------- ----------- 
1           jksfff     270         1
5           sfjskj     270         1
3           skfjsk     249         2
4           sfjskj     249         2
2           loskfi     238         3（所影响的行数为 5 行）

Answer 14

下周见
 
该问题已经结贴 ，得分记录： txlicenhe (2000000)  

Answer 15

蒋老师的答案更绝：set nocount ondrop table test
go
CREATE TABLE test(
        Code     smallint NOT NULL,        --学生编号
        Name     varchar(10) NOT NULL,     --学生姓名
        Lessons  varchar(1000) NOT NULL,   --课程名称，每个名称以“,”结束
        Score1   varchar(1000) NOT NULL,   --期中成绩，每课程成绩以“,”结束
        Score2   varchar(1000) NOT NULL,   --期末成绩，每课程成绩以“,”结束        PRIMARY KEY (Code)
)
go
insert into test (Code,Name,Lessons,Score1,Score2) values(20301,'张三','语文,数学,外语,','100,99,98,','97,96,95,')
go
insert into test (Code,Name,Lessons,Score1,Score2) values(20302,'李四','语文,数学,外语,','90,89,88,','87,86,85,')
go
insert into test (Code,Name,Lessons,Score1,Score2) values(20303,'王五','语文,数学,外语,','70,69,68,','67,66,65,')
create table #t(
        Code     smallint NOT NULL,        --学生编号
        Name     varchar(10) NOT NULL,     --学生姓名
        Lessons  varchar(100) NOT NULL,   --课程名称，每个名称以“,”结束
        Score1   int,
        Score2   int,
flag1 int,
flag2 int,
flag3 int,
        lev int
)
godeclare @i int
select @i=1
insert #t 
select code,name
,substring(lessons,1,charindex(',',lessons)-1) 
,substring(score1,1,charindex(',',score1)-1)
,substring(score2,1,charindex(',',score2)-1)
        ,charindex(',',lessons)+1
,charindex(',',score1)+1
,charindex(',',score2)+1
        ,1
from testwhile @@rowcount<>0
beginset @i=@i+1  insert #t 
select A.code,A.name
,substring(A.lessons,flag1,charindex(',',A.lessons,flag1)-flag1) 
,substring(A.score1,flag2,charindex(',',A.score1,flag2)-flag2)
,substring(A.score2,flag3,charindex(',',A.score2,flag3)-flag3)
        ,charindex(',',A.lessons,flag1)+1
,charindex(',',A.score1,flag2)+1
,charindex(',',A.score2,flag3)+1
,@i
from test A,#t B where B.lev=@i-1 and A.code=B.code and flag1<>len(A.lessons)+1end--select * from #t
declare @sql varchar(8000)
set @sql = 'select code,name'
select @sql = @sql + ',sum(case lessons when '''+lessons+''' then score1 end) as '+lessons+'中'
+ ',sum(case lessons when '''+lessons+''' then score2 end) as '+lessons+'末'
 from (select distinct lessons as lessons from #t) as a
select @sql = @sql+' from #t group by code,name order by code'exec(@sql)drop table #t