mysql - SQL左联接具有多个值

我有一个非常旧的数据库，有一个非常糟糕的表结构，我正在尝试创建这些表的更好的结构。为此，我需要匹配两个表，以获取类别的id。
这是我的两张旧桌子：
表类别：

| ID |  catname     |  cat1  |    cat2     |   cat3  | cat4 |
+----+--------------+--------+-------------+---------+------+
|  1 |  bike        |  bike  | NULL        | NULL    | NULL |
|  2 |  accessories |  bike  | accessories | NULL    | NULL |
|  3 |  helmets     |  bike  | accessories | helmets | NULL |
|  4 |  lights      |  bike  | accessories | lights  | NULL |
|  5 |  led         |  bike  | accessories | lights  | led  |

餐桌产品：

| ID |  productnr  |  productname  | cat1  |    cat2     |   cat3  | cat4 |
+----+-------------+---------------+-------+-------------+---------+------+
|  1 |  451157     |  productya    |  bike | accessories | NULL    | NULL |
|  2 |  555523     |  product11    |  bike | accessories | helmets | NULL |
|  3 |  234432     |  helmetxqa    |  bike | accessories | helmets | NULL |
|  4 |  666623     |  lightblue    |  bike | accessories | lights  | NULL |
|  5 |  542123     |  foobarlight  |  bike | accessories | lights  | led  |

首先，我想从products表中去掉cat1、2、3和4列。
所以我得到这样的结果：

| ID |  catId  | productnr  |  productname  |
+----+---------+------------+---------------+
|  1 |  2      | 451157     |  productya    |
|  2 |  3      | 555523     |  product11    |
|  3 |  3      | 234432     |  helmetxqa    |
|  4 |  4      | 666623     |  lightblue    |
|  5 |  5      | 542123     |  foobarlight  |

有人能告诉我，我应该如何做一个查询，检查4只猫是否都匹配，然后给我猫的id？我试过这种方法，但我认为这是错误的方法，因为每次一个产品只有2或3只猫，我没有得到相关的catId。所以它只适用于定义了所有4种cat的产品。

SELECT
    cat.`id`,
    prod.`productnr`,
    prod.`productname`
FROM
    products as prod
LEFT JOIN
    categorys as cat
ON
    cat.`cat1` = prod.`cat1`
AND
    cat.`cat2` = prod.`cat2`
AND
    cat.`cat3` = prod.`cat3`
AND
    cat.`cat4` = prod.`cat4`

如果有人也对我有用，请告诉我。;-)
谢谢你帮我：）

最佳答案

您可以通过对每个类别组合执行单独的联接来实现这一点。当然，这是一个等级排序，你不想重复。因此，以下检查：

SELECT prod.id, coalesce(c4.id, c3.id, c2.id, c1.id) as catid
       prod.`productnr`, prod.`productname`
FROM products prod left join
     categorys c4
     on c4.cat1 = prod.cat1 and c4.cat2 = prod.cat2 and
        c4.cat3 = prod.cat3 and c4.cat4 = prod.cat4 left join
     categorys c3
     on c3.cat1 = prod.cat1 and c3.cat2 = prod.cat2 and
        c3.cat3 = prod.cat3 and c3.cat4 is null and
        c4.id is null left join
     categorys c2
     on c2.cat1 = prod.cat1 and c2.cat2 = prod.cat2 and c2.cat3 is null and
        c3.id is null and c4.id is null left join
     categorys c1
     on c1.cat1 = prod.cat1 and c1.cat2 is null and
        c2.id is null and c3.id is null and c4.id is null;

在某些情况下，这可能会产生重复的行（尽管它可以避免这种情况）。如果发生这种情况，那么group by可能仍然是必要的。

关于mysql - SQL左联接具有多个值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/26474992/