hivesql-----query

以下是针对您提供的查询要求的Hive SQL查询语句：

1）查询所有学生的成绩信息：学生姓名、课程名、课程成绩。

```sql

SELECT s.sname AS student_name, c.cname AS course_name, sc.score
FROM score sc
JOIN students s ON sc.sid = s.sid
JOIN courses c ON sc.cid = c.cid;

```

2）查询编号为10的课程比编号为20的课程成绩高的学生的编号及课程分数。

```sql

SELECT a.sid, a.score AS score_10, b.score AS score_20
FROM score a
JOIN score b ON a.sid = b.sid
WHERE a.cid = 10 AND b.cid = 20 AND a.score > b.score;

```

3）查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩。

```sql

SELECT s.sid, s.sname, AVG(sc.score) AS avg_score
FROM score sc
JOIN students s ON sc.sid = s.sid
GROUP BY s.sid, s.sname
HAVING AVG(sc.score) >= 60;

```

4）查询并创建表格：从数据中获取学生的姓名和各科成绩，并将学生姓名，各科成绩的数组形式数据保存在temp表格中。

```sql

SELECT s.sname, COLLECT_LIST(sc.score) AS scores
FROM score sc
JOIN students s ON sc.sid = s.sid
GROUP BY s.sname
INTO TABLE temp;

```

5）查询编号为10的课程的平均分。

```sql

SELECT AVG(score) AS avg_score
FROM score
WHERE cid = 10;

```

6）查询每门课程的平均分（课程编号，课程名，平均分）。

```sql

SELECT c.cid, c.cname, AVG(sc.score) AS avg_score
FROM score sc
JOIN courses c ON sc.cid = c.cid
GROUP BY c.cid, c.cname;

```

7）按照课程对学生的成绩进行顺序排序(课程编号，学生编号，成绩，排名）。

```sql

SELECT c.cid, sc.sid, sc.score, RANK() OVER (PARTITION BY c.cid ORDER BY sc.score DESC) AS rank
FROM score sc
JOIN courses c ON sc.cid = c.cid;

```

8）查询每门课程第一名（所有并列第一名）（课程名称，学生姓名，成绩）。

```sql

SELECT c.cname AS course_name, s.sname AS student_name, sc.score
FROM score sc
JOIN students s ON sc.sid = s.sid
JOIN courses c ON sc.cid = c.cid
-- 使用DENSE_RANK()获取每门课程的第一名学生，包括所有并列第一名
WHERE DENSE_RANK() OVER (PARTITION BY sc.cid ORDER BY sc.score DESC) = 1;

```

9）统计每门课程不及格的学生人数（课程编号，不及格人数）。

```sql

SELECT cid, COUNT(*) AS fail_count
FROM score
WHERE score < 60
GROUP BY cid;

```

10）统计每门课程不及格的学生的姓名（课程编号，不及格学生姓名集合）。

```sql

SELECT cid, COLLECT_SET(s.sname) AS fail_students
FROM score sc
JOIN students s ON sc.sid = s.sid
WHERE sc.score < 60
GROUP BY cid;

```

11）查询两门及其以上不及格课程的学生的学号，姓名。

```sql

SELECT s.sname, s.sid
FROM students s
WHERE s.sid IN (
  SELECT sid
  FROM score
  GROUP BY sid
  HAVING COUNT(*) >= 2 AND SUM(score) / COUNT(*) < 60
);

```

12）查询学生的总成绩并进行排名（姓名 总分 名次）。

```sql

SELECT s.sname, SUM(sc.score) AS total_score, RANK() OVER (ORDER BY SUM(sc.score) DESC) AS rank
FROM score sc
JOIN students s ON sc.sid = s.sid
GROUP BY s.sname;

```

13）按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩。

```sql

SELECT s.sid, s.sname, COLLECT_LIST(sc.score) AS scores, AVG(sc.score) AS avg_score
FROM score sc
JOIN students s ON sc.sid = s.sid
GROUP BY s.sid, s.sname
ORDER BY avg_score DESC;

```

14）按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩。

这个查询与第13个查询相同，因此可以使用相同的SQL语句。

15）查询各科成绩最高分、最低分和平均分（课程编号，最高分，最低分，平均分）。

```sql

SELECT cid, MAX(score) AS max_score, MIN(score) AS min_score, AVG(score) AS avg_score
FROM score
GROUP BY cid;

```

请注意，上述SQL语句是基于您提供的查询要求和表结构假设编写的。在实际应用中，可能需要根据具体的Hive表结构和数据情况进行调整。同时，确保在执行这些查询之前，相关的表和数据已经正确创建和加载。