mysql - 通过给定的选择将客户与供应商联系起来

Question

我有一个电子商务应用程序，逻辑简化如下:

7个学院各1个名额，有4名学生有意向录取。

这里是如何分配座位...

Akbar 将被 324 大学录取，因为这是他的第一选择，而且没有竞争。现在他退出了比赛，因此即使 amar 排在第二位，他也会进入 162 大学的候选名单。 Anthony也在456大学获得了他的第一选择，而名为“shantanu”的学生没有被任何大学录取。

是否有将返回预期结果的 SQL 查询？

---

drop table college;

create table college (id int not null, primary key (id));
insert into college values (162), (324), (456), (862), (169), (589), (489);

drop table students;
create table students (name varchar(255), marks int, first_pref int, second_pref int,third_pref int);
insert into students values ('shantanu', 67, 162, 324,456);
insert into students values ('amar', 98, 162, 862, 169);
insert into students values ('akbar', 99, 324, 162, 589);
insert into students values ('anthony', 76, 456, 489, 589);

select * from students;
+----------+-------+------------+-------------+------------+
| name     | marks | first_pref | second_pref | third_pref |
+----------+-------+------------+-------------+------------+
| shantanu |    67 |        162 |         324 |        456 |
| amar     |    98 |        162 |         862 |        169 |
| akbar    |    99 |        324 |         162 |        589 |
| anthony  |    76 |        456 |         489 |        589 |
+----------+-------+------------+-------------+------------+
4 rows in set (0.00 sec)

select name, marks, ( case 
when first_pref=162 then 1 
when second_pref = 162 then 2 
when third_pref = 162 then 3 else 0 
end ) as mypref from students having mypref > 0
order by   marks desc;
+----------+-------+--------+
| name     | marks | mypref |
+----------+-------+--------+
| akbar    |    99 |      2 |
| amar     |    98 |      1 |
| shantanu |    67 |      1 |
+----------+-------+--------+
3 rows in set (0.00 sec)

select name, marks, ( case
when first_pref=324 then 1
when second_pref = 324 then 2
when third_pref = 324 then 3 else 0
end ) as mypref from students having mypref > 0
order by  marks desc;
+----------+-------+--------+
| name     | marks | mypref |
+----------+-------+--------+
| akbar    |    99 |      1 |
| shantanu |    67 |      2 |
+----------+-------+--------+
2 rows in set (0.00 sec)

select name, marks, ( case
when first_pref=456 then 1
when second_pref = 456 then 2
when third_pref = 456 then 3 else 0
end ) as mypref from students having mypref > 0
order by  marks desc;
+----------+-------+--------+
| name     | marks | mypref |
+----------+-------+--------+
| anthony  |    76 |      1 |
| shantanu |    67 |      3 |
+----------+-------+--------+
2 rows in set (0.00 sec)

预期的输出是这样的......

Student_name college_id
akbar 324
amar 162
anthony 456
shantanu NULL

Answer 1

select name,id from students,college, (case

when first_pref,second_pref,third_pref is not null then mypref
(case
first_pref,second_pref,third_pref == null
end)
else null
end)

as mypref;