MySQL在行中获得日期的计数(MySQL get count of periods where date in row)

MySQL在行中获得日期的计数(MySQL get count of periods where date in row)

我有一个MySQL表，类似于这个例子：

c_id date value 66 2015-07-01 1 66 2015-07-02 777 66 2015-08-01 33 66 2015-08-20 200 66 2015-08-21 11 66 2015-09-14 202 66 2015-09-15 204 66 2015-09-16 23 66 2015-09-17 0 66 2015-09-18 231

我需要得到的是日期排成行的时间段数。 我没有固定的开始或结束日期，可以有任何。

例如：2015-07-01 - 2015-07-02是第一期，2015-08-01是第二期，2015-08-20 - 2015-08-21是第三期，2015-09-14 - 2015- 09-18为第四期。 所以在这个例子中有四个时期。

SELECT SUM(value) as value_sum, ... as period_count FROM my_table WHERE cid = 66

不能整天搞清楚这一点.. Thx。

I have an MySQL table, similar to this example:

c_id date value 66 2015-07-01 1 66 2015-07-02 777 66 2015-08-01 33 66 2015-08-20 200 66 2015-08-21 11 66 2015-09-14 202 66 2015-09-15 204 66 2015-09-16 23 66 2015-09-17 0 66 2015-09-18 231

What I need to get is count of periods where dates are in row. I don't have fixed start or end date, there can be any.

For example: 2015-07-01 - 2015-07-02 is one priod, 2015-08-01 is second period, 2015-08-20 - 2015-08-21 is third period and 2015-09-14 - 2015-09-18 as fourth period. So in this example there is four periods.

SELECT SUM(value) as value_sum, ... as period_count FROM my_table WHERE cid = 66

Cant figure this out all day long.. Thx.

最满意答案

我没有足够的声望来评论上述答案。

如果你所需要的只是数字的分割，那么你可以简单地用你的问题重新说明：“有多少条目有日期D，这样日期D-1 DAY没有条目？”

在这种情况下，您只需要这些：

SELECT COUNT(*) as PeriodCount FROM `periods` WHERE DATE_ADD(`date`, INTERVAL - 1 DAY) NOT IN (SELECT `date` from `periods`);

在你的PHP中，从第一行中选择“PeriodCount”列。

在我澄清之前，你让我使用了一些疯狂的存储过程方法：P

I don't have enough reputation to comment to the above answer.

If all you need is the NUMBER of splits, then you can simply reword your question: "How many entries have a date D, such that the date D - 1 DAY does not have an entry?"

In which case, this is all you need:

SELECT COUNT(*) as PeriodCount FROM `periods` WHERE DATE_ADD(`date`, INTERVAL - 1 DAY) NOT IN (SELECT `date` from `periods`);

In your PHP, just select the "PeriodCount" column from the first row.

You had me working on some crazy stored procedure approach until that clarification :P