sql脚本的不同的查询-白红宇

sql脚本的不同的查询

阅读量：4212 次

发布时间：2019-05-26

本文共 5185 字，大约阅读时间需要 17 分钟。

select * from Song

--insert into Singer

--(RealName, SingerName, Intro, Sex, SchoolName,OwnerUserId)

--(select Lyricist,Lyricist,Lyricist,'男','',0 from SingerMediaAuthor)

--go

--update Singer set RealName = '汪苏泷',SingerName = '汪苏泷'

--go

--insert into dbo.SingerMedia

--(UploadedOn,MediaName,EnglishName,IsAudit, IsShow, TotalPlayCount,DuringSecond,TypeId,FileSize,Singer_Id)

--(select GETDATE(),'我也不知道','I do not know',0,1,0,0,0,0,Id from dbo.Singer)

--go

--insert into DaSai..SingerMediaAuthor

--(

--Lyricist, Compose, Arranged, Description, CreatedOn, CreatedBy, Seq, IsDelete, IsShow

--)

--(

--select Lyricist, Compose, Arranged, Description, CreatedOn, CreatedBy, Seq, IsDelete, IsShow from abc..Song

--)

--go

--update SingerMediaAuthor as sma set sma.SingerMedia_Id = sm.Id from SingerMedia as sm

--where sma.Id = sm.Singer_Id

select sma.Id, sm.Singer_Id from SingerMedia as sm,SingerMediaAuthor as sma

where sma.Id = sm.Singer_Id

select * from SingerMediaAuthor

select * from dbo.SingerMedia

select * from dbo.Song

--create table temp

--(

Id int identity(1,1) not null primary key ,

Bh nvarchar(20),

Xm nvarchar(20),

Rq datetime,

Je float

--)

--insert into temp values('1','张三','

2009-03-06 17:21:31.607',100.00)

--insert into temp values('2','李四','

2009-03-07 17:21:31.607',200.00)

--insert into temp values('3','王五','

2009-03-08 17:21:31.607',300.00)

--insert into temp values('4','赵六','

2009-03-09 17:21:31.607',400.00)

--insert into temp values('5','李四','

2009-03-10 17:21:31.607',500.00)

--insert into temp values('6','赵六','

2009-03-11 17:21:31.607',600.00)

--insert into temp values('7','赵六','

2009-03-12 17:21:31.607',700.00)

--insert into temp values('8','王五','

2009-03-13 17:21:31.607',800.00)

--insert into temp values('9','李四','

2009-03-14 17:21:31.607',900.00)

--go

--请取出每个姓名最后一条记录，一个姓名一条记录，日期最大那条记录。

select Bh 编号, Xm 姓名, Rq 日期, Je 金额 from temp

where Bh in

(

select Bh from temp

where Rq in

(select MAX(Rq) from temp group by Xm)

)

--删除表中重复的记录（记录完全相同才算重复）

delete from temp

where Id in

(

select max(Id) as Id

from temp

group by Bh, Xm, Rq, Je

having COUNT(Id) > 1

)

--学员表（学号，姓名，单位，年龄）

--create table Stu

--(

Id int identity(1,1) primary key not null,

StuNo nvarchar(50),

StuName nvarchar(50),

StuUnit nvarchar(50),

StuAge int

--)

--课程表（课程编号，课程名称）

--create table Course

--(

Id int identity(1,1) primary key not null,

CourseNo nvarchar(50),

CourseName nvarchar(50)

--)

--成绩表（学号，课程编号，成绩）

--create table Score

--(

Id int identity(1,1) primary key not null,

StuNo nvarchar(50),

CourseNo nvarchar(50),

Score float

--)

--insert into Course values('C1','数学')

--insert into Course values('C2','语文')

--insert into Course values('C3','英语')

--insert into Course values('C4','政治')

--insert into Course values('C5','物理')

--insert into Course values('C6','化学')

--insert into Course values('C7','生物')

--insert into Stu values('S1','Tom','IBM',25)

--insert into Stu values('S2','Jim','IBM',26)

--insert into Stu values('S3','Tim','NBA',27)

--insert into Stu values('S4','Jone','NBA',28)

----insert into Stu values('S5','Yuli','IBM',27)

--insert into Stu values('S6','Yiyi','IBM',27)

--insert into Stu values('S7','Jerry','IBM',28)

--insert into Stu values('S8','Marry','IBM',25)

--insert into Score values('S1','C1',56.23)

--insert into Score values('S1','C2',86.23)

--insert into Score values('S1','C3',69.24)

--insert into Score values('S1','C4',72.23)

--insert into Score values('S1','C5',86.23)

--insert into Score values('S1','C6',96.23)

--insert into Score values('S1','C7',52.23)

--insert into Score values('S2','C1',99.23)

--insert into Score values('S2','C2',100.00)

--insert into Score values('S2','C3',98.56)

--insert into Score values('S3','C1',85.23)

--insert into Score values('S3','C2',76.23)

--insert into Score values('S3','C3',62.23)

--insert into Score values('S3','C4',79.23)

--insert into Score values('S3','C5',86.23)

--insert into Score values('S3','C6',83.23)

--insert into Score values('S3','C7',95.23)

--insert into Score values('S4','C1',45.23)

--insert into Score values('S4','C2',53.23)

--insert into Score values('S4','C3',51.23)

--insert into Score values('S4','C4',52.23)

--insert into Score values('S5','C1',100.00)

--insert into Score values('S5','C2',100.00)

--insert into Score values('S5','C3',100.00)

--1、查询未选修课程编号为‘C5’学员姓名和所属单位

select StuName,StuUnit

from Stu

where StuNo not in

(

select StuNo

from Score

where CourseNo = 'C5'

)

--2、查询选修满全部课程的学员人数

select COUNT(*)

from Stu

where StuNo in

(

select StuNo

from Score

group by StuNo

having Count(CourseNo) =

(select COUNT(*) from Course)

)

--3、查询选修了课程的学员人数

select COUNT(*)

from Stu

where StuNo in

(

select StuNo from Score

)

--4、查询课程编号A1，A2，A3，三门课程总分最高分及学员姓名

--查询课程编号C1，C2，C3，三门课程总分最高分及学员姓名

select top 1 sc.StuNo,s.StuName,sc.Score

from

(

select SUM(Score) as Score,StuNo

from Score

where CourseNo in

('C1','C2','C3')

group by StuNo

) as sc,Stu as s

where sc.StuNo = s.StuNo

order by sc.Score desc

--5、查询选修课程超过5门的学员学号和所属单位

select * from stu

select top 1 * from course

select * from score

select StuNo,StuUnit

from Stu

where StuNo

--6、查询存在满分（100分）课程的学员平均年龄

--7、查询各单位学员理科成绩平均分（理科课程：课程编号以A开头）

转载地址：http://wvzmi.baihongyu.com/

你可能感兴趣的文章

/dev/sdxx is apparently in use by the system; will not make a filesystem here! 解决方法

查看>>

RMAN-01009: syntax error: found "dot" 解决方法

查看>>

Oracle smon_scn_time 表说明

查看>>

VBox fdisk 不显示添加的硬盘解决方法

查看>>

Secure CRT 自动记录日志配置小记

查看>>

RMAN RAC 到单实例 duplicate 自动分配通道触发 ORA-19505 错误

查看>>

mysql 随机分页的优化

查看>>

SQL SERVER中判断某个字段是否包含大写字母

mysql中的 skip-name-resolve 问题

MySQl备份恢复策略（完全+增量备份策略）

查看>>

VBS发送邮件

查看>>