根据sql中的优先级分配值

Question

我有一个名称表，它的值如下：

Designation       Priority
--------------------------
President         1
Founder           1
managing director 2
creative director 3
ceo               3
ict director      4
dpo               5
school director   6

以及员工表：

Name  Designation
-----------------
john  president / school director
ralph ict director / dpo
ron   Managing Director / Founder
rex   Ceo/Creative Director
rick  ceo/president
nick  Founder / Managing Director

我期望的输出是：

john   president 
ralph  ict director
ron    founder
rick   president
nick   founder

其中只有具有比与其相邻的等级更高的等级的指定应该在结果集中。

这种类型的问题在SQL中可能存在吗？还是我必须使用Python？我正在使用SQL Server 2019。Employee表中"指定"列的值具有由"/"分隔的指定。

Answer 1

您认为您的关系数据库是错误的。实际上你需要3张桌子。

employee
+-------------------+------------+
|    employee_id    |    name    |
+-------------------+------------+
|         1         |    John    |
|         2         |    Jane    |
+-------------------+------------+
designation
+----------------------+-------------------------+----------------+
|    designation_id    |    designation_name     |    priority    |
+----------------------+-------------------------+----------------+
|          1           |    president            |       1        |
|          2           |    school director      |       1        |
|          3           |    ict director         |       2        |
+----------------------+-------------------------+----------------+
designation_to_employee
+---------+---------------------+-------------------+
|   id    |   designation_id    |    employee_id    |
+---------+---------------------+-------------------+
|   1     |          1          |       1           |
|   2     |          2          |       1           |
|   3     |          3          |       2           |
+---------+---------------------+-------------------+

有了这三个表，就不需要在表内进行分隔。。每个员工可以有多个职位——然后可以根据所述职位进行排序。

IE

SELECT a.name, c.designation_name from employee a
LEFT JOIN designation_to_employee b
ON a.employee_id = b.employee_id
LEFT JOIN designation c
ON b.designation_id = c.designation_id
ORDER BY c.priority ASC

当然，如果你不想让约翰出现在两次结果中，因为他是校长和学校主任，你可以GROUP BY a.employee_id或类似的东西。。。这应该足以让你走上正轨。如果你想要约翰的所有，但在一行中，你将不得不使用NESTED SELECT和CONCAT，这远远超出了本OP的范围，如果你正在尝试的话，这需要成为SO的一个新问题。首先正确设置数据库，然后尝试。