更新: 我正在编辑我的问题，希望得到更好的答案。我看到这不是那么简单，但我不敢相信没有比目前所提到的更简单的解决方案。 我现在正在寻找是否有某种 php、mysql 解决方案以最有效的方式处理这个问题。我在下面修改了我的问题，试图让事情更清楚

我有一个包含以下字段的表:

• 用户名
• 组号
• 行动
• 行动日期

每当我的系统上的用户被添加到一个组(action = 1)或从一个组中删除(action = -1)时，这个表就会简单地存储。每当上述操作之一发生时，都会记录日期时间，如 ActionDate

只要该用户在该计费月份至少 15 天属于该组(计费月份不一定是月初，可以从1 月 15 日至 2 月 15 日)

我每个月都会在结算月开始时为当时属于其组的所有用户向我的组收费。现在，在一个月的时间里，他们可能会向他们的组中添加新用户或从他们的组中删除现有用户。 如果他们删除了一个用户，我需要知道该用户是否在该账单月的至少 15 天内属于该组。如果他当时什么都不做，如果没有，则需要为该用户退还该组(因为他们在月初为该用户付款，但他加入该组的时间不到 15 天) 如果他们添加了一个用户并且该用户在该组中至少停留了 15 天(即在计费月的 15 天内添加并且在 15 天结束之前未被删除)，则必须为该用户向该组收费。如果用户作为组的一部分没有结束 15 天，那么我们什么都不做(不收费)。

一些额外的复杂性是:

• 一个用户可能会在那个账单月中被多次添加或删除，我们需要跟踪他加入该组的总天数
• 我们需要能够区分被移除(最终)或被添加(最终)的用户，以便正确地向群组收费。 (例如，作为组的一部分有 10 天的用户 - 如果他最终被从组中删除，那么我们会退款。如果他被添加到组中，那么我们不会收费 - 因为少于 10 天)
• 在任何给定的计费月中，用户可能不会出现在该表中，因为他们的状态没有改变 - 即他们仍然是组的一部分或从未成为组的一部分。事实上，不需要对这些用户做任何事情，因为如果有必要，他们将被包含在“今天组中有多少用户”的基本月度计算中

我开始意识到没有简单的 mysql 解决方案，我需要一个 php、mysql 组合。请帮忙!!!

这是我最近的 sql 尝试，但它没有包含我在下面讨论的所有问题:

SELECT * 
  FROM groupuserlog 
 where action = 1 
   and actiondate >= '2010-02-01' 
   and actiondate < date_add('2010-02-01',INTERVAL 15 DAY) 
   and userid not in (select userid 
                        from groupuserlog 
                       where action = -1 
                         and actiondate < '2010-03-01' 
                         and actiondate > date_add('2010-02-01', INTERVAL 15 DAY))

最佳答案

我假设用户可能在结算周期之前很久就加入了一个组，并且在结算周期内可能不会更改状态。这需要扫描整个表以构建如下所示的成员资格表:

create table membership (
   UserId int not null,
   GroupId int not null,
   start datetime not null,
   end datetime not null,
   count int not null,
   primary key (UserId, GroupId, end )
);

一旦正确填写，您想要的答案就很容易获得:

set @sm = '2009-02-01';
set @em = date_sub( date_add( @sm, interval 1 month), interval 1 day);

# sum( datediff( e, s ) + 1 ) -- +1 needed to include last day in billing

select UserId, 
       GroupId,  
       sum(datediff( if(end > @em, @em, end), 
                     if(start<@sm, @sm, start) ) + 1 ) as n
from membership 
where start <= @em and end >= @sm
group by UserId, GroupId
having n >= 15;

扫描需要通过游标来执行(不会很快)。我们需要按 ActionDate 和 Action 对您的输入表进行排序，以便“加入”事件出现在“离开”事件之前。计数字段 是否有助于应对病态情况——成员(member)资格在某一天结束，然后在同一天重新开始，在同一天再次结束，在同一天再次开始，等等。在这些情况下，我们递增每个开始事件的计数，以及每个结束事件的递减。我们只会在结束事件将计数归零时关闭成员(member)资格。在填充成员资格表结束时，您可以查询计数值:关闭成员资格应该有计数 = 0，开放成员资格(尚未关闭)应该有计数 = 1。任何计数在 0 和 1 之外的条目都应该仔细检查- 这表明某处存在错误。

游标查询是:

select UserID as _UserID, GroupID as _GroupID, Date(ActionDate) adate, Action from tbl 
order by UserId, GroupId, Date(ActionDate), Action desc;

“Action desc”应该打破平局，以便开始事件出现在结束事件之前，如果有人在同一天加入和离开一个组。 ActionDate 需要从日期时间转换为日期，因为我们对天的单位感兴趣。

光标内的操作如下:

if (Action = 1) then 
  insert into membership 
    set start=ActionDate, end='2037-12-31', UserId=_UserId, GroupId=_GroupId, count=1
    on duplicate key update set count = count + 1;
elsif (Action == -1) 
  update membership 
    set end= if( count=1, Actiondate, end),
        count = count - 1 
    where UserId=_UserId and GroupId=_GroupId and end = '2037-12-31';
