php - 如何联接表以获取所有表A和大多数表B

我很确定我需要为此保留一个左连接，但是我有一个障碍。我需要根据表A中的列从表B中拉出一列。

TABLE A = list
list_id
user_id
operator_id
operator_name
operator_level


TABLE B = operators
operators_id
type
image
skill1
skill2
skill3
1
2
3
...
10

这是我现在拥有的SQL查询：

SELECT * FROM list l
    LEFT JOIN operators o ON l.operator_id = o.operators_id
WHERE l.user_id=1
ORDER BY o.10 DESC

它返回我想要的所有表A，并且还返回我不需要的所有表B。
列1-10包含INT值，并且这些列对应于表A中的operator_level。

因此，实际上，我需要创建一个临时列，并将与operator_level相对应的INT放入1-10列中，或者只为该行返回该列。我不知道该怎么做。

以下是一些示例数据和预期结果：

list list_id, user_id, operator_id, operator_name, operator_level
    1,       1,         2,     Johnson, Bob,        1
    2,       1,         3,     Mouse, Mickey,       9
    3,       1,         2,     Duck, Donald,        5

operators operator_id, type,    image,     skill1,    skill2, skill3,  1,   2,   3,   4,   5,   6,   7,   8,   9,  10
     1,      pilot,   image.jpg, F16,       B32,                   , 50,  60,  70,  80,  90,  100, 110, 120, 130, 140
     2,      medic,   image.jpg, first aid, trauma, general surgery, 100, 105, 110, 115, 120, 125, 130, 135, 140, 145
     3,      kitchen, image.jpg, knife,     soup,                  , 20,  22,  24,  26,  28,  30,  32,  34,  36,  38

Expected Results list_id, user_id, operator_id, operator_name, operator_level, type,    image,     skill1,    skill2, skill3,         op_rate
    1,       1,         2,     Johnson, Bob,        1,        pilot,   image.jpg, F16,       B32,                     50
    2,       1,         3,     Mouse, Mickey,       9,        medic,   image.jpg, first aid, trauma, general surgery, 140
    3,       1,         2,     Duck, Donald,        5,        kitchen, image.jpg, knife,     soup,                    28

抱歉，它在我的编辑器中排列良好，但是没有对齐。当我粘贴时。

将示例数据添加到sqlfiddle，不知道如何在其中添加预期结果。

http://sqlfiddle.com/#!2/390d8/1

最佳答案

外部联接具有基本的权利，但是您可以做的是限制在查询中选择的字段，如下所示：

select
    l.list_id,
    l.user_id,
    l.operator_id,
    l.operator_name,
    l.operator_level,
    `1`+`2`+`3`+...+`10` as opLevel
FROM
    list l
        LEFT JOIN operators o
            ON l.operator_id = o.operators_id
WHERE
    l.user_id=1
ORDER BY
    o.10 DESC

这是基于这样一个事实，您似乎对于正确的运算符有0的值和/或值。

话虽这么说，为什么在地球上会有十个字段来存储一点信息？您的表应该只包含一个名为“ OperatorLevel”的字段，并在其中包含值-除非我遗漏了某些内容。

或者，如果您有多个值并且想要最高的值，则也可以在查询中使用great（）函数：

select
    l.list_id,
    l.user_id,
    l.operator_id,
    l.operator_name,
    l.operator_level,
    greatest(`1`, `2`, ... `10`) as opLevel
FROM
    list l
        LEFT JOIN operators o
            ON l.operator_id = o.operators_id
WHERE
    l.user_id=1
ORDER BY
    o.10 DESC

编辑：好的，根据其他信息，您可以使用以下（ick ick）语句：

select
    l.list_id,
    l.user_id,
    l.operator_id,
    l.operator_name,
    l.operator_level,
    case
        when l.operator_level=1 then o.`1`
        when l.operator_level=2 then o.`2`
        when l.operator_level=3 then o.`3`
        // etc etc yuck!
        when l.operator_level=10 then o.`10`
    end as yicky
FROM
    list l
        LEFT JOIN operators o
            ON l.operator_id = o.operators_id
WHERE
    l.user_id=1
ORDER BY
    o.10 DESC

编辑2：

我非常建议进行数据标准化。

鉴于您拥有的数据，似乎您正在将数据复制到各种用户。这听起来像一场噩梦般的更新。据我了解，似乎每个“技能”都有薪水表，并且根据操作员的水平而增加吗？

我将创建一个具有以下结构的表（基于用户1的假设）：

skill   level   value
 B32     1   25
 B32     2   30
.....
 B32     10  75
 F16     1   25
 F16     2   30
...
 F16     10  75

然后，您可以简单地执行从“列表”表到“操作员”表的链接，然后根据其中的数据链接到“成绩”表。

这将使查询简单得多。

说了这么多，我实际上会考虑将您的“操作员”表标准化为仅技能。将“类型”和“图像”移到“列表”表中（并在使用时称其为用户）。

现在，“操作员”表应该重命名为具有以下数据的技能组表：

userID  skill   Level
 1   F16     4
 1   B32     8
 2   F_Aid   2

这将使您拥有具有3种以上技能的用户，并且使您可以轻松记录用户的每种技能水平。他们可能是B32的超级英雄，但在F16只是中等。

表a

php - 如何联接表以获取所有表A和大多数表B