MYSQL 数据多选题答案对比问题

Question

现在有两个表：

表1：question

| id| ltem | right_answer |
| 1 | XX |  ["A","C","D"]  |
| 2 | BB | ["B","C" ] |


表2：report

| id  |qid | answer         |  status      |
| 1   | 1   | ["A","B","C"] | wrong       |
| 2   | 2   | ["B","C" ]    | right      |
| 3   | 1   | ["A","C"]     | past_right |

表2的 status 字段的值 是根据 表2的answer的值 对比表1 right_answer 的值，表2的qid对应表1的id

有3中情况，正确，错误，部分正确(right,wrong,past_right)

mysql 怎么写，根据表2的answer 得出 status？

Answer 1

在业务代码里面处理

Answer 2

你还是用代码写吧，代码很好写，一个list的包含就行，如果你sql，你得case when then 。
然后条件是字符串切分和字符串包含。
先不说多恶心，他也太=慢啊。
况且从原则上来讲，数据库不应该参与运算

Answer 3

SQL做不了的或者太复杂就用代码去实现

Answer 4

自定义函数

Answer 5

```sql
SELECT
    a.*,
    ( 
    CASE WHEN a.answer = a.right_answer THEN 'right' 
         WHEN  INSTR(REPLACE(a.right_answer,"]",""),REPLACE(a.answer,"]","")) > 0 THEN 'past_right' 
         ELSE 'wrong' END 
    ) STATUS 
FROM
    ( SELECT r.*, q.right_answer FROM report r LEFT JOIN question q ON r.qid = q.id ) a

```