如何利用某一时期重复的数据创建结构

Question

我需要用这种方式从两个表返回数据。

Array(
    [0] => Array(
        [Name] => lorem,
        [Qty] => 2),
    [1] => Array(
        [Name] => ipsum,
        [Qty] => 1),
    [2] => Array(
        [Name] => dolor,
        [Qty] => 0)
    )

PHP只负责获取数据，不会进行任何操作来组织数据。两个表，一个用来记录某个事件、日期和与事件相关的表的id。桌子是这样的：

tableA
id |   date     | tableB_id
1    2020-10-02   2
1    2020-10-19   2
1    2020-10-21   1
1    2020-11-2    3
1    2020-11-11   1

tableB
id | name     
1    lorem
2    ipsum
3    dolor 

和我的SQL查询

SELECT b.name as Name, a.created_at as created
FROM tableA b 
 INNER JOIN tableA a ON b.tableA_id = a.id WHERE MONTH(b.created_at) = '10' ORDER BY a.id;

这会导致

Name | created
ipsum  2020-10-02
ipsum  2020-10-19
lorem  2020-10-21

Answer 1

您可以使用下一个php代码：

$query = "SELECT 
    name Name, COUNT(tableA.id) Qty
FROM tableB
LEFT JOIN tableA ON tableB.id = tableA.tableB_id AND MONTH(tableA.date) = 10
GROUP BY tableB.name;";

// get DB version using PDO
$stmt = $pdo->prepare($query);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

print_r($rows);

活生生的PHP小提琴

结果：

Array
(
    [0] => Array
        (
            [Name] => dolor
            [Qty] => 0
        )

    [1] => Array
        (
            [Name] => ipsum
            [Qty] => 2
        )

    [2] => Array
        (
            [Name] => lorem
            [Qty] => 1
        )

)