在我的CRM系统中,我有带引线的桌子。我想做一个图表,看看在过去的7天里增加了多少线索。为了达到这个目的,我需要从上周开始的每一天都有之和,。
我的表tab_leads附带了lead_id (整数)和lead_create_date (时间戳,格式: 0000-00- 00 :00:00)。
所以我需要这样的东西:
现在,我提出了这个问题:
SELECT
DATE(lead_create_date) AS `Date`,
COUNT(*) AS `Leads`
FROM
tab_leads
WHERE
lead_create_date >= CURRENT_DATE - INTERVAL 6 DAY
GROUP BY
DATE(lead_create_date)但问题是,如果在那些日子里,我们没有任何数据(例如。(周末)我得到的钱不到7笔。例:
在绘制图表时,我需要始终使用7和,即使值为0。如何在MySQL或MySQL + PHP中做到这一点?
..UPDATE:我只是想在成功的情况下创建SQL。样本数据:
CREATE TABLE tab_leads (
`lead_id` int,
`lead_create_date` timestamp
) ENGINE=InnoDB
INSERT INTO tab_leads
(`lead_id`, `lead_create_date`)
VALUES
(0, '2015-05-02 05:30:40'),
(1, '2015-05-02 00:00:00'),
(2, '2015-05-03 00:00:00'),
(3, '2015-05-03 00:00:00'),
(4, '2015-05-05 00:00:00'),
(5, '2015-05-06 00:00:00'),
(6, '2015-05-07 00:00:00'),
(7, '2015-05-08 00:00:00'),
(8, '2015-05-08 00:00:00')
;发布于 2015-05-08 14:07:50
在这里,查询是
select
t1.Date,
coalesce(t2.Leads, 0) AS Leads
from
(
select DATE_FORMAT(a.Date,'%Y-%m-%d') as Date
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
where a.Date BETWEEN curdate() - interval 7 DAY AND curdate()
)t1
left join
(
SELECT
DATE(lead_create_date) AS `Date`,
COUNT(*) AS `Leads`
from tab_leads
WHERE
lead_create_date >= CURRENT_DATE - INTERVAL 6 DAY
GROUP BY DATE(lead_create_date)
)t2
on t2.Date = t1.Date
group by t1.Date
order by t1.Date desc查询的第一部分只是生成给定范围的日期,然后将其用作左表,然后向原始查询进行左连接。
从示例数据中,您将获得如下结果
+------------+-------+
| Date | Leads |
+------------+-------+
| 2015-05-08 | 2 |
| 2015-05-07 | 1 |
| 2015-05-06 | 1 |
| 2015-05-05 | 1 |
| 2015-05-04 | 0 |
| 2015-05-03 | 2 |
| 2015-05-02 | 2 |
| 2015-05-01 | 0 |
+------------+-------+
8 rows in set (0.02 sec)发布于 2015-05-08 13:12:59
不漂亮,但试试这个?
SELECT tmp.dt, src.Leads
FROM (
SELECT CURRENT_DATE - INTERVAL 6 DAY as dt UNION
SELECT CURRENT_DATE - INTERVAL 5 DAY UNION
SELECT CURRENT_DATE - INTERVAL 4 DAY UNION
SELECT CURRENT_DATE - INTERVAL 3 DAY UNION
SELECT CURRENT_DATE - INTERVAL 2 DAY UNION
SELECT CURRENT_DATE - INTERVAL 1 DAY UNION
SELECT CURRENT_DATE) as tmp
left join (
SELECT
DATE(lead_create_date ) AS `Date`,
COUNT(*) AS `Leads`
FROM
tab_leads
WHERE
lead_create_date >= CURRENT_DATE - INTERVAL 6 DAY
GROUP BY
DATE(lead_create_date )
) as src on tmp.dt = src.`Date`发布于 2015-05-08 13:50:02
因为在原始表中没有给定日期的数据,所以不能只返回0的行。您可能会看到一些常见的例子,如:
为每个用户提供每个字段的计数,即使它是0。
这些例子是可以完成的,假设每个user都有一个记录,即使没有some field的记录。在您的例子中,您没有第2天、第4天或第7天的记录,因此它们不能返回。
要解决这个问题,您需要创建一个最近7天的临时表。使用一个给出的这里示例
select a.Date
from (
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) a
where a.Date between DATE_SUB(NOW(), INTERVAL 7 DAY) and NOW();这会给你提供你需要的临时桌子。然后,您可以像这样预置您的聚合:
SELECT lead_create_date AS date, COUNT(*) AS numLeads
FROM myTable
GROUP BY lead_create_date;然后将其外部连接到临时表,该表将填充0值:
SELECT tmp.date, COALESCE(m.numLeads, 0) AS numLeads
FROM(
SELECT a.Date
FROM
(SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS Date
FROM
(SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
CROSS JOIN
(SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
CROSS JOIN
(SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c) a
WHERE a.date BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW()) tmp
LEFT JOIN(
SELECT lead_create_date AS date, COUNT(*) AS numLeads
FROM myTable
GROUP BY lead_create_date) m ON m.date = tmp.date
ORDER BY tmp.date;看上去很可怕,但很管用。根据另一个答案中的评论,它看起来也很好。
下面是一个SQL Fiddle示例。
编辑
澄清一下,我给出的第一个查询只是为了说明如何创建一个包含最后7个日期的临时表。第二个查询显示如何在整个表上预先形成聚合,以获取表中所有日期的计数。第三个查询将它们组合在一起,只显示最后七天的计数,这就是您正在寻找的答案。
https://stackoverflow.com/questions/30124147
复制相似问题