MYSQL循环这段代码

Question

我希望以下代码在多个结束日期下运行。这些结束日期必须是该月的第一天。我可以在自己更改结束日期的同时多次执行代码，但这就是我想要从 2001 年至今的每月数据的大量工作。所以我猜我必须循环这个？

这是我正在使用的代码:

Select t4.Count, t4.Status
From(
    SELECT count(l.VoerID) as Count, l.Datum, l.Status, l.LogID
      FROM (
             SELECT k.VoerID, k.Datum, MAX(k.LogID) AS LogID
               FROM DB.LogStatus k
                Where Datum > '2001-01-01'
                and Datum < '2013-07-01'
              GROUP BY k.VoerID
           ) m
      JOIN DB.LogStatus l
        ON l.VoerID = m.VoertuigID AND l.LogID = m.LogID
    Where status in ('x',y,'z')
    Group by Status
)t4

谁可以帮忙？

编辑:::::@STEPH

当我使用这种和平的代码时(1):

SELECT VoertID,max(LogID) as MaxLogID,Datum
          from DB.LogStatus
WHERE Datum >= '2001-01-01'
                    and Datum < '2013-07-01'
and VoerID = '50789'
GROUP BY VoerID

我得到 VoerID 50789 和最后一个 LogID，但没有相应的日期。这怎么可能？

Answer 1

根据您的输出，您需要操纵日期以始终返回该月的第一天。

在下面的 SQL 中，我提出了一种方法，其中涉及计算出月初(相对于每个日期)过去了多少天，然后将其减去。

    SELECT date_sub(Datum,interval day(Datum)-1 day) datum, status, count(l.VoerID) as count
    FROM DB.LogStatus l
INNER JOIN (SELECT VoerID,max(LogID) as MaxLogID 
          from DB.LogStatus
WHERE Datum >= '2001-01-01'
                    and Datum < '2013-07-01'
GROUP BY VoerID) maxl on l.VoerID=Maxl.VoerID and l.LogID=Maxl.MaxLogID

Where status in ('x','y','z')
and Datum >= '2001-01-01'
                and Datum < '2013-07-01'

GROUP BY date_sub(Datum,interval day(Datum)-1 day),status

分解你的原始代码:

1. 返回给定的 VoerID，返回 FIRST Date 和 MAX LogID

          SELECT k.VoerID, k.Datum, MAX(k.LogID) AS LogID 
          FROM DB.LogStatus k
   
          Where Datum > '2001-01-01'
   
          and Datum < '2013-07-01'
   
         GROUP BY k.VoerID    
   

2. 从日志中获取状态处于某个范围内、LogID 匹配且表中的 VoerID 与派生表中的 VoertuigID 匹配的所有项目(实际上并不存在于代码中)。按日志状态分组并获取 FIRST Datum、FIRST LogID 和 COUNT

         SELECT count(l.VoerID) as Count, l.Datum, l.Status, l.LogID
               FROM (            step1           ) m
               JOIN DB.LogStatus l
                 ON l.VoerID = m.VoertuigID AND l.LogID = m.LogID
             Where status in ('x',y,'z')
             Group by Status
   

3. 从 2 返回计数和状态列

修改后的代码:

1. 识别特定日期内每个 VoerID 的最后一个 LogID

    SELECT VoerID,max(LogID) as MaxLogID 
             from DB.LogStatus
   WHERE Datum >= '2001-01-01'
                       and Datum < '2013-07-01'
   GROUP BY VoerID
   

2. 通过这些最大 LogID 限制日志，然后筛选相关状态，然后计算每月第一天每种状态的行数

   SELECT date_sub(Datum,interval day(Datum)-1 day) datum, status, count(l.VoerID) as count
   
   FROM DB.LogStatus l
   INNER JOIN ( m  ) maxl on l.VoerID=Maxl.VoerID and l.LogID=Maxl.MaxLogID
   Where status in ('x','y','z')
   and Datum >= '2001-01-01'
   and Datum < '2013-07-01'
   GROUP BY date_sub(Datum,interval day(Datum)-1 day),status