我的用户表带有免费用户或付费用户的标志，并带有注册日期。就像是
+------------+-----------+--------------+
| USERNAME | FREE_USER | DATE_OF_REG |
+------------+-----------+--------------+
| user_name1 | y | 2018-01-14 |
| user_name2 | n | 2017-12-23 |
| user_name3 | y | 2017-12-12 |
| user_name4 | y | 2017-11-19 |
| user_name5 | n | 2017-09-13 |
| user_name6 | y | 2017-08-27 |
| user_name7 | n | 2017-07-06 |
+------------+-----------+--------------+
我需要最近6个月的free_users和paid_users计数，如果一个月内没有注册用户，则应将0设为免费和付费计数。
我想为免费用户和付费用户创建折线图，如本示例（http://www.chartjs.org/samples/latest/charts/line/basic.html）所示。为此，我需要使用逗号（，）分隔格式的数据，例如：
month_of_registration：['八月'，'九月'，'十月'，'十一月'，'十二月'，'一月']
free_users_count_for_related_month：[0，1，0，1，0，1，1]
paid_users_count_for_related_month：[1、0、1、0、0、1、0]
如何在PHP中使用mysql查询来实现这一点。
我写了这个查询：
SELECT MONTH(date_of_reg) as monthno, MONTHNAME(date_of_reg) as month_name, YEAR(date_of_reg) as year,
COUNT(CASE WHEN `free_user` = 'y' THEN 1 END) AS free_user,
COUNT(CASE WHEN `free_user` = 'n' THEN 1 END) AS paid,
COUNT(CASE WHEN `free_user` != '' THEN 1 END) as total_users
FROM tbl_agents GROUP BY MONTH( date_of_reg ) ORDER BY year, monthno
但是我没有达到我想要的。
最佳答案
的SQL
首先，您需要创建一个表（临时表或其他表），该表代表最近6个月（tbl_agents中可能没有任何记录）。您可以使用“联合”语法和date_sub函数来执行此操作（这使跟踪事件变得容易）：
select now() as month union
select date_sub(now(), INTERVAL 1 MONTH) as month union
select date_sub(now(), INTERVAL 2 MONTH) as month union
select date_sub(now(), INTERVAL 3 MONTH) as month union
select date_sub(now(), INTERVAL 4 MONTH) as month union
select date_sub(now(), INTERVAL 5 MONTH) as month union
select date_sub(now(), INTERVAL 6 MONTH) as month
输出：
month
2018-01-25T03:04:22Z
2017-12-25T03:04:22Z
2017-11-25T03:04:22Z
2017-10-25T03:04:22Z
2017-09-25T03:04:22Z
2017-08-25T03:04:22Z
2017-07-25T03:04:22Z
然后，我们可以在当月对tbl_agents使用LEFT JOIN并使用您现有的SQL来实现必要的输出。请注意，我们正在上面的临时表中给出日期：
SELECT MONTH(m.month) AS monthno,
MONTHNAME(m.month) as month_name,
YEAR(m.month) as year,
SUM(CASE WHEN `free_user` = 'y' THEN 1 ELSE 0 END) AS free_user,
SUM(CASE WHEN `free_user` = 'n' THEN 1 ELSE 0 END) AS paid,
SUM(CASE WHEN `free_user` != '' THEN 1 ELSE 0 END) as total_users
FROM (
select now() as month union
select date_sub(now(), INTERVAL 1 MONTH) as month union
select date_sub(now(), INTERVAL 2 MONTH) as month union
select date_sub(now(), INTERVAL 3 MONTH) as month union
select date_sub(now(), INTERVAL 4 MONTH) as month union
select date_sub(now(), INTERVAL 5 MONTH) as month union
select date_sub(now(), INTERVAL 6 MONTH) as month
) AS m
LEFT JOIN tbl_agents as tb
ON MONTH(tb.DATE_OF_REG) = MONTH(m.month)
GROUP BY MONTH(tb.DATE_OF_REG)
ORDER BY m.month DESC;
输出：
| monthno | month_name | year | free_user | paid | total_users |
|---------|------------|------|-----------|------|-------------|
| 1 | January | 2018 | 1 | 0 | 1 |
| 12 | December | 2017 | 1 | 1 | 2 |
| 11 | November | 2017 | 1 | 0 | 1 |
| 10 | October | 2017 | 0 | 0 | 0 |
| 9 | September | 2017 | 0 | 1 | 1 |
| 8 | August | 2017 | 1 | 0 | 1 |
| 7 | July | 2017 | 0 | 1 | 1 |
您可以在这个小提琴中看到它的作用：http://sqlfiddle.com/#!9/5ad682/12/0
的PHP
示例中所需的输出是JSON-因此，您要做的就是从MySQL获取数据并建立一个表示输出的数组，然后再转换为JSON。例如，它可能看起来像：
<?php
//$data = mysql_query_results
$output_array = array('month_of_registration' => array(),
'free_users_count_for_related_month' => array(),
'paid_users_count_for_related_month' => array());
foreach( $data as $e ) {
$output_array['month_of_registration'][] = $e['month_name'];
$output_array['free_users_count_for_related_month'][] = $e['free_user'];
$output_array['paid_users_count_for_related_month'][] = $e['paid'];
}
$output_data = json_encode($output_array);