mysql - 将多行中的列值插入到单行中

Question

我有一个看起来像的表格

hop_id hop_name site_name
1      abcd     ctg
2      bcss     ctg
3      cdhj     ctg
4      dght     dhk
5      efgh     dhk

我想执行 SQL 查询以返回如下结果:

hop_id  hop_1  hop_2  hop_3  hop_4  hop_5....  site_name
1       abcd   bcss   cdhj                     ctg
2       dght   efgh                            dhk

有知道如何为其编写正确的sql吗？任何提示将不胜感激。

Answer 1

我像这样解决了我的问题...谢谢你的帮助。

路线路径:

                                    <?php
                                    //divide route path

                                        echo "<br>" .$route_path."</br>";
                                        $hop = explode(">",$route_path);
                                        $hop_count = count($hop)-1;

                                        //maximum number of path columns in the database
                                        $maxPath=9;

                                        $lastIndex = 0;                 
                                        for($i=0; $i<=$maxPath; $i++)
                                        {
                                            if($i<=$hop_count)
                                            {
                                                $path[$i] = $hop[$i];                           
                                                $lastIndex = $i;
                                            }
                                            else
                                            {
                                                $path[$i] = null;
                                            }
                                        }
                                        $path[$lastIndex+1] = $router_name;

                                        for($i=1; $i<=$maxPath; $i++)
                                        {
                                            if($path[$i] != NULL)
                                            {
                                                $hop_route[$i-1] = $path[$i-1].'>'.$path[$i];
                                                echo $hop_route[$i-1].'<br/>';
                                            }
                                            else
                                            {
                                                $hop_route[$i-1] = NULL;
                                            }
                                        }                       


                                        //inserting into database
                                        $sql1 = "INSERT INTO hop (hop_id, hop_1_route, hop_2_route, hop_3_route, hop_4_route, hop_5_route, hop_6_route, hop_7_route, hop_8_route, hop_9_route, hop_count, site_name) VALUES ('', '".$hop_route[0]."', '".$hop_route[1]."', '".$hop_route[2]."', '".$hop_route[3]."', '".$hop_route[4]."', '".$hop_route[5]."', '".$hop_route[6]."', '".$hop_route[7]."', '".$hop_route[8]."', '".$hop_count."', '".$site_name."')";
                                        if ($conn->query($sql1) === TRUE) 
                                        {
                                            //echo "New record created successfully";
                                        } 
                                        else 
                                        {
                                            echo "Error: " . $sql1 . "<br>" . $conn->error;
                                        }