我有两个表(membership和persons)。我想要得到每个人的名字和他们的最高职位

Question

人员表

id name
1  boyax
2  lexan
3  joe
4  buten
5  milo

成员资格表

id  position
1   president
1   chief-clerk
1   asst-secretary
1   book-keeper
2   asst-secretary
2   book-keeper
3   book-keeper
3   chief-clerk
3   asst-secretary
4   book-keeper
4   asst-secretary
4   president
4   chief-clerk
5   chief-clerk
5   asst-secretary

成员资格排序方式：1. president, 2. chief-clerk, 3. asst-secretary and 4. booker-keeper

我尝试过这种方法：

select id, name , position from persons
left join membership on membership.id = persons.id group by persons.id

结果应该是：

id name    position
1  boyax   president
2  lexan   asst-secretary
3  joe     chief-clerk
4  buten   president
5  milo    chief-clerk

任何想法都是值得高度赞赏的。

Answer 1

你可以尝试下面的查询-

SELECT id, name, position
FROM(SELECT id, name, position, MAX(POS)
     FROM(select id, name, position, CASE WHEN position = 'president' THEN 1
                                          WHEN position = 'chief-clerk' THEN 2
                                          WHEN position = 'asst-secretary' THEN 3
                                          WHEN position = 'booker-keeper' THEN 4 END POS
          from persons
          left join membership on membership.id = persons.id
         ) T
          group by id, name, position
    ) TEMP