php - 根据另一列值获取一列的值

Question

所以我有这张 table

TableTBL: 
+--------+------+-------+
|    ID  | Col1 | Col2  |
+--------+------+-------+
|      1 |    A |    30 | 
|      2 |    A |    20 | 
|      3 |    B |    40 |  
|      4 |    A |    10 |  
+--------+------+-------+

现在我想从基于 Col1 的 Col2 中获取值，比如说我想要 A 的所有值，所以结果将是 [30, 20, 10] 。

我尝试了以下查询，但似乎效果不佳:

SELECT DISTINCT Col2 FROM TableTBL WHERE Col1 = 'A' ORDER BY Col2 DESC;

我在 php 中的 foreach 循环中使用它，所以这里是带有 php 代码的查询:

$sql = "SELECT DISTINCT Col1 FROM TableTBL ORDER BY Col1 DESC;";
$stmt = mysqli_stmt_init($conn);
if (!mysqli_stmt_prepare($stmt, $sql )) 
{
    echo "SQL statement failed";
}
else
{
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);

    while ($row = mysqli_fetch_assoc($result)) 
    { 
        $rows[] = $row['Col1']; 

    } 
    for($i = 0; $i < count($rows) ;$i++)
    {
        echo $rows[$i].": {\n";

        $sql = "SELECT DISTINCT Col2 FROM TableTBL WHERE Col1 = '$rows[$i]' ORDER BY Col2 DESC;";
        $stmt = mysqli_stmt_init($conn);
        if (!mysqli_stmt_prepare($stmt, $sql)) 
        {
            echo "SQL statement failed";
        }
        else
        {
            mysqli_stmt_execute($stmt);
            $result = mysqli_stmt_get_result($stmt);

            while ($row = mysqli_fetch_assoc($result)) { 
                $rows[] = $row; 
            } 
            foreach($rows as $row) {
                $arrList[] = $row['Col2'];
            }
            $text =  implode("',' ", array_unique($arrList));
        }
    }
}

这里的$text是包含以逗号分隔的结果的字符串。

我在这里遇到的问题是，除了第一次迭代之外，我在所有迭代中都得到了重复的结果，它给了我 A 30, 20, 40, 10 ，但它给了我 B 30, 20, 40, 10.

Answer 1

正如@Barmar 建议的那样，使用单个查询:

$sql = "SELECT Col1, 
               GROUP_CONCAT(DISTINCT Col2 ORDER BY Col2 DESC SEPARATOR ', ') AS Col2 
        FROM TableTBL 
        GROUP BY Col1
        ORDER BY Col1 ";
$stmt = mysqli_stmt_init($conn);
if (!mysqli_stmt_prepare($stmt, $sql)) {
    echo "SQL statement failed";
} else {
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    $arrList = array();
    while ($row = mysqli_fetch_assoc($result)) {
        $arrList[] = array(
            'Col1' => $row['Col1'],
            'Col2' => $row['Col2']
        );
    }
    var_export($arrList);
}

输出:

array(
    0 => array(
        'Col1' => 'A',
        'Col2' => '30, 20, 10',
    ),
    1 => array(
        'Col1' => 'B',
        'Col2' => '40',
    ),
)

查看我的 Demo query .

如果你不知道 GROUP_CONCAT() 他是一个 documentation