选择查询，选择多个列，其中一列应该是不同的 mssql - SELECT query with selecting multiple columns where one column should be distinct mssql

SELECT query with selecting multiple columns where one column should be distinct mssql

我有一个包含PRTL_BusRoute的表格，其中提到了各个区域的ID，AreaID是每个区域下的ID's区域。

**ID**   **BustrouteID**   **AreaID**
    0          1             3
    1          1             5
    1          1             6
    2          2             5
    3          2             6    
    4          2             8
    5          3             9
    6          3             12
    7          4             1
    8          4             8
    9          4             11

公交路线的名称存储在tbl_BusRoute中，区域存储在dbo.GEN_Area中。我需要像这样显示：

 Zonename1
    Areas coming under this zone in a list
 Zonename2
    Areas coming under this zone in a list
 ........

我的 php 代码是这样的：

$area = '';                                                               
$getbus = mssql_query("SELECT BusRouteID,AreaID  FROM dbo.Acc_BusRouteDetail");
       while($data_getbus = mssql_fetch_row($getbus)){ 
           $getZone = mssql_query("SELECT Busroute FROM PRTL_BusRoute 
           WHERE BusRouteID = '$data_getbus[0]'");  
           while($data_getZone = mssql_fetch_row($getZone)){
                     $zone_name =  "<h4>".$data_getZone[0]."</h4>"; 
                     }  
       $getArea = mssql_query("SELECT AreaName_1 FROM dbo.GEN_Area 
          WHERE AreaID = '$data_getbus[1]'  ORDER BY AreaName_1");
       while($data_getArea = mssql_fetch_row($getArea)){
           $area .= $data_getArea[0]."</br>";
       }
       echo $zone_name."<br/>".$area."<br/>";
   }

输出是这样的：

ZoneName1
      Areaname1
   ZoneName1
      AreaName1
      AreaName2
   ZoneName1
      AreaName1
      Areaname2
      AreaName3
   ZoneName2
     Areaname1
   ZoneName2
     Areaname1
     Areaname2 so on

如果可能的话，我想知道如何选择不同的公交路线及其下的所有区域。我用完了如何选择单个路由 ID 的逻辑，然后选择它下的所有区域

我会稍微

更改 SQL 查询以一次获取所有数据，每次循环并打印该区域，同时如果自最后一行以来发生了变化，则只打印总线路线。像这样(未经测试的(代码;

$last_route = '';
$getbus = mssql_query("SELECT br.BusRoute,a.AreaName_1 FROM dbo.Acc_BusRouteDetail brd JOIN dbo.PRTL_BusRoute br ON br.busrouteid = brd.busrouteid JOIN dbo.GEN_Area a ON a.areaid = brd.areaid ORDER BY br.busroute, a.areaname_1");
while($data_getbus = mssql_fetch_array($getbus)){ 
   if($last_route != $data_getbus['BusRoute']) {
      $last_route = $data_getbus['BusRoute'];
      echo '<h4>'.$last_route.'</h4>';
   }
   echo '<br/>'.$data_getbus['AreaName_1'].'</br>';
}