在MySQL中映射选定的值 [英] Mapping the selected value in MySQL

问题描述

我从两个表中有2组数据，我需要加入表

I have 2 sets of data from two table , i need to join the table

table_ 1

id     qid     choice               
--------------------------
11     2020     Item 1
12     2020     Item 2
13     2020     Item 14

table_2

rid    qid     question
---------------------------
1001   2020    1.I love apple
1002   2020    2.I love orange
1003   2020    14.I hate lemon

输出

id    rid     qid     choice       question
-------------------------------------------------
11    1001    2020    Item 1      1.I love apple
12    1002    2020    Item 2      2.I love orange
13    1003    2020    Item 14     14.I hate lemon

表_2中问题"列的格式必须以

The formate of the column "question" in table_2 must start with

1.XXXX , 2.XXXX , 14.XXXX until 20.XXXXXX

我认为在."之前获取数字.作为加入条件.但是如何获得数量或其他更好的解决方案呢?

I think get the number before "." as the joining condition. But how to get the number or any other better solutions?

推荐答案

您应更改当前表的数据结构.但是对于您当前的数据结构，您可以使用此棘手的查询(假设table_1.choice的模式始终为Item XX):

You should change the data structure of your current tables. But for your current data structure you may use this tricky query (assuming the pattern of table_1.choice is always Item XX):

SELECT t1.id, t2.rid, t1.qid, t1.choice, t2.question
  FROM Table1 t1
  JOIN Table2 t2
    ON RIGHT(t1.choice, LENGTH(t1.choice) - 5) 
     = LEFT(t2.question, LOCATE('.',t2.question) - 1);

输出:

| ID |  RID |  QID |  CHOICE |        QUESTION |
|----|------|------|---------|-----------------|
| 11 | 1001 | 2020 |  Item 1 |  1.I love apple |
| 12 | 1002 | 2020 |  Item 2 | 2.I love orange |
| 13 | 1003 | 2020 | Item 14 | 14.I hate lemon |

这篇关于在MySQL中映射选定的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！