SQL双选择

Question

这是我的桌子

sno | city | state | stateid

1   | chennai | tamilnadu | 1

2   | dindigul | tamilnadu | 1

3   | trivandrum | Kerala | 2

4   | cochin | Kerala | 2

我想要这样显示(应该在上升的时候选择国家Id，城市应该显示到更大的状态)。

tamilnadu

金奈

丁迪古尔

Kerala

三文鱼

科钦

我怎样才能做到这一点？

到目前为止，我尝试的是

(select city from (select * from state order by statid asc))

在那里，内部查询正在运行(由stateid asc从状态顺序选择* from )，但是当我使用(select city from (innerquery)时。

它正在抛出#1248 - Every derived table must have its own alias

我怎么才能解决这个问题？

Answer 1

在PHP中这样做：

 <?php
 $con = mysqli_connect("localhost", "root", "", "dbname");

$rs = $con->query('select city, state from mytable');
while ($row = $rs->fetch_array(MYSQLI_ASSOC)) {
  $state_cities[$row['state']][] = $row['city'];
}

foreach ($state_cities as $state => $cities) {
  echo "<b>".$state . "</b><br>";
  foreach ($cities as $title) {
  echo $title . "<br>";
  }
}

?>

Answer 2

(select city from (select * from state order by statid asc))

它需要带有运算符的表名、位置和字段名。

(select city from <TableName> WHERE <FieldName> <Operator> (select * from state order by statid asc))