php - 我如何重写我的 sql 查询，以便它以特定格式返回值(使用 mysql 或 php)？

Question

我有一个名为 number 的表:

number_id  |  number_name
   1            aaaa
   2            bbbb
   3            cccc
   4            dddd
   5            eeee
   6            ffff

(它只包含 6 个条目)

我还有一个名为 texts 的表:

text_id  |  start_time  |  number_id  |  text_content
   1        some date1  |     2       |    blabla1
   2        some date2  |     1       |    blabla2
   3        some date3  |     2       |    blabla3
   4        some date4  |     3       |    blabla4
   5        some date5  |     4       |    blabla5
   6        some date6  |     6       |    blabla6
  etc.

(它包含很多条目)。

现在，当我进行这样的查询时:

SELECT
   * 
FROM
   (SELECT
      DATEDIFF(now(),
      start_time) AS days_ago,
      number_id,
      COUNT(text_id) AS num_texts 
   FROM
      TEXTS 
   WHERE
      start_time BETWEEN  DATE_SUB(NOW(), INTERVAL 100 DAY) AND NOW() 
   GROUP BY
      DATE(start_time),
      number_id) AS temp

它向我返回过去 100 天中每个地点每天的短信数量，编号为 number_id。结果如下所示:

当我将它编码为 json 时，我得到:

[{"days_ago":"19","number_id":"1","num_texts":"179"},
{"days_ago":"19","number_id":"5","num_texts":"1"},
{"days_ago":"18","number_id":"1","num_texts":"61"},
{"days_ago":"18","number_id":"2","num_texts":"1"},
{"days_ago":"18","number_id":"5","num_texts":"1"},
{"days_ago":"18","number_id":"6","num_texts":"3"},
{"days_ago":"17","number_id":"3","num_texts":"1"},
{"days_ago":"8","number_id":"1","num_texts":"2"},
{"days_ago":"8","number_id":"2","num_texts":"2"},
{"days_ago":"7","number_id":"4","num_texts":"1"},

我想编写一个不同的查询，或者更改现有的查询。有没有一种方法可以编写一个查询，以下列方式向我返回这些数据:

[{"days_ago": "7", "number_id" : "1", "num_texts" : "18", "number_id" : "2", "num_texts" : "12", "number_id" : "3", "num_texts" : "12" , ... , "number_id" : "6", "num_texts" : "1"},
{"days_ago": "6", "number_id" : "1", "num_texts" : "18", "number_id" : "2", "num_texts" : "12", "number_id" : "3", "num_texts" : "12" , ... , "number_id" : "6", "num_texts" : "1"},
{"days_ago": "5", "number_id" : "1", "num_texts" : "18", "number_id" : "2", "num_texts" : "12", "number_id" : "3", "num_texts" : "12" , ... , "number_id" : "6", "num_texts" : "1"},
{"days_ago": "4", "number_id" : "1", "num_texts" : "18", "number_id" : "2", "num_texts" : "12", "number_id" : "3", "num_texts" : "12" , ... , "number_id" : "6", "num_texts" : "1"},
{"days_ago": "3", "number_id" : "1", "num_texts" : "18", "number_id" : "2", "num_texts" : "12", "number_id" : "3", "num_texts" : "12" , ... , "number_id" : "6", "num_texts" : "1"},
{"days_ago": "2", "number_id" : "1", "num_texts" : "18", "number_id" : "2", "num_texts" : "12", "number_id" : "3", "num_texts" : "12" , ... , "number_id" : "6", "num_texts" : "1"},
{"days_ago": "1", "number_id" : "1", "num_texts" : "18", "number_id" : "2", "num_texts" : "12", "number_id" : "3", "num_texts" : "12" , ... , "number_id" : "6", "num_texts" : "1"}] 

基本上每一行都应该包含关于 days_ago 的信息以及 6 个 number_id 中每一个的值 num_texts。我需要将此查询限制为仅过去 7 天，因此我知道我可以在此处修改它:

BETWEEN DATE_SUB(NOW(), INTERVAL 100 DAY) AND NOW()

并将 100 更改为 7，但是否有可能以某种方式获取此特定格式的其余信息？

还有一件事 - 我从 php 调用这个查询，所以如果在 php 中以某种方式更容易解析它并作为 json 返回，那也是一个选项。

非常感谢任何提示家伙!

=========编辑:

正如@AlexBlex 指出的那样——我提出的 json 结构可能无效，因为多次使用相同的键名。为了帮助您更好地理解我的问题 - http://thevectorlab.net/flatlab/morris.html这是 morris js 图表的示例。在该示例中，我想生成名为 Quarterly Apple iOS device unit sales 的图表。在水平线上，我需要日期(几天前)，在垂直线上，我需要 num_texts。我想要 6 行，每行代表不同的 number_id。

Answer 1

假设您的 number具有这 6 个永远不会改变 的固定值，可以通过链接 texts 的 6 个副本来实现表(或创建 6 个 View )。

但是如果你在 php 中通过跟踪 days_ago 的时间来做它可能会更干净更改并添加新的 JSON 记录。您还必须确保没有 number_id被跳过并为 num_texts 添加零值对于任何跳过的。

添加，示例伪代码:

$days_ago=0;
while ([read_records]) {
    if (record['days_ago']!=$days_ago {
        $days_ago!=0 [Close line]
        $days_ago=record['days_ago'];
        [Add new line]
        [Add days_ago field]  }
    [Add number_id and num_texts fields]
}
[Close line]

第二次编辑，带 View 的解决方案:

如果view_1是我在下面描述的 View 和view_2 number_id 的 View 2 ，您可以像这样链接它们:

SELECT view_1.days_ago, view_1.num_texts AS num_texts1, view_2.num_texts AS num_texts2
FROM view_1 INNER JOIN view_2 ON view_1.days_ago=view_2.days_ago

只有当您拥有所有 number_id 的条目时，此 willo 才能正常工作对于每个 days_ago , 否则你将不得不创建一个只有 days_ago 的 View 并使用左/右连接加入 number_id意见。