最近在学习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