end if

我没有给你所需的游标定义的确切语法(你可以在 MySQL 手册中找到)因为完整的代码会使这个想法模糊不清。事实上，在您的应用程序中执行游标逻辑可能会更快 - 甚至可能在应用程序中构建成员详细信息。

编辑:这是实际的代码:

create table tbl (
   UserId int not null,
   GroupId int not null,
   Action int not null,
   ActionDate datetime not null
);

create table membership (
   UserId int not null,
   GroupId int not null,
   start datetime not null,
   end datetime not null,
   count int not null,
   primary key (UserId, GroupId, end )
);

drop procedure if exists popbill;
delimiter //

CREATE PROCEDURE popbill()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE _UserId, _GroupId, _Action int;
  DECLARE _adate date;
  DECLARE cur1 CURSOR FOR 
  select UserID, GroupID, Date(ActionDate) adate, Action 
  from tbl order by UserId, GroupId, Date(ActionDate), Action desc;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  truncate table membership;

  OPEN cur1;

  REPEAT
    FETCH cur1 INTO _UserId, _GroupId, _adate, _Action;
    IF NOT done THEN
       IF _Action = 1 THEN
          INSERT INTO membership
          set start=_adate, end='2037-12-31', 
              UserId=_UserId, GroupId=_GroupId, count=1
          on duplicate key update count = count + 1;
       ELSE
          update membership 
          set end= if( count=1, _adate, end),
              count = count - 1 
          where UserId=_UserId and GroupId=_GroupId and end = '2037-12-31';
       END IF;
    END IF;
  UNTIL done END REPEAT;

  CLOSE cur1;
END
//

delimiter ;

这是一些测试数据:

insert into tbl values (1, 10, 1, '2009-01-01' );
insert into tbl values (1, 10, -1, '2009-01-02' );
insert into tbl values (1, 10, 1, '2009-02-03' );
insert into tbl values (1, 10, -1, '2009-02-05' );
insert into tbl values (1, 10, 1, '2009-02-05' );
insert into tbl values (1, 10, -1, '2009-02-05' );
insert into tbl values (1, 10, 1, '2009-02-06' );
insert into tbl values (1, 10, -1, '2009-02-06' );
insert into tbl values (2, 10, 1, '2009-02-20' );
insert into tbl values (2, 10, -1, '2009-05-30');
insert into tbl values (3, 10, 1, '2009-01-01' );
insert into tbl values (4, 10, 1, '2009-01-31' );
insert into tbl values (4, 10, -1, '2009-05-31' );

这是正在运行的代码和结果:

call popbill;
select * from membership;

+--------+---------+---------------------+---------------------+-------+
| UserId | GroupId | start               | end                 | count |
+--------+---------+---------------------+---------------------+-------+
|      1 |      10 | 2009-01-01 00:00:00 | 2009-01-02 00:00:00 |     0 |
|      1 |      10 | 2009-02-03 00:00:00 | 2009-02-05 00:00:00 |     0 |
|      1 |      10 | 2009-02-06 00:00:00 | 2009-02-06 00:00:00 |     0 |
|      2 |      10 | 2009-02-20 00:00:00 | 2009-05-30 00:00:00 |     0 |
|      3 |      10 | 2009-01-01 00:00:00 | 2037-12-31 00:00:00 |     1 |
|      4 |      10 | 2009-01-31 00:00:00 | 2009-05-31 00:00:00 |     0 |
+--------+---------+---------------------+---------------------+-------+
6 rows in set (0.00 sec)

然后，检查 2 月 9 日出现了多少个账单日:

set @sm = '2009-02-01';
set @em = date_sub( date_add( @sm, interval 1 month), interval 1 day);

select UserId, 
       GroupId,  
       sum(datediff( if(end > @em, @em, end), 
                 if(start<@sm, @sm, start) ) + 1 ) as n
from membership 
where start <= @em and end >= @sm
group by UserId, GroupId;

+--------+---------+------+
| UserId | GroupId | n    |
+--------+---------+------+
|      1 |      10 |    4 |
|      2 |      10 |    9 |
|      3 |      10 |   28 |
|      4 |      10 |   28 |
+--------+---------+------+
4 rows in set (0.00 sec)

这可以只扫描表以查找自上次运行以来的更改:

1. 删除“截断成员资格”声明。
2. 创建包含最后处理的时间戳的控制表
3. 计算您希望在此运行中包含的最后一个时间戳(我建议 max(ActionDate) 不好，因为可能会有一些乱序到达时间戳较早。一个不错的选择是“00:00 :00”，或本月第一天的“00:00:00”)。
4. 更改游标查询以仅包含上次运行日期(来自控制表)和计算的最后日期之间的 tbl 条目。
5. 最后用计算出的最后日期更新控制表。

如果你这样做，传递一个允许你从头开始重建的标志也是一个好主意 - 即。将控制表重置为开始时间，并在运行常规过程之前截断成员表。

关于sql - 帮助编写一个 mysql 查询 - 这必须已经完成了 1000 次，但我正在努力..请帮助？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/2338950/