陶瓷网站模板,做网站经常用的字体有哪些,千锋python培训,广州建设工程交易中心主任1.删除除了学号字段以外#xff0c;其它字段都相同的冗余记录#xff0c;只保留一条#xff01;#xff08;也就是要删除王五和赵六中一条重复数据只留一条#xff09; 要求的预期效果: 原始数据创建表结构#xff1a;
CREATE TABLE tb_student (id int(16) NOT NULL,na…1.删除除了学号字段以外其它字段都相同的冗余记录只保留一条也就是要删除王五和赵六中一条重复数据只留一条 要求的预期效果: 原始数据创建表结构
CREATE TABLE tb_student (id int(16) NOT NULL,name varchar(10) DEFAULT NULL,sex varchar(10) DEFAULT NULL,age char(2) DEFAULT NULL,PRIMARY KEY (id)
) 插入数据
insert into tb_student (id, name, sex, age) values(2,李四,男,21);
insert into tb_student (id, name, sex, age) values(3,张三,女,17);
insert into tb_student (id, name, sex, age) values(4,李四,男,12);
insert into tb_student (id, name, sex, age) values(6,王五,女,20);
insert into tb_student (id, name, sex, age) values(5,王五,女,20);
insert into tb_student (id, name, sex, age) values(7,赵六,男,18);
insert into tb_student (id, name, sex, age) values(1,赵六,男,18);
insert into tb_student (id, name, sex, age) values(8,张三,男,17);
sql如下
DELETE FROM tb_student WHERE id NOT IN
( SELECT id FROM
(( SELECT min(id) id FROM tb_student GROUP BY name, sex,age ))
t)
2.查询各科成绩都及格的学员要求查询出参加考试的各科成绩都高于60分不管参加了多少科考试 要求结果 CREATE TABLE tb_score (id int(10) NOT NULL,username varchar(20) DEFAULT NULL,course varchar(20) DEFAULT NULL,score int(10) DEFAULT NULL,PRIMARY KEY (id)
)
insert into tb_score (id, username, course, score) values(1,张三,语文,50);
insert into tb_score (id, username, course, score) values(2,张三,数学,80);
insert into tb_score (id, username, course, score) values(3,张三,英语,90);
insert into tb_score (id, username, course, score) values(4,李四,语文,70);
insert into tb_score (id, username, course, score) values(5,李四,数学,80);
insert into tb_score (id, username, course, score) values(6,李四,英语,80);
insert into tb_score (id, username, course, score) values(7,王五,语文,50);
insert into tb_score (id, username, course, score) values(8,王五,英语,70);
insert into tb_score (id, username, course, score) values(9,赵六,数学,90);
sql如下
select username,score from tb_score where id
not in (select id from tb_score where score 60)
3.mysql完成行转列操作
原始数据如下 行转列后的数据 完成上面的后就可以看到只需要将结果以名称分组并按列sum求和或用max取最大值就可以获取到满足的结构了因为不对应学科的记录其分数必然是0完善结果如下 : 总结if主要是用来创建新列并将非对应学科的分数写为0用sum或max配合group by保证取出的值是学科对应的值这样就可以完成行转列了。
