MySQL多对多表到表矩阵

I have a many to many table in MySQL - it can look something like:

我在MySQL中有很多表 - 它看起来像:

A | B
1 | 1
1 | 2
1 | 3
3 | 4
3 | 5
4 | 1
4 | 2
4 | 5

etc.

You'll notice that the numbers are not always contiguous, (A=1,3,4..) - I am looking for the fastest way to turning this into a matrix table, ignoring columns and rows with no data (e.g. A=2). What I have working is painfully slow as I am taking each row into a separate array in php, going through a nested while loop, where it checks if the key for array A and array B match the current index:

您会注意到这些数字并不总是连续的(A = 1,3,4 ..) - 我正在寻找将其转换为矩阵表的最快方法,忽略没有数据的列和行(例如A = 2)。我工作的速度非常慢,因为我将每行放入php中的一个单独的数组中,通过嵌套的while循环,它检查数组A和数组B的键是否与当前索引匹配:

while ($i <= $max_A) {
    if (in_array($i, $array_A)) {
        print ("<tr>");

        while ($j <= $max_B) {
            if (in_array($j, $array_B)) {
                print ("<td>");
                if (in_matrix($i, $j, $array_A, $array_B)) {
                    print ("1");
                } else {
                    print ("0");
                }
                print ("</td>");
            }
            $j++;
        }
        print ("</tr>\n");
        $j = $min_B;
    }
    $i++;
}

function in_matrix($search_value_A, $search_value_B, $array_A, $array_B) {
    // Store the array keys matching search
    $keys_A = array_keys($array_A, $search_value_A);
    $keys_B = array_keys($array_B, $search_value_B);
    foreach ($keys_A as $value) {
        if (in_array($value, $keys_B)) {
            return true;
        }
    }
    return false;
}

Perhaps there is more I can do MySQL side or speed up the search function - I tried finding a way to searching a two-dimensional array (e.g. return true if Column A = x AND Column B = y in a given row).

也许还有更多我可以做MySQL方面或加快搜索功能 - 我试图找到一种方法来搜索二维数组(例如,如果列A = x并且给定行中的列B = y,则返回true)。

Thanks

1 个解决方案

#1

It's called pivoting the data when it's done in SQL, because you're changing row into columnar data:

它被称为在SQL中完成数据转换,因为您正在将行更改为列数据:

   SELECT t.a,
          MAX(CASE WHEN t.b = 1 THEN 'x' ELSE NULL END),
          MAX(CASE WHEN t.b = 2 THEN 'x' ELSE NULL END),
          MAX(CASE WHEN t.b = 3 THEN 'x' ELSE NULL END),
          MAX(CASE WHEN t.b = 4 THEN 'x' ELSE NULL END),
          MAX(CASE WHEN t.b = 5 THEN 'x' ELSE NULL END),
     FROM TABLE t
GROUP BY t.a

The CASE statement is similar in structure to a SWITCH statement, but doesn't support fall through. You'll have to define a CASE statement for each row value you want to output as a column.

CASE语句在结构上与SWITCH语句类似,但不支持fall through。您必须为要作为列输出的每个行值定义CASE语句。

#1