sql sever 例题两篇

最近在学习sql sever ,做题时发现了几个bug.

我用的是SQL Server Management Studio 19,出了不少bug,不太明白怎么回事

bug1:该求的小数都变成了整数,不是四舍五入,是整数后之间不显示了,不知道怎么回事

bug2:如果from后的表是我们上一步求出来的结果,其中包括了order by ,查询结果就一定报错

想出结果就必须把order by 删除才有结果
希望有大佬来解决小弟的问题

话不多说上题

第一篇

create table 属性 (编号 int,属性 varchar(5),相生 varchar(5),相克 varchar(5))
insert into 属性 values (1,'金','水','木'),
(2,'木','火','土'),(3,'水','木','火'),
(4,'火','土','金'),(5,'图','金','水');

create table 英雄(英雄号 varchar(10),英雄名 varchar(10),编号 int)
insert into 英雄 values ('H1','安琪拉',4),('H2','甄姬',3),
('H3','哪吒',4),('H4','金吒',1),('H5','木吒',2),
('H6','上官婉儿',2),('H7','黑旋风',5),('H8','敖丙',3),
('H9','周瑜',4);

create table 皮肤 (皮肤号 varchar(10),皮肤名 varchar(100),价格 int,英雄号 varchar(10))
insert into 皮肤 values('S01','童梦奇缘',88,'H1'),('S02','山有木兮木有枝',168,'H6'),
('S03','心悦君兮君不知',168,'H6'),('S04','小桥流水人家',88,'H9'),('S05','龙的传人',6,'H8'),
('S06','魔童降世',288,'H3'),('S07','匹诺曹',48.8,'H5'),('S08','巧克力恋人',6,'H7'),
('S09','冰雪奇缘',18.8,'H2'),('S10','布娃娃的夏天',28.8,'H1'),('S11','黑色星期五',28.8,'H7'),
('S12','123木头人',88,'H5'),('S13','打黄盖限定',6,'H9');

create table 玩家 (id int,姓名 varchar(100),性别 varchar(10),注册日期 date)
insert into 玩家 values(213602,'蕾丝彦祖大佬','男','2001-10-09'),(309857,'风','女','2013-02-16'),
(378247,'佛性玩家','女','2013-03-16'),(223342,'挂机的nmsl','男','2013-11-19'),(115325,'打野来中路','女','2013-12-05'),
(210944,'李白的小迷妹','女','2014-07-07'),(214145,'偷猪贼','女','2018-02-06'),(135978,'我是xxxx的狗','男','2015-06-07'),
(202354,'233333','女','2018-02-15'),(205563,'不服solo','男','2019-09-08');

create table 皮肤拥有 (id int,皮肤号 varchar(10),途径 varchar(10))
insert into 皮肤拥有 values (210944,'S02','购买'),
(210944,'S03','购买'),(210944,'S06','购买'),(210944,'S09','购买'),
(210944,'S11','赠送'),(210944,'S13','赠送'),(205563,'S09','购买'),
(205563,'S10','购买'),(205563,'S12','购买'),(205563,'S13','购买'),
(214145,'S02','购买'),(214145,'S03','购买'),(214145,'S09','购买'),
(213602,'S04','赠送'),(213602,'S05','赠送'),(135978,'S11','购买'),
(135978,'S01','购买'),(135978,'S02','购买'),(135978,'S03','购买'),
(135978,'S04','购买'),(135978,'S06','赠送'),(223342,'S01','购买'),
(223342,'S07','购买'),(223342,'S12','购买'),(223342,'S13','购买'),
(115325,'S02','赠送'),(115325,'S03','赠送'),(115325,'S06','赠送');

select * from 皮肤拥有

检索注册日期在2017年之前的女玩家的ID和姓名。
select id ,姓名 from 玩家 where 性别='女' and year(注册日期)<2017
检索男玩家所购买的皮肤的皮肤号和皮肤名。
select distinct 皮肤.皮肤号,皮肤名 from 玩家,皮肤,皮肤拥有 where 玩家.id
=皮肤拥有.id and 皮肤.皮肤号=皮肤拥有.皮肤号 and 性别='男' and 途径='购买'
检索至少拥有5款皮肤的玩家的ID和姓名。
select 玩家.id,姓名 from 玩家,皮肤拥有 where 玩家.id=皮肤拥有.id and 途径='购买' group by 玩家.id,姓名 having count(皮肤拥有.皮肤号)>=5
检索至少拥有两个赠送皮肤的玩家的ID。
select 玩家.id from 玩家,皮肤拥有 where 玩家.id=皮肤拥有.id and 途径='赠送' group by 玩家.id having count(皮肤拥有.皮肤号)>=2

