mysql - select sub sub sql 查询 3 个表

Question

表1 - 客户数据

c_id(整数)

名称(varchar)

表2 - account_data

a_id(整数)

c_id (int) -> 使用customer_data。c_id

plan_id(整数)

表3 - 游戏数据

g_id(整数)

排序(整数)

a_id (int) -> 使用account_data。a_id

游戏名称(变量)

我使用子查询从account_data中选择game_data。像这样:

SELECT `a_id`,`c_id`,`plan_id`,
(SELECT `game_name` FROM `game_data` WHERE `a_id` = a.`a_id` ORDER BY `sort` ASC LIMIT 1) as main_game
FROM `accoubt_data` AS a WHERE `a_id` > 0 ORDER BY `id` DESC

此sql适用于从account_data中选择game_data

但我无法使用它从 customer_data 中选择 game_data

我该如何做某事？

customer_data
 +----------------------+
 | c_id      | name     |
 +----------------------+
 | 1001      | Joe      |
 | 1002      | John     |
 | 1003      | David    |
 +----------------------+

account_data
 +-------------------------------------+
 | a_id      | cid      |  plan_id     |
 +-------------------------------------+
 | 6015     | 1002     |   34          |
 | 6028     | 1003     |   1           |
 | 6088     | 1001     |   9           |
 +-------------------------------------+

game_data
 +--------------------------------------+
 | g_id     | game_name    |    a_id    |
 +--------------------------------------+
 | 8011     | GTA5         |    6015    |
 | 8023     | WWE2016      |    6028    |
 | 8088     | FIFA16       |    6088    |
 | 8095     | FIFA17       |    6088    |
 | 8086     | FIFA15       |    6088    |
 +--------------------------------------+

这是基本选择

我需要从 customer_data.c_id 获取此数据

 +--------------------------------------+
 | c_id   | name     | frist_game       | 
 +--------------------------------------+
 | 1001   |  Joe     | FIFA15           |
 | 1002   |  John    | GTA5             |   
 | 1003   |  David   | WWE2016          |  
 +--------------------------------------+

c_id > a_id > g_id ORDER BY 排序 ASC

Answer 1

尝试加入:

SELECT c.`c_id`,c.name,
       (SELECT `game_name` FROM `game_data` 
        WHERE `a_id` = a.`a_id`
        ORDER BY `sort` ASC LIMIT 1) as first_game
FROM `accoubt_data` a
JOIN customer_data c ON(a.c_id = c.c_id)
WHERE a.`a_id` > 0 
ORDER BY a.`id` DESC