group by里出現(xiàn)某個(gè)表的字段拓劝，select里面的列要么是該group by里出現(xiàn)的列考赛，要么是帶有聚合函數(shù)的列或者是別的表的列一罩。
查詢所有同學(xué)的學(xué)號(hào)月趟、選課數(shù)灯蝴、總成績(jī)
select student_id,count(course_id),sum(score) from score group by student_id;
查詢所有同學(xué)的學(xué)號(hào)、姓名孝宗、選課數(shù)穷躁、總成績(jī)
select student_id,count(course_id),sum(score) from score s,student stu where s.student_id=stu.student_id group by s.student_id;
having
查詢平均成績(jī)大于60分的同學(xué)的學(xué)號(hào)和平均成績(jī)
select student_id,avg(score) from score group by student_id having avg(score) > 60
查詢沒(méi)有學(xué)全所有課的同學(xué)的學(xué)號(hào)、姓名
select stu.student_id,stu.student_name from score s,student stu group by stu.student_id having count(s.course_id) < (select count(1) from course);
行轉(zhuǎn)列
INSERT INTO student_scores(user_name,subject,score) VALUES ('張三', '語(yǔ)文', 80);
INSERT INTO student_scores(user_name,subject,score) VALUES ('張三', '數(shù)學(xué)', 90);
INSERT INTO student_scores(user_name,subject,score) VALUES ('張三', '英語(yǔ)', 70);
INSERT INTO student_scores(user_name,subject,score) VALUES ('張三', '生物', 85);
INSERT INTO student_scores(user_name,subject,score) VALUES ('李四', '語(yǔ)文', 80);
INSERT INTO student_scores(user_name,subject,score) VALUES ('李四', '數(shù)學(xué)', 92);
INSERT INTO student_scores(user_name,subject,score) VALUES ('李四', '英語(yǔ)', 76);
INSERT INTO student_scores(user_name,subject,score) VALUES ('李四', '生物', 88);
INSERT INTO student_scores(user_name,subject,score) VALUES ('碼農(nóng)', '語(yǔ)文', 60);
INSERT INTO student_scores(user_name,subject,score) VALUES ('碼農(nóng)', '數(shù)學(xué)', 82);
INSERT INTO student_scores(user_name,subject,score) VALUES ('碼農(nóng)', '英語(yǔ)', 96);
INSERT INTO student_scores(user_name,subject,score) VALUES ('碼農(nóng)', '生物', 78);
CREATE TABLE student_scores (
id int(11) NOT NULL AUTO_INCREMENT,
user_name varchar(255) DEFAULT NULL,
subject varchar(255) DEFAULT NULL,
score decimal(10,2) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
SELECT * from student_scores;
SELECT user_name,
MAX(IF (subject='語(yǔ)文',score,NULL)) '語(yǔ)文',
MAX(IF (subject='數(shù)學(xué)',score,NULL)) '數(shù)學(xué)',
MAX(IF (subject='英語(yǔ)',score,NULL))'英語(yǔ)',
MAX(IF (subject='生物',score,NULL))'生物'
FROM student_scores
GROUP BY user_name;
