SQL-找到所有他们所有朋友得分少于10的学生

发布于 2025-01-26 12:51:32 字数 966 浏览 2 评论 0原文

我必须使用 sqlserver 为数据库作业编写一些简单的查询。该问题中提供了该架构：

students(student_id, name);
friends(student_id, friend_id);
db_grades(student_id, grade);

对于每对朋友，（x，y）和（y，x）等2行将被添加到朋友表

找到所有的学生，他们所有的朋友的得分都低于 10

首先，我尝试使用组来解决此问题，然后写下了以下查询。

Select Distinct s.name, MAX(g.grade)
       From (students s inner join friends f on s.student_id = f.student_id), db_grades g
       Where f.friend_id = g.student_id
       Group by f.friend_id, s.name
       Having MAX(g.grade) < 10;

在第二次尝试中，我想使用关键字中的来实现它，但它也无法正常工作。

Select distinct s.name
    From students s  join friends f on s.student_id = f.student_id
    Where f.friend_id not in
    (
       Select f.friend_id
       From  db_grades g1
       Where g1.student_id = f.friend_id and g1.grade > 10
    )

我将感谢您在编写正确查询方面的帮助。

原文

I have to write some simple queries for my database homework using SQLServer. The schema has been provided in the question:

students(student_id, name);
friends(student_id, friend_id);
db_grades(student_id, grade);

For every pair of friends, 2 rows like (x, y) and (y, x) would be added to the friends table

Find all students, who all of their friends have a score of less than
10

First I tried to solve this question using GROUP BY, and I wrote the below query.

Select Distinct s.name, MAX(g.grade)
       From (students s inner join friends f on s.student_id = f.student_id), db_grades g
       Where f.friend_id = g.student_id
       Group by f.friend_id, s.name
       Having MAX(g.grade) < 10;

And in the second try, I wanted to implement it using the IN keyword, but it also does not work correctly.

Select distinct s.name
    From students s  join friends f on s.student_id = f.student_id
    Where f.friend_id not in
    (
       Select f.friend_id
       From  db_grades g1
       Where g1.student_id = f.friend_id and g1.grade > 10
    )

I will be grateful for your help on how to write the correct query.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

不知所踪 2025-02-02 12:51:32

聚合是一个选择：

SELECT s.student_id, s.name
FROM students s
INNER JOIN friends f
    ON f.student_id = s.student_id
INNER JOIN db_grades g
    ON g.student_id = f.friend_id
GROUP BY s.student_id, s.name
HAVING COUNT(CASE WHEN f.grade >= 10 THEN 1 END) = 0;

Aggregation is one option:

SELECT s.student_id, s.name
FROM students s
INNER JOIN friends f
    ON f.student_id = s.student_id
INNER JOIN db_grades g
    ON g.student_id = f.friend_id
GROUP BY s.student_id, s.name
HAVING COUNT(CASE WHEN f.grade >= 10 THEN 1 END) = 0;

回复收藏 0 原文

离笑几人歌 2025-02-02 12:51:32

另一个选择是使用不存在。请注意， 内在加入之间的区别在于，此选项包括那些根本没有朋友的选项。

SELECT s.student_id, s.name
FROM students s
WHERE NOT EXISTS (SELECT 1
    FROM friends f
    INNER JOIN db_grades g
        ON g.student_id = f.friend_id
    WHERE f.student_id = s.student_id
      AND g.grade >= 10
);

Another option is to use NOT EXISTS. Note that the difference between this and HAVING with an INNER JOIN is that this option includes those that have no friends at all.

SELECT s.student_id, s.name
FROM students s
WHERE NOT EXISTS (SELECT 1
    FROM friends f
    INNER JOIN db_grades g
        ON g.student_id = f.friend_id
    WHERE f.student_id = s.student_id
      AND g.grade >= 10
);

回复收藏 0 原文

~没有更多了~