SQL - 查找只喜欢同一年级学生的学生的成绩 [英] SQL - Find the grade of students who only like students in the same grade
问题描述
问题4
查找只有朋友在同一年级的学生的姓名和成绩。
当我终于想到我有答案时，我的查询返回了所有的值表朋友。
这是我所能想到的最好的。
select h1.id，h1.name，h1.grade，h2.id，h2.name，h2.grade
from friend f1
在f1.id1上加入highschooler h1 = h1.id
加入highschooler h2 on f1.id2 = h2.id
where h1.grade = any（从朋友中选择h3.grade f2
在f2.id1上加入highschooler h3 = h3.id
其中h3。 id = f1.id1）
我需要在SQL Lite中运行查询。
我使用 http://sqlfiddle.com 在SQL Lite中测试我的查询，这里是示例我使用的数据。
/ *为我们的表创建模式* /
create table Highschooler（ID int ，名称文本，等级int）;
创建表Friend（ID1 int，ID2 int）;
创建表赞（ID1 int，ID2 int）;
$ b $ *用我们的数据填充表格* /
插入高分级数值（1510，'Jordan'，9）;
插入到高级数值（1689，'Gabriel'，9）;
插入高级数值（1381，'Tiffany'，9）;
插入到高级数值中（1709，'Cassandra'，9）;
插入高级数值（1101，'Haley'，10）;
插入高级数值（1782，'Andrew'，10）;
插入高级数值（1468，'Kris'，10）;
插入高级数学值（1641，'Brittany'，10）;
插入高级数值（1247，'Alexis'，11）;
插入高级数值（1316，'Austin'，11）;
插入高级数值（1911，'Gabriel'，11）;
插入高级数值（1501，'Jessica'，11）;
插入高级数值（1304，Jordan，12）;
插入高级数值（1025，'John'，12）;
插入到高中的价值观（1934年，凯尔，12）;
插入高级数值（1661，'Logan'，12）;
插入Friend值（1510，1381）;
插入好友值（1510,1689）;
插入Friend值（1689,1709）;
插入Friend值（1381，1247）;
插入朋友值（1709，1247）;
插入Friend值（1689，1782）;
插入Friend值（1782,1468）;
插入Friend值（1782,1316）;
插入Friend值（1782，1304）;
插入好友值（1468，1101）;
插入Friend值（1468，1641）;
插入Friend值（1101,1641）;
插入Friend值（1247，1911）;
插入Friend值（1247，1501）;
插入Friend值（1911，1501）;
插入Friend值（1501，1934）;
插入Friend值（1316，1934）;
插入Friend值（1934，1304）;
插入Friend值（1304,1661）;
插入Friend值（1661,1025）;
插入Friend选择ID2，朋友的ID1;
插入喜欢值（1689，1709）;
插入喜欢值（1709，1689）;
插入喜欢值（1782，1709）;
插入喜欢值（1911，1247）;
插入喜欢值（1247,1468）;
插入喜欢值（1641，1468）;
插入喜欢值（1316，1304）;
插入喜欢值（1501，1934）;
插入喜欢值（1934，1501）;
插入喜欢值（1025，1101）;
提前致谢。
。
Cesar
没有其他年级的学生有友谊关系，对吗？这是一种表达方式：
select * from highschooler h
不存在
（select 1 from highschooler h2 where h2.grade！= h.grade and exists
（select 1 from friends f where f.id1 = h.id or f.id2 = h.id）and（f.id1 = h2 .id或f.id2 = h2.id）））
按等级排列，名称
编辑：如果你还要求他们至少有一个朋友，你也需要检查这一点
I am doind a free Stanford online course (which is pretty cool, you should check that out) and I've been racking my brains for the lest 2 days and can't find an answer to the following problem. Please help.
Question 4 Find names and grades of students who only have friends in the same grade. Return the result sorted by grade, then by name within each grade.
When I finally thought that I had the answer my query returned all the values from the table Friend.
This is the best I could come up with.
select h1.id, h1.name, h1.grade, h2.id, h2.name, h2.grade
from friend f1
join highschooler h1 on f1.id1 = h1.id
join highschooler h2 on f1.id2 = h2.id
where h1.grade = any (select h3.grade from friend f2
join highschooler h3 on f2.id1 = h3.id
where h3.id = f1.id1)
I'm required to run the query in SQL Lite. I'm using http://sqlfiddle.com to test my queries in SQL Lite and here is the sample data I'm using.
/* Create the schema for our tables */
create table Highschooler(ID int, name text, grade int);
create table Friend(ID1 int, ID2 int);
create table Likes(ID1 int, ID2 int);
/* Populate the tables with our data */
insert into Highschooler values (1510, 'Jordan', 9);
insert into Highschooler values (1689, 'Gabriel', 9);
insert into Highschooler values (1381, 'Tiffany', 9);
insert into Highschooler values (1709, 'Cassandra', 9);
insert into Highschooler values (1101, 'Haley', 10);
insert into Highschooler values (1782, 'Andrew', 10);
insert into Highschooler values (1468, 'Kris', 10);
insert into Highschooler values (1641, 'Brittany', 10);
insert into Highschooler values (1247, 'Alexis', 11);
insert into Highschooler values (1316, 'Austin', 11);
insert into Highschooler values (1911, 'Gabriel', 11);
insert into Highschooler values (1501, 'Jessica', 11);
insert into Highschooler values (1304, 'Jordan', 12);
insert into Highschooler values (1025, 'John', 12);
insert into Highschooler values (1934, 'Kyle', 12);
insert into Highschooler values (1661, 'Logan', 12);
insert into Friend values (1510, 1381);
insert into Friend values (1510, 1689);
insert into Friend values (1689, 1709);
insert into Friend values (1381, 1247);
insert into Friend values (1709, 1247);
insert into Friend values (1689, 1782);
insert into Friend values (1782, 1468);
insert into Friend values (1782, 1316);
insert into Friend values (1782, 1304);
insert into Friend values (1468, 1101);
insert into Friend values (1468, 1641);
insert into Friend values (1101, 1641);
insert into Friend values (1247, 1911);
insert into Friend values (1247, 1501);
insert into Friend values (1911, 1501);
insert into Friend values (1501, 1934);
insert into Friend values (1316, 1934);
insert into Friend values (1934, 1304);
insert into Friend values (1304, 1661);
insert into Friend values (1661, 1025);
insert into Friend select ID2, ID1 from Friend;
insert into Likes values(1689, 1709);
insert into Likes values(1709, 1689);
insert into Likes values(1782, 1709);
insert into Likes values(1911, 1247);
insert into Likes values(1247, 1468);
insert into Likes values(1641, 1468);
insert into Likes values(1316, 1304);
insert into Likes values(1501, 1934);
insert into Likes values(1934, 1501);
insert into Likes values(1025, 1101);
Thank you in advance.
Regards.
Cesar
So we want to find students for whom there are no students in other grades they have a friendship relationship, right? This is one way to express that:
select * from highschooler h
where not exists
(select 1 from highschooler h2 where h2.grade != h.grade and exists
(select 1 from friends f where (f.id1 = h.id or f.id2 = h.id) and (f.id1 = h2.id or f.id2 = h2.id)))
order by grade, name
EDIT: If you also require them to have at least one friend, you'll need to check for that too
这篇关于SQL - 查找只喜欢同一年级学生的学生的成绩的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
