1.⽤⼀条SQL 语句 查询出每⻔课都⼤于80 分的学⽣姓名
建表语句:
create table tableA
(
name varchar(10),
kecheng varchar(10),
fenshu int(11)
)DEFAULT CHARSET = 'utf8';
插⼊数据
insert into tableA values ('张三','语⽂',81);
insert into tableA values ('张三','数学',75);
insert into tableA values ('李四','语⽂',76);
insert into tableA values ('李四','数学',90);
insert into tableA values ('王五','语⽂',81);
insert into tableA values ('王五','数学',100);
insert into tableA values ('王五','英语',90);
答案有3种:
答案A:
select distinct name from tableA where name not in (select distinct name fr
om tableA where fenshu<=80)
答案B:
select name from tableA group by name having min(fenshu)>80
答案C:
select name from tableA group by name having count(kecheng)>=3 and min(fens
hu)>=80
2.⼀道SQL语句⾯试题,关于group by表内容:
2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负
如果要⽣成下列结果, 该如何写sql语句?
建表语句:
create table tableb (
rq varchar(10),
shengfu nchar(1)
)DEFAULT CHARSET = 'utf8';
插⼊数据:
insert into tableb values('2005-05-09','胜');
insert into tableb values('2005-05-09','胜');
insert into tableb values('2005-05-09','负');
insert into tableb values('2005-05-09','负');
insert into tableb values('2005-05-10','胜');
insert into tableb values('2005-05-10','负');
insert into tableb values('2005-05-10','负')
答案:
select rq, sum(case when shengfu='胜' then 1 else 0 end)'胜',sum(case when
shengfu='负' then 1 else 0 end)'负' from tableb group by rq
3.⼀个叫 team 的表,⾥⾯只有⼀个字段name, ⼀共有4 条纪录,分别是a,b,c,d, 对应四个球对,现在四 个球对进⾏⽐赛,⽤⼀条sql 语句显示所有可能的⽐赛组合
team表建表语句:
CREATE TABLE team (
`name` varchar(20)
) DEFAULT CHARSET=utf8;
插⼊数据:
INSERT INTO team VALUES ('a');
INSERT INTO team VALUES ('b');
INSERT INTO team VALUES (