php - 对 SQL 表中的 2 列进行计数并分组

Question

我正在使用这样的表结构:

departure | destination | Type    | Pilot
EDDK        EDDM          flight    name
EDDK        EDDI          flight    name
EDDI        EDDK          flight    name
EDDK        EDDP          flight    name
EDDM        EDDK          flight    name
EDDF        EDDK          flight    name
EDDF        EDDI          flight    name

and so on...

现在我正在使用像这样的 codeigniter sql 查询:

$query = $this->db->select('departure, COUNT(departure) as zaehler', False)
                    ->from('tablename')
                    ->where('Pilot', $user)
                    ->where('Type', 'flight')
                    ->group_by('departure')
                    ->order_by('zaehler', 'DESC')
                    ->get();

return $query->result();

这会产生预期的数组:

Array
(
   [0] => stdClass Object
      (
        [departure] => EDDK
        [zaehler] => 3
     )

   [1] => stdClass Object
      (
        [departure] => EDDF
        [zaehler] => 2
      )

   [2] => stdClass Object
      (
        [departure] => EDDM
        [zaehler] => 1
      )
   [3] => stdClass Object
      (
        [departure] => EDDI
        [zaehler] => 1
      )

我还对“目的地”使用相同的查询。是否有可能只用一次就获得这两个查询的结果？那么结果是这样的？

Array
  (
   [0] => EDDK
      (
        [departure] => 3
        [destination] => 3
     )

   [1] => EDDF
      (
        [departure] => 2
        [destination] => 0
      )

   [2] => EDDM
      (
        [departure] => 1
        [destination] => 1
      )
   [3] => EDDI
      (
        [departure] => 1
        [destination] => 2
      )
   [4] => EDDP
      (
        [departure] => 0
        [destination] => 1
      )

Answer 1

我将让您将其转换为您的 php 格式，但查询的一般形式如下:

SELECT Departure, DepCnt, DesCnt FROM
(SELECT Departure, COUNT(Departure) AS DepCnt FROM T
GROUP BY Departure)
INNER JOIN
(SELECT Destination, COUNT(Destination) AS DesCnt FROM T
GROUP BY Destination)
ON Departure = Destination

正如评论中提到的，当没有出发地或没有目的地时，上述解决方案将省略位置。以下修改涉及此问题。

SELECT Loc, COALESCE(DepCnt,0), COALESCE(DesCnt,0) FROM
(SELECT DISTINCT Departure AS Loc FROM T
UNION
SELECT DISTINCT Destination FROM T)
LEFT OUTER JOIN
(SELECT Departure, COUNT(Departure) AS DepCnt FROM T
GROUP BY Departure) ON Departure = Loc
LEFT OUTER JOIN
(SELECT Destination, COUNT(Destination) AS DesCnt FROM T
GROUP BY Destination) ON Destination = Loc