至少偷猪贼和佛性玩家拥有的皮肤的皮肤名和价格。
select 皮肤名,价格 from 玩家,皮肤,皮肤拥有 where 玩家.id =皮肤拥有.id and 皮肤.皮肤号=皮肤拥有.皮肤号 and 途径='购买' and 姓名 in('偷猪贼','佛性玩家')
检索蕾丝彦祖大佬没有的皮肤的皮肤名。
select 皮肤名 from 皮肤 where 皮肤名 not in(select 皮肤名 from 玩家,皮肤,皮肤拥有 where 玩家.id =皮肤拥有.id and 皮肤.皮肤号=皮肤拥有.皮肤号 and 途径='购买' and 姓名 ='蕾丝彦祖大佬'
)
检索id为223342的玩家拥有的皮肤对应的英雄名及属性。
select 英雄名,属性 from 属性,英雄 where 属性.编号=英雄.编号 and 英雄号 in (select distinct 英雄号 from 玩家,皮肤,皮肤拥有 where 玩家.id =皮肤拥有.id and 皮肤.皮肤号=皮肤拥有.皮肤号 and 途径='购买' and 玩家.id=223342
)
检索拥有上官婉儿所有皮肤的玩家的ID和姓名。

select id,姓名 from 玩家 where id in(
select id from 皮肤拥有 where 途径='购买' and 皮肤号 in(select 皮肤号 from 英雄,皮肤 where 英雄.英雄号=皮肤.英雄号 and 英雄名='上官婉儿'
) group by id having count(id)=(select count(l.皮肤号) from (select 皮肤号 from 英雄,皮肤 where 英雄.英雄号=皮肤.英雄号 and 英雄名='上官婉儿'
) l)) 
统计水属性英雄的皮肤的数量。
select count(皮肤号) from 属性,英雄,皮肤 where 属性.编号=英雄.编号 and 英雄.英雄号=皮肤.英雄号 and 属性='水'  
求男玩家平均在一个皮肤上花多少钱，要求显示男玩家平均在一个皮肤上花。

每个男玩家平均花费多少钱买皮肤，要求显示玩家ID，姓名和平均花销
select 皮肤拥有.id,姓名,avg(价格) as 平均花销 from 皮肤拥有,玩家,皮肤
 where 皮肤拥有.id=玩家.id and 皮肤.皮肤号=皮肤拥有.皮肤号 and 性别='男' and 途径='购买'
 group by 皮肤拥有.id,姓名;
哪款皮肤持有最高，要求显示皮肤名和价格。

select 皮肤名,价格 from 皮肤 where 皮肤号 in(select k.h from (select 皮肤号 h,count(皮肤号) b from 皮肤拥有 where 途径='购买' group by 皮肤号
) k where k.b=(select max(l.b) from (select 皮肤号 a,count(皮肤号) b from 皮肤拥有 where 途径='购买' group by 皮肤号
) l))
检索游戏生涯比233333长，拥有皮肤数比其多的玩家的姓名及注册日期，按游戏生涯长短升序排列。

select 姓名,注册日期 from 玩家 where id in(select l.* from (select id from 玩家 where 注册日期<(select 注册日期 from 玩家 where 姓名='233333'
)) l where l.id in(select id from 皮肤拥有 where 途径='购买' group by id having count(皮肤号)>(select count(皮肤号) from 玩家,皮肤拥有 where 玩家.id=皮肤拥有.id and 姓名='233333'and 途径='购买'
))) order by 注册日期 desc
检索英雄名以吒结尾的所有英雄的名字和属性。
select 英雄名,属性 from 英雄,属性 where  属性.编号=英雄.编号 and 英雄名 like '%吒'
求皮肤价格大于平均皮肤价格的火属性皮肤的皮肤名和价格。
select 皮肤名,价格 from 皮肤,属性,英雄
where 皮肤.英雄号=英雄.英雄号 and 英雄.编号=属性.编号 and 属性='火' and 价格> (select avg(价格) from 皮肤);
求游戏生涯比所有女玩家都短的男玩家的姓名和注册日期。
select 姓名,注册日期 from 玩家 where 注册日期>(select max(注册日期) from 玩家 where 性别='女');

第二篇

create table student (id int,name varchar(10),age int,height int,sex varchar(5),cid int,dele int);
insert into student values(1,'小明',18,180.00,'女',1,null),
(2,'小月月',18,180.00,'女',2,0001),(3,'彭于晏',29,185.00,'男',1,null),(4,'刘德华',59,175.00,'男',2,0001),
(5,'黄蓉',38,160.00,'女',1,null),(6,'凤姐',28,150.00,'保密',2,0001),(7,'王祖贤',18,172.00,'女',1,0001),
(8,'周杰伦',36,null,'男',1,null),(9,'程坤',27,181.00,'男',2,null),(10,'刘亦菲',25,166.00,'女',2,null),
(11,'金星',33,162.00,'中性',3,0001),(12,'静香',12,180.00,'女',4,null),(13,'郭靖',12,170.00,'男',4,null),
(14,'周杰',34,176.00,'女',5,null);

