mysql - 我如何从子表中获取序列号

Question

我有两个表，一个是序列表，一个是主表。我在下面展示了两个表格格式。我想得到下面的确切输出。我是这个领域的新手。请帮助我做到这一点。

连续剧

FLD_ID  FLD_SERIAL  FLD_TYPE
1   1234-34563-2323-2345    MONITOR
2   1234-34563-2323-2346    KEYBOARD
3   1234-34563-2323-2347    MONITOR
4   1234-34563-2323-2348    MONITOR
5   1234-34563-2323-2349    MONITOR
6   1234-34563-2323-2350    MONITOR
7   1234-34563-2323-2351    KEYBOARD
8   1234-34563-2323-2352    CPU
9   1234-34563-2323-2353    CPU
10  1234-34563-2323-2354    CPU

主要

FLD_ID  FLD_CPU FLD_MONITOR FLD_KEYBOARD
1           8   1           7
2           9   4   
3          10               2

预期输出

FLD_ID  FLD_CPU                 FLD_MONITOR         FLD_KEYBOARD
1   1234-34563-2323-2352    1234-34563-2323-2345    1234-34563-2323-2351
2   1234-34563-2323-2353    1234-34563-2323-2348    
3   1234-34563-2323-2354                        1234-34563-2323-2346

Answer 1

这样的东西对你有用:

SELECT 
  `FLD_ID`,
  IFNULL((SELECT `FLD_SERIAL` FROM `Serial` WHERE `FLD_ID` = `FLD_CPU`),'') AS `FLD_CPU`,
  IFNULL((SELECT `FLD_SERIAL` FROM `Serial` WHERE `FLD_ID` = `FLD_MONITOR`),'') AS `FLD_MONITOR`,
  IFNULL((SELECT `FLD_SERIAL` FROM `Serial` WHERE `FLD_ID` = `FLD_KEYBOARD`),'') AS `FLD_KEYBOARD`
FROM
  `Main`