#法一selectstu.student_id, stu.student_name, sub.subject_name,count(exam.subject_name)asattended_examsfromStudentsasstucrossjoinSubjectsassubleftjoinExaminationsasexamonstu.student_id=exam.student_idandsub.subject_name=exam.subject_namegroupbystudent_id, subject_name #注意group by后面不用带表名order...
39. Students and Examinations # Table: Students +---+---+ | Column Name | Type | +---+---+ | student_id | int | | student_name | varchar | +---+---+ student_id is the primary key for this table. Each row of this table contains the ID and the name of one student in...
# Write your MySQL query statement below with t1 as (select * from Students, Subjects) select t1.student_id, t1.student_name, t1.subject_name, count(t2.subject_name) attended_exams from t1 left join Examinations t2 on t1.student_id = t2.student_id and t1.subject_name = t2.subject_...
Create table If Not Exists Examinations (student_id int, subject_name varchar(20)) Truncate table Students insert into Students (student_id, student_name) values ('1', 'Alice') insert into Students (student_id, student_name) values ('2', 'Bob') insert into Students (student_id, student...
LeetCode题解(1280):学生们参加各科测试的次数(SQL) 标签:SQL 解法一: SELECT ST.student_id, ST.student_name, SU.subject_name, IFnull(E.attended_exams, 0) AS attended_exams FROM Students AS ST CROSS JOIN Subjects AS SU LEFT JOIN (SELECT student_id,...
https://leetcode.cn/studyplan/sql-free-50/ 回到顶部 查询 查询的结构 #方式1:SELECT...,...,...FROM...,...,...WHERE多表的连接条件AND不包含组函数的过滤条件GROUPBY...,... HAVING 包含组函数的过滤条件ORDERBY... ASC/DESC LIMIT ...,... #方式...
|1280|[Students and Examinations](https://leetcode.com/problems/students-and-examinations)|$\color{green}{\textsf{Easy}}$|[MySQL](https://github.com/xxxVitoxxx/leetcode/blob/main/1280.students_and_examinations/main.sql)| |1372|[Longest ZigZag Path in a Binary Tree](https://leetcode.co...
1415-students-and-examinations 1431-all-ancestors-of-a-node-in-a-directed-acyclic-graph 1433-encrypt-and-decrypt-strings 1435-xor-queries-of-a-subarray 1447-jump-game-iv 1452-restaurant-growth 1456-find-the-city-with-the-smallest-number-of-neighbors-at-a-threshold-distance 1460-number-of-subs...
要求写一段 SQL 语句,查询出每个学生参加每一门科目测试的次数,结果按 student_id 和 subject_name 排序。 查询结构格式如下所示: Students table: +---+---+|student_id|student_name|+---+---+|1|Alice||2|Bob||13|John||6|Alex|+---+---+ Subjects table: +---+|subject_name|+---+|...
1280. Students and Examinations 思路:Crossjoin 和 Leftjoin 把三张表同时放在一起,然后进行GROUP by 计数 select s.student_id,s.student_name,j.subject_name, count(e.subject_name) attended_exams from Students s cross join Subjects j left join Examinations e ...