mysql加入和分组

Question

从发帖的角度来看，我是这个论坛的新手，正如这个问题可能表明的那样。我对数据的显示方式有一些疑问。

所以，我有 3 个表(我只显示我想要的列):

访客:

center_id (where the visitor was)  |  state_id (where they came from)

中心:

center_id  |  center

状态:

state_id  |  state

这是我一直在使用的查询

SELECT states.state, visitors.center_id, visitors.state_id, centers.center, COUNT(visitors.state_id) AS totalCount 
FROM visitors 
LEFT JOIN states ON states.state_id = visitors.state_id 
LEFT JOIN centers ON centers.center_id = visitors.center_id 
WHERE visitors.vdate = <some date> AND visitors.state_id <> '0'  
GROUP BY centers.center, visitors.state_id

这会产生以下数组:

Array
(
    [0] => Array
        (
            [state] => Connecticut
            [location_id] => 1
            [state_id] => 8
            [center] => Little River
            [totalCount] => 1
        )

    [1] => Array
        (
            [state] => California
            [location_id] => 5
            [state_id] => 6
            [center] => North Augusta
            [totalCount] => 1
        )

    [2] => Array
        (
            [state] => Colorado
            [location_id] => 5
            [state_id] => 7
            [center] => North Augusta
            [totalCount] => 2
        )
    [6] => Array
        (
            [state] => Connecticut
            [location_id] => 9
            [state_id] => 8
            [center] => Santee
            [totalCount] => 2
        )

     [7] => Array
        (
            [state] => Virginia 
            [location_id] => 9
            [state_id] => 51
            [center] => Santee
            [totalCount] => 1
        )
)

这才是我真正想要的:

Array
(
    [Little River] => Array
        (
          [0] => Array
             (
                [state] => Connecticut
                [state_id] => 8
                [totalCount] => 1
             )
        )

    [North Augusta] => Array
        (
           [0] => Array
              (
                [state] => California
                [state_id] => 6
                [totalCount] => 1
              )

           [1] => Array
              (
                [state] => Colorado
                [state_id] => 7
                [totalCount] => 2
              )
          )
      [Santee] => Array
          (
           [0] => Array
              (
                [state] => Connecticut
                [state_id] => 8
                [totalCount] => 2
              )

           [1] => Array
              (
                [state] => Virginia 
                [state_id] => 51
                [totalCount] => 1
              )
          )
)

最终我将它放入一个看起来像这样的表中:

__________________
|State   |   Count|
-------------------
|     Santee      |
-------------------
| Georgia |   5   |
-------------------
| Alabama |   10  |
-------------------
| North Augusta   |
-------------------
| another |    7  |
-------------------

抱歉啰嗦，但这是我能描述的唯一方式。

我也试过用 php 来破解它，但我也可能在那里做错了什么。我可以制作一个包含 3 列的表格，其中列出每个州的中心，但我真的在寻找一行显示中心，然后是所有州和该中心以及下一个中心的计数。

如有任何帮助，我们将不胜感激。

Answer 1

查询可能只返回二维(或更少)维度的结果。您将需要解析此结果集以将其转换为树。

用PHP，真的没有难度:

foreach ($rawResultSet as $row) {
    $finalResult[$row['center']][] = array(
        $row['state'],
        $row['state_id'],
        $row['totalCount']

    );
}