sql 常见面试题

　　经常会看到这样的SQL面试题，请用一条SQL语句统计出学生的总总成绩，请用一条sql语句删除表中重复的内容，但第一条保留。最近得闲，就试着写了这么个demo，今天来这和大家分享下，如果大家有其他的sql题也可以拿出来大家一起讨论，一起分享。

     先创建一个表：

CREATE TABLE [ dbo ] . [ Score ] ( [ ID ] [ int ] IDENTITY ( 1 , 1 ) PRIMARY KEY NOT NULL , [ Name ] [ nvarchar ] ( 50 ) NULL , [ CID ] [ int ] NULL , [ Score ] [ int ] NULL ）

然后插入些测试数据：

INSERT INTO [ Test ] . [ dbo ] . [ Score ] ( [ Name ] , [ CID ] , [ Score ] ) VALUES ( ' 张三 ' , 1 , 60 ) INSERT INTO [ Test ] . [ dbo ] . [ Score ] ( [ Name ] , [ CID ] , [ Score ] ) VALUES ( ' 张三 ' , 2 , 70 ) INSERT INTO [ Test ] . [ dbo ] . [ Score ] ( [ Name ] , [ CID ] , [ Score ] ) VALUES ( ' 张三 ' , 3 , 80 ) INSERT INTO [ Test ] . [ dbo ] . [ Score ] ( [ Name ] , [ CID ] , [ Score ] ) VALUES ( ' 张三 ' , 4 , 90 ) INSERT INTO [ Test ] . [ dbo ] . [ Score ] ( [ Name ] , [ CID ] , [ Score ] ) VALUES ( ' 李四 ' , 1 , 60 ) INSERT INTO [ Test ] . [ dbo ] . [ Score ] ( [ Name ] , [ CID ] , [ Score ] ) VALUES ( ' 李四 ' , 2 , 70 ) INSERT INTO [ Test ] . [ dbo ] . [ Score ] ( [ Name ] , [ CID ] , [ Score ] ) VALUES ( ' 李四 ' , 3 , 80 ) INSERT INTO [ Test ] . [ dbo ] . [ Score ] ( [ Name ] , [ CID ] , [ Score ] ) VALUES ( ' 李四 ' , 4 , 90 ) INSERT INTO [ Test ] . [ dbo ] . [ Score ] ( [ Name ] , [ CID ] , [ Score ] ) VALUES ( ' 王五 ' , 1 , 60 ) INSERT INTO [ Test ] . [ dbo ] . [ Score ] ( [ Name ] , [ CID ] , [ Score ] ) VALUES ( ' 王五 ' , 2 , 70 ) INSERT INTO [ Test ] . [ dbo ] . [ Score ] ( [ Name ] , [ CID ] , [ Score ] ) VALUES ( ' 王五 ' , 3 , 80 ) INSERT INTO [ Test ] . [ dbo ] . [ Score ] ( [ Name ] , [ CID ] , [ Score ] ) VALUES ( ' 王五 ' , 4 , 90 )

好了，准备工作做完了，下面我们来写两条Sql语句，解决开始提出的那两个问题

A、统计学生的成绩

select name, SUM (Score)Score from Score group by Name

B、删除表中重复的记录，因为这表中的name是有重复的，所以我们就直接用这表来test

delete from Score where Name in   ( select Name from Score group by Name having COUNT (name) > 0 ) and ID    not in ( select MIN (id) from Score group by Name having COUNT (Name) > 0 ) 好了，两条语句解决了两个问题.

转载于:https://www.cnblogs.com/_fyz/archive/2011/04/29/2032439.html

相关资源：数据结构—成绩单生成器