如果我按“ 08”查询,将从表1中的id_code1位置3搜索
然后将获得id_code1包含'08'的数据,然后从table2中选择数据,并获得id_code2从table3中选择数据

我有3张桌子
表1.包含作为字符串的id_code1和id_code2

表格1

id_code1  |   id_code2
----------+--------------
1008SD7X  |    M71X456700
1008DG5B  |    N71X456800
100894EX  |    P71X456900
1064TESE  |    Q71X456135
19745EGS  |    R71X456789
10DFEE77  |    S71X456239
1EERSD7X  |    S86X436477




table2包含id_code1,data1,data2,data3

id_code1  |   data1  |  data2  |  data3  |
----------+----------+---------+---------+
1008SD7X  |   12.9   |  10     |   7.5   |
1008DG5B  |   13.8   |  10     |   4.2   |
100894EX  |   15.4   |  11     |   3.0   |
1064TESE  |   12.7   |  12     |   2.5   |
19745EGS  |   14.2   |  12     |   5.5   |
10DFEE77  |   12.5   |  11     |   4.7   |
1EERSD7X  |   15.5   |  10     |   3.5   |


table3包含id_code2,data4,data5,data6

id_code1   | data4  |  data5  | data6 |
-----------+--------+---------+-------+
M71X456700 |   29   |  11     |   5   |
N71X456800 |   38   |  18     |   2   |
P71X456900 |   34   |  11     |   3   |
Q71X456135 |   47   |  12     |   5   |
R71X456789 |   12   |  14     |   5   |
S71X456239 |   15   |  11     |   4   |
S86X436477 |   15   |  19     |   3   |


现在我不能用table2选择table3

select table3.* from table3
where table3.id_code2 in
        ( select table1.id_code2
        from table1 where
        locate('08',table1.id_code1)=3)


id_code1   | data4  |  data5  | data6 |
-----------+--------+---------+-------+
M71X456700 |   29   |  11     |   5   |
N71X456800 |   38   |  18     |   2   |
P71X456900 |   34   |  11     |   3   |


我希望这样的结果

id_code1  | id_code2  |  data1 |  data2  | data3  |  data4 | data5  | data6
----------+-----------+--------+---------+--------+--------+--------+-------
1008SD7X  |M71X456700 | 12.9   |  10     |   7.5  |  29   |  11     |   5   |
1008DG5B  |N71X456800 | 13.8   |  10     |   4.2  |  38   |  18     |   2   |
100894EX  |P71X456900 | 15.4   |  11     |   3.0  |  34   |  11     |   3   |

最佳答案

遵循以下原则即可:

SELECT  t1.id_code1,
        t1.id_code2,
        t2.data1,
        t2.data2,
        t2.data3,
        t3.data4,
        t3.data5,
        t3.data6
  FROM  table1 t1
    INNER JOIN table2 t2 ON t2.id_code1 = t1.id_code1
    INNER JOIN table3 t3 ON t3.id_code2 = t1.id_code2
  WHERE LOCATE('08', t1.id_code1) = 3;


这将从table1返回与您的WHERE子句匹配的所有行,并连接到table2table3并从这些表中返回适当的列。

结果:

id_code1  id_code2    data1  data2  data3  data4  data5  data6
1008SD7X  M71X456700  12.9   10     7.5    29     11     5
1008DG5B  N71X456800  13.8   10     4.2    38     18     2
100894EX  P71X456900  15.4   11     3      34     11     3


请注意,我已将id_code1的名称更改为id_code2,因为我认为这是由于输入错误造成的。

工作SQL Fiddle

关于mysql - SQL从具有2个表和条件的查找表中选择数据,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/56136233/

10-15 18:55
查看更多