create table class (id int,name varchar(20))
insert into class values(1,'python_01期'),(2,'python_02期'),(3,'python_04期');

select * from class

查询年龄大于18岁的信息
select * from student where age>18
查询年龄小于18岁的信息
select * from student where age<18
查询18到20岁之间的学生，全部字段信息
select * from student where age between 18 and 20
查询18岁以上的女性
select * from  student  where age>18 and sex='女'
查找18岁以上或者身高超过180（包含）以上的
select * from student where age>18 or height>=180
查找不是（18岁以上的女性）
select * from student where id not in(select id from  student  where age>18 and sex='女'
)
查年龄不是小于或等于18的，要找女的
select * from student where id not in (select id from student where age<=18
)  and sex='女'
查询姓名以’小’开始的名字
select name from student where name like '小%'
查询姓名中有’小’的所有名字
select name from student where name like '%小%'
查询名字是两个字组成的
select name from student where name like '__'
查询有三个字的名字
select name from student where name like '___'
查询至少有2个字名字的信息
select name from student where name like '__%'

查询以李开始的姓名
select name from student where name like '李%'

查询以‘郭’开头，以‘城’结尾
select name from student where name like '郭%城'

查询年龄为18，34岁的信息
select * from student where age in(18,34)
查询年龄不是12，18，34岁的信息
select * from  student where age not in (12,18,34)
查询年龄在18岁到34岁之间的信息
select * from student where age between 18 and 34

查询年龄不在18到34岁之间的信息
select * from student where age not between 18 and 34

查出身高记录为空的数据
select * from  student  where height is null
查询身高记录不为空的数据
select * from  student  where height is not null

查询年龄在18岁到34岁之间的男性，按照年龄从小到大的顺序排
select * from student where age between 18 and 34 and sex='男' order by age
查询年龄在18到34岁之间的女性，身高由高到矮排序
select * from student where age between 18 and 34 and sex='女' order by height desc

查询年龄在18到34岁之间的女性，身高由高到矮排序，如果身高也一样的话，按照id由大到小排
select * from student where age between 18 and 34 and sex='女' order by height desc
,id desc
查询年龄在18到34岁的女性，按照身高由大到小排，如果身高一样就按照年龄由小到大排，如果年龄也一样按照id从大到小排
select * from student where age between 18 and 34 and sex='女' order by age desc,height desc
,id desc
按照年龄从小到大，如果年龄一样大，就按照身高从高到矮排
select * from student order by age asc,height desc 
查询男性有多少人，显示出来，并且让表头名称为‘男性人数’ 
select sex 男性人数,count(sex) from student where sex='男' group by sex
查询班里的最大的年龄
select max(age) from student
查询女性的最高身高
select max(height) from student where sex='女'
计算所有人的年龄总和
select sum(age) from student 
计算班上平均年龄
select avg(age) from student
计算班上平均年龄，结果保留三位小数
select round(avg(age),3) from student

计算男性的平均身高，保留2位小数
select round(avg(height),2) from student where sex='男'
显示班里每一个性别的学生有多少个
select count(*) from student
按照性别分组，并显示每一组中的成员名称
select a.*,student.name from (select sex from  student group by sex
) a left join student on a.sex=student.sex
计算男性的人数
select sex,count(sex) from student where sex='男' group by sex
并显示组中男性的姓名年龄和id
select name,id from student where sex='男' 
查询平均年龄超过30岁的性别，以及姓名
select name,sex from student where age>30
查询学生表中前五个数据
select top 5 * from student 
查询学生信息按照年龄升序排序，在结果中从第11条数据开始取，取两个出来
select top 2 * from student where id not in(select top 10 id from student order by age asc
) order by age asc
学生表的cid与课程表的id一致的
 
select * from student where cid in(select id from class)
查询学生表与课程表能够对的上号的信息，字段要求显示学生表全部字段，课程表只显示name字段
select student.*,class.name from student left join class on student.cid=class.id 
上述查询中，把课程的名称放在第一列
select class.name,student.* from student left join class on student.cid=class.id 

查询学生表与课程表能够对的上号的信息，字段要求显示学生姓名和课程名称 
select student.name,class.name from student left join class on student.cid=class.id 

查询能对的上号的学生与课程信息，显示课程表的课程名称，学生表的全部信息。按照课程名称由小到大排序，课程名称一样的时候，就按照学生ID由小到大来排序
select student.*,class.name from student left join class on student.cid=class.id  order by class.name desc,student.id desc

根据上表的查询结果，把没有课程的学生信息找出来
select l.id,l.name,l.age,l.height,l.sex,l.cid,l.dele from (select student.*,class.name b from student left join class on student.cid=class.id ) l where l.